Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajeshmadhukar
Starting Member
1 Post |
 Posted - 2005-11-23 : 04:56:52
|
| REFRENCIAL INTEGRITY QUESTION :-I have wo tables Parent and the childCREATE TABLE PARENT( PID int PRIMARY KEY, P_NAME VARCHAR(50), GENDER char(1))INSERT INTO PARENT VALUES(1,'George','M')INSERT INTO PARENT VALUES(2,'Maria','F')INSERT INTO PARENT VALUES(3,'Newton','M')INSERT INTO PARENT VALUES(4,'Kristy','F')WHEN I try to Create table Child with following definition :-CREATE TABLE CHILD( CID int, M_PID int REFERENCES PARENT(PID) ON DELETE CASCADE, F_PID int REFERENCES PARENT(PID) ON DELETE CASCADE, GENDER Char(1)) I get Error :- Server: Msg 1785, Level 16, State 1, Line 1Introducing FOREIGN KEY constraint 'FK__CHILD__F_PID__17C286CF' on table 'CHILD' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Server: Msg 1750, Level 16, State 1, Line 1Could not create constraint. See previous errors. I want the child table with multiple ON DELETE CASCADE statement as it has been defined HoweverSQL SERVER 2000 successfully runs the create table statementCREATE TABLE CHILD( CID int, M_PID int REFERENCES PARENT(PID) ON DELETE CASCADE, F_PID int REFERENCES PARENT(PID), GENDER Char(1)) HOW CAN I IMPLEMENT REFERENTIAL INTEGRITY WITHOUT USING TRIGER? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-11-23 : 14:50:32
|
quote:
HOW CAN I IMPLEMENT REFERENTIAL INTEGRITY WITHOUT USING TRIGER?
Due to both of your FKs pointing to PID in the parent and also having them setup with cascading deletes, there is no way to implement this without using triggers. SQL Server thinks it will get into a continuous loop in this scenario, so it prevents it from happening.Tara Kizeraka tduggan |
 |
|
|
|
|
|
|
|
REFERENCIAL INTEGRITY