May 13th, 2009

How do Constraints Work?, FOREIGN KEY (Part 5 of 5)

Posted by admin in P. Table Creation

You can define the actions performed by SQL when you UPDATE or DELETE a key value in the parent table. This ON UPDATE or ON DELETE clause is placed in the FOREIGN KEY constraint.

          [CONSTRAINT  column_constraint_name]
            REFERENCES parent_table[(parent_columns)]
            [ON UPDATE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]
            [ON DELETE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]

                                                                              or

          [CONSTRAINT  table_constraint_name]
            FOREIGN KEY (key_columns)
            REFERENCES parent_table[(parent_columns)]
            [ON UPDATE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]
            [ON DELETE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]
ON UPDATE:
NO ACTION   Generates an error on a foreign key violation. This is the default.
CASCADE     Updates the dependent foreign key value to the parent key value.
RESTRICT    Does not allow the dependent foreign key to change value.
SET NULL    Sets the dependent foreign key values to null.
SET DEFAULT Sets the dependent foreign key values to their default values.
ON DELETE:
NO ACTION   Generates an error on a foreign key violation. This is the default.
CASCADE     Deletes the rows that contain foreign key values that match the deleted parent key value.
RESTRICT    Does not allow the dependent foreign key to change value.
SET NULL    Sets the dependent foreign key values to null.
SET DEFAULT Sets the dependent foreign key values to their default values.

Comments are closed.

Sorry, the comment form is closed at this time.