467,149 Members | 1,244 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,149 developers. It's quick & easy.

Referetial Integrity - set default, null

100+
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
  • viewed: 1221
Share:
2 Replies
ck9663
Expert 2GB
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+
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.

Similar topics

reply views Thread by Sharon Cowling | last post: by
7 posts views Thread by Mike Mascari | last post: by
4 posts views Thread by aj | last post: by
7 posts views Thread by Jimmie H. Apsey | last post: by
6 posts views Thread by Jeff North | last post: by
2 posts views Thread by njames | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.