April 4, 2005 at 2:45 am
Hi!
I have two table, let's say T01 (ID,...) and T02(..., ID01, ID02), where ID01, ID02 are foreign key based on primary key on T01.
I want to implemet referential integrity with cascade update and delete (for both ID01, ID02) and i can't do it.
I try to create a diagram based on those 2 tables:
- I checked referential integrity (for both foreign keys) - ok!
- I checked Update and Delete in cascade (for one foreign key) - ok!
If I save now the diagram there is no problem!
- I checked Update and Delete in cascade (for the other foreign key) - ok!
If I try to save now the diagram there is a big problem: MS SQL Server doesn't allow me to do this and give me an error!
That's the problem!
Can you give me an advice?
Thanks a lot!
April 6, 2005 at 5:19 pm
Hi
Would you post the error message??
In advance, it's not a recommended idea to use cascaded deletes... it's better if you implement something like this using triggers...
And... supose the next case:
t01 t02
01 A..., 01, 02
02 B..., 02,03
03 C...,01, 03
D....,01,02
Supose that you delete '01' record from t01... with cascaded deletes you will be deleting also A, C and D records from t02.. but there is a restriction because the referential integrity rule that join t02.A with t01.02 and so...
I think that what you need is not a cascaded delete... perhaps you just need to replace the value in t02 in a way that it's not joined any more with t01... something like
t01 t02
01 A..., null, 02
02 B..., 02,03
03 C...,null, 03
D....,null,02
but you cannot do this if you have a restriction and/or a referential integrity relation... you must enforce the r.i. using triggers, or any other method...
Nicolas
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply