By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,766 Members | 1,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,766 IT Pros & Developers. It's quick & easy.

Referetial Integrity - set default, null

100+
P: 144
hi,

can i set that, when the primary-key in a parent table is updated or deleted, the foreign-key in the child table will be set to its default value.

i've tried using the following command
Expand|Select|Wrap|Line Numbers
  1. FOREIGN KEY (kdJur) REFERENCES parent ON UPDATE set default,on delete set null
but, i receive this error
Incorrect syntax near the keyword 'set'.


is there any way to achieve this?

thank you.
Oct 15 '08 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
I'm not sure you can do that command. There are only two options on that can be used for the ON UPDATE clause. Here's the complete syntax (search for ON UPDATE). You either take NO ACTION or CASCADE.

Here's an extract from BOL

If CASCADE is specified, the row is updated in the referencing table if that row is updated in the parent table. If NO ACTION is specified, SQL Server raises an error and the update action on the row in the parent table is rolled back.


But it still can be done.

Create an INSTEAD OF trigger to do the CASCADING update for you. The cascade, in this case, will replace the value of your Foreign Key to NULL instead of a CASCADE update or a CASCADE delete.

Just a thought, I'm not sure how your app works, but this will result in a lot of what we call "orphan records".

Happy coding!

-- CK
Oct 15 '08 #2

100+
P: 144
hi ck9663,

thanks for the reply. it helps!
Oct 16 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.