unable to add foreign key
So I am trying to add a foreign key to a table with three columns
one is an id column (the primary key) and the other two are foreign keys, or at least they are supposed to be. I try adding the last foreign key and i keep getting
Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'fk_id' on table 'tablename' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
However when i do no action i get
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_id". The conflict occurred in database "database", table "dbo.othertable", column 'id'.
It doesn't make sense, because they are both int's and they both have the same range of numbers.
When i use the GUI it says it saved on the 'othertable' but failed on the one that needs the foreign key. Any ideas?
one is an id column (the primary key) and the other two are foreign keys, or at least they are supposed to be. I try adding the last foreign key and i keep getting
Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'fk_id' on table 'tablename' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
However when i do no action i get
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_id". The conflict occurred in database "database", table "dbo.othertable", column 'id'.
It doesn't make sense, because they are both int's and they both have the same range of numbers.
When i use the GUI it says it saved on the 'othertable' but failed on the one that needs the foreign key. Any ideas?
ASKER
problem is that even if i don't do cascade it fails. I have removed the other foreign key before to make it work as well. No avail.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Like i said though, when i remove the other foreign key, I still can't add in the new one. can it be cyclic with only one foreign key?
ASKER
'OTHERTABLE' table saved successfully
'TABLE' table
- Unable to create relationship 'FK_id'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_id". The conflict occurred in database "DATABASE", table "dbo.REFERED TABLE", column 'id'.
Its like it is trying to create it twice or something and then fails to do it once.
'TABLE' table
- Unable to create relationship 'FK_id'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_id". The conflict occurred in database "DATABASE", table "dbo.REFERED TABLE", column 'id'.
Its like it is trying to create it twice or something and then fails to do it once.
ASKER
Well sadly i have come to the conclusion there is no good way to work with this database using Foreign Keys, I will have to learn how to make triggers to simulate FKs
http://www.sqlservercentral.com/Forums/Topic949832-392-1.aspx#bm949964