|
|
Posted by Erland Sommarskog on 12/29/07 23:34
Matthew Wells (Matthew.Wells@FirstByte.net) writes:
> I'm sure this has come up for people before. I have two fields in one
> table that both refer to my users table.
>
> TakenByID
> EnteredByID
>
> Both of these refer to UserName in tblUsers. I am trying to set up
> relationships for the two fields. I made one for EnteredByID with no
> problem, but I get an error when I try to set one to TakenByID. I tried
> doing it in the opposite order as well (knowing this wouldn't work) and
> got the same error.
>
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE
> statement conflicted with COLUMN FOREIGN KEY constraint
> 'rel_tblPatients_tblUsers'. The conflict occurred in database 'dbname',
> table 'tblUsers', column 'UserLoginName'.
That particular message means that you have data that violates the
constraint.
But if you have two FK columns that refers to the same base table,
you cannot set up cascading foreing keys, I think. There are tons of
restrictions on when you can use ON CASCADE. The SQL Server developers
took a very conservative approach when they added cascading DRI to
SQL Server.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|