Cascade update to two fields in a table...

Hello,

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'.

Here's the statement generated:

ALTER TABLE dbo.tblPatients ADD CONSTRAINT
rel_tblPatients_tblUsers FOREIGN KEY
(
EnteredByID
) REFERENCES dbo.tblUsers
(
UserName
) ON UPDATE CASCADE

GO

Why won't this work in SQL Server? I have no problem in Access.

Thanks.

Matthew Wells
Matthew.Wells@FirstByte.net

Re: Cascade update to two fields in a table...

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx