Cascading Referential Integrity Constraints

I am reading about Cascading Referential Integrity Constraints, but i need examples for
NO ACTION, CASCADE, SET NULL, SET DEFAULT
enrique_aeoAsked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Ok. It would be like this:

ALTER TABLE ur_table add constraint FOREIGN KEY (column_name) REFERENCES other_table(primary_key_in_that_table) ON DELETE NO ACTION;
ALTER TABLE ur_table add constraint FOREIGN KEY (column_name) REFERENCES other_table(primary_key_in_that_table) ON DELETE CASCADE;
ALTER TABLE ur_table add constraint FOREIGN KEY (column_name) REFERENCES other_table(primary_key_in_that_table) ON DELETE SET NULL;
ALTER TABLE ur_table add constraint FOREIGN KEY (column_name) REFERENCES other_table(primary_key_in_that_table) ON DELETE SET DEFAULT;
ALTER TABLE ur_table add constraint FOREIGN KEY (column_name) REFERENCES other_table(primary_key_in_that_table) ON UPDATE NO ACTION;
ALTER TABLE ur_table add constraint FOREIGN KEY (column_name) REFERENCES other_table(primary_key_in_that_table) ON UPDATE CASCADE;
ALTER TABLE ur_table add constraint FOREIGN KEY (column_name) REFERENCES other_table(primary_key_in_that_table) ON UPDATE SET NULL;
ALTER TABLE ur_table add constraint FOREIGN KEY (column_name) REFERENCES other_table(primary_key_in_that_table) ON UPDATE SET DEFAULT;
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope this excerpt from MSDN guides you completely on this:

"NO ACTION is the default if ON DELETE or ON UPDATE is not specified.

ON DELETE NO ACTION
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, an error is raised and the DELETE statement is rolled back.

ON UPDATE NO ACTION
Specifies that if an attempt is made to update a key value in a row whose key is referenced by foreign keys in existing rows in other tables, an error is raised and the UPDATE statement is rolled back.

CASCADE, SET NULL and SET DEFAULT allow for deletions or updates of key values to affect the tables defined to have foreign key relationships that can be traced back to the table on which the modification is performed. If cascading referential actions have also been defined on the target tables, the specified cascading actions also apply for those rows deleted or updated. CASCADE cannot be specified for any foreign keys or primary keys that have a timestamp column.

ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows that contain those foreign keys are also deleted.

ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key are also updated to the new value specified for the key.

Note:  
CASCADE cannot be specified if a timestamp column is part of either the foreign key or the referenced key.

ON DELETE SET NULL
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to NULL. All foreign key columns of the target table must be nullable for this constraint to execute.

ON UPDATE SET NULL
Specifies that if an attempt is made to update a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to NULL. All foreign key columns of the target table must be nullable for this constraint to execute.

ON DELETE SET DEFAULT
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Any nonnull values that are set because of ON DELETE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint.

ON UPDATE SET DEFAULT
Specifies that if an attempt is made to update a row with a key referenced by foreign keys in existing rows in other tables, all the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If a column is nullable, and there is no explicit default value set, NULL becomes the implicit default value of the column. Any non-null values that are set because of ON UPDATE SET DEFAULT must have corresponding values in the primary table to maintain the validity of the foreign key constraint."

And just include this clauses while creating your Foreign keys to enforce it out..
0
 
enrique_aeoAuthor Commented:
ok, it's fine. But i need code
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.