Referetial Integrity - set default, null

[thesti]

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

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.

[ck9663]

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

[thesti]

hi ck9663,

thanks for the reply. it helps!