Posted by Matthew Wells on 12/29/07 20:43

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

• Next in forum: Re: Database Tripled In Size!!
• Prev in forum: Re: Instance name vs servername
• Thread view: Cascade update to two fields in a table...

[Reply to this message]