foreign keys in sql server 2000

Foreign Keys In Sql server 2000

I have only these two tables in my completely new database right now.

CREATE TABLE GROUPS(GROUP_ID CHAR(2) CONSTRAINT PKGROUPS PRIMARY KEY,
GROUP_NAME VARCHAR(20);

CREATE TABLE ISSUE(FROM_GROUP CHAR(2),TO_GROUP CHAR(2)
,ISSUE_DATE SMALLDATETIME , QTY NUMERIC);

ALTER TABLE ISSUE
ADD CONSTRAINT FK_ISSUE_1
FOREIGN KEY(FROM_GROUP) REFERENCES GROUPS(GROUP_ID)
ON UPDATE CASCADE ON DELETE CASCADE);

THIS WORKS TILL HERE.

BUT THE NEXT STATEMENT GIVES THIS PROBLEM

ALTER TABLE ISSUE
ADD CONSTRAINT FK_ISSUE_2
FOREIGN KEY(TO_GROUP) REFERENCES GROUPS(GROUP_ID)
ON UPDATE CASCADE ON DELETE CASCADE;

THE MESSAGE IS:

unable to create relationship 'FK_ISSUE_2'.
introducing FOREIGN KEY constraint 'FK_ISSUE_2' on table ISSUE
may cause cycles or multiple cascade paths. specify ON DELETE
NO ACTION or ON UPDATE NO ACTION or modify other FOREIGN KEY constraints

PL. NOTE THERE IS NO DATA IN THESE TABLES.
AND THERE ARE NO MORE TABLES RIGHT NOW IN THE DATABASE.
so multiple cascade path or cycles are not occuring here.

'interesting thing to note is; this is allowed in oracle!!!!!!!!!'

AWAITING A REPLY AS SOON AS POSSIBLE PLEASE

REGARDS
SANGITA.
[1367 byte] By [kadamsangeetha] at [2007-11-18 18:04:53]
# 1 Re: foreign keys in sql server 2000
this is a problem I faced befor ..:mad:
seems that it's now way other than inforcing one of the relationships using triggers which will affect performance and clarity of the databse

Please send again to the forum if you find a solution anywhere else.
hspc at 2007-11-9 13:37:17 >
# 2 Re: foreign keys in sql server 2000
In SQL Server you cannot create referential integrity which will create more than one path to a table in a list of cascading referential actions

For detail refer to following link:

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q321/8/43.asp&NoWebContent=1
ITGURU at 2007-11-9 13:38:16 >