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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.