What is Non-Identifying relationship?
If a column (foreign key) / multiple columns (foreign key columns) referencing some table becomes a column in a table as a NON-PRIMARY KEY, and then it is non-identifying relationship.
How to create non-identifying relationship with cardinality of 1 to many relationship in Employee Table?
Example 1: Employee Table with Department Table:
Since the relationship is one to many with Department_No (Department Table) and Department_No (Employee table), we go with Non-Identifying Relationship. In Non-Identifying relationship, primary key migrates from the Department table to Employee table and it is not a part of the primary key in Employee table. Here Department_No becomes the foreign key column and generally Foreign Key relationship is represented by a dotted line.
- Click Non-Identifying relationship in the toolbar.
- Click first on Department table; you can see a dotted line and then click on Employee table. Now you can see a relationship(dotted line) created between the Department Table and Employee Table.
- Double Click the relationship. Change the relationship name as required (EMPLOYEE_FK01).
- Under section CARDINALITY, check Mandatory Parent and Mandatory Child, since NULL values are not allowed in both the primary key and foreign key columns. Now the cardinality is 1..1 to 1..n.
- Cardinality describes the relationship between a parent table and a child table. When you edit properties of the relationship line, you will be requested check(enable) the check boxes for mandatory parent column, and mandatory child. We have explained various permutations and combinations below.
| Parent Table | Child Table | Mandatory Parent | Mandatory Child | Cardinality Relationship |
|---|---|---|---|---|
| Department.Department_No | Employee.Department_No | Yes | Yes | 1..1 to 1..n |
| Department.Department_No | Employee.Department_No | Yes | No | 1..1 to 0..n |
| Department.Department_No | Employee.Department_No | No | Yes | 0..1 to 1..n |
| Department.Department_No | Employee.Department_No | No | No | 0..1 to 0..n |
- If you want to change the default foreign key index name, then change the name (EMPLOYEE_IDX01) in “Index to Foreign Key” tab. You have to double click the relationship line and there is a tab named “Index to Foreign Key”.
- Click apply and ok. You can see how tables are connected and a new column Department_No is included in the Employee table.
Example 2: Employee Table with Title Table:
Repeat the above same procedure and you can see how Title table and Employee table are connected. You can see a new column Title_Code is included in the Employee Table.
How to create SELF REFERENTIAL OR RECURSIVE RELATIONSHIP in Employee Table:
When a Foreign key references the parent key(Primary key) of the same table, then it is called a SELF REFERENTIAL OR RECURSIVE RELATIONSHIP. The column Manager_No in the Employee table is a Foreign key which takes its values from the column Employee_No of the same Employee table.
This relationship is created by non-identifying relationship with cardinality 1 to many relationship in Employee Table:
- Click Non-Identifying relationship in the tool bar.
- Click the employee table twice. You can see the relationship created and a new column “FK_EMPLOYEE_NAME” appearing within the table. Go to the properties of that column and change the name to “MANAGER_NO”. So this column ‘Manager_No” will reference the values from its primary key column ‘Employee_No”.