Defining Relationships Between Parent and Child Tables

From: Oracle 10g Docs:  Maintaining Data Integrity in Application Development


Several relationships between parent and child tables can be determined by the other types of integrity constraints defined on the foreign key in the child table.
No Constraints on the Foreign Key  When no other constraints are defined on the foreign key, any number of rows in the child table can reference the same parent key value. This model allows nulls in the foreign key.
This model establishes a one-to-many relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. An example of such a relationship is shown in Figure 6-3, "Tables with Referential Integrity Constraints" between the employee and department tables. Each department (parent key) has many employees (foreign key), and some employees might not be in a department (nulls in the foreign key).
NOT NULL Constraint on the Foreign Key  When nulls are not allowed in a foreign key, each row in the child table must explicitly reference a value in the parent key because nulls are not allowed in the foreign key.


Any number of rows in the child table can reference the same parent key value, so this model establishes a one-to-many relationship between the parent and foreign keys. However, each row in the child table must have a reference to a parent key value; the absence of a value (a null) in the foreign key is not allowed. The same example in the previous section can be used to illustrate such a relationship. However, in this case, employees must have a reference to a specific department.


UNIQUE Constraint on the Foreign Key  When a UNIQUE constraint is defined on the foreign key, only one row in the child table can reference a given parent key value. This model allows nulls in the foreign key.


This model establishes a one-to-one relationship between the parent and foreign keys that allows undetermined values (nulls) in the foreign key. For example, assume that the employee table had a column named MEMBERNO, referring to an employee membership number in the company insurance plan. Also, a table named INSURANCE has a primary key named MEMBERNO, and other columns of the table keep respective information relating to an employee insurance policy. The MEMBERNO in the employee table should be both a foreign key and a unique key:

• To enforce referential integrity rules between the EMP_TAB and INSURANCE tables (the FOREIGN KEY constraint)
• To guarantee that each employee has a unique membership number (the UNIQUE key constraint)

UNIQUE and NOT NULL Constraints on the Foreign Key  When both UNIQUE and NOT NULL constraints are defined on the foreign key, only one row in the child table can reference a given parent key value, and because NULL values are not allowed in the foreign key, each row in the child table must explicitly reference a value in the parent key.


This model establishes a one-to-one relationship between the parent and foreign keys that does not allow undetermined values (nulls) in the foreign key. If you expand the previous example by adding a NOT NULL constraint on the MEMBERNO column of the employee table, in addition to guaranteeing that each employee has a unique membership number, you also ensure that no undetermined values (nulls) are allowed in the MEMBERNO column of the employee table.