Insert rules

The insert rules for referential integrity apply to parent and dependent tables.

The following insert rules for referential integrity apply to parent and dependent tables:

Parent table rules
You can insert a row at any time into a parent table without taking any action in the dependent table. For example, you can create a new department in the DEPT table without making any change to the EMP table. If you are inserting rows into a parent table that is involved in a referential constraint, the following restrictions apply:
  • A unique index must exist on the parent key.
  • You cannot enter duplicate values for the parent key.
  • You cannot insert a null value for any column of the parent key.
Dependent table rules
You cannot insert a row into a dependent table unless a row in the parent table has a parent key value that equals the foreign key value that you want to insert. You can insert a foreign key with a null value into a dependent table (if the referential constraint allows this), but no logical connection exists if you do so. If you insert rows into a dependent table, the following restrictions apply:
  • Each nonnull value that you insert into a foreign key column must be equal to some value in the parent key.
  • If any field in the foreign key is null, the entire foreign key is null.
  • If you drop the index that enforces the parent key of the parent table, you cannot insert rows into either the parent table or the dependent table.

Example

For example, assume your company doesn't want to have a row in the PARTS table unless the PROD# column value in that row matches a valid PROD# in the PRODUCTS table. The PRODUCTS table has a primary key on PROD#. The PARTS table has a foreign key on PROD#. The constraint definition specifies a RESTRICT constraint. Every inserted row of the PARTS table must have a PROD# that matches a PROD# in the PRODUCTS table.