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

Matthew Wells (Matthew.Wells@xxxxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: I cant seem to access my fulltext data
    ... log from messages coming from MSSearch or MSSCi? ... This might refer to your ... The most common reason for such a failure is the the SQL Server Service ...
    (microsoft.public.sqlserver.fulltext)
  • RE: Cannot generate SSPI context
    ... Let us refer to the following steps to troubleshoot the issue: ... How to troubleshoot the "Cannot generate SSPI context" error message ... How to use Kerberos authentication in SQL Server ... from your newsreader: microsoft.private.directaccess.partnerfeedback. ...
    (microsoft.public.windows.server.sbs)
  • Can I refer to a local recordset after the FROM clause ?
    ... In order to improve performance for a SQL Server DB which resides on an ... FROM clause to a local recordset instead of a table. ... to be accessed more than 1000 times within a loop. ... I want to perform perform in-line SQL statements which refer to ...
    (microsoft.public.vb.database.ado)
  • RE: Newbie replication questions
    ... Microsoft MSDN site since I believe that no books can describe more ... For SQL Server Replication Management and Configuration, please refer to: ...
    (microsoft.public.sqlserver.replication)
  • Re: VBA no longer works when linked to SQL server tables
    ... recordset code then the email will be sent but obviously no data will ... be inserted into 'tblusers' so it doesnt seem to like the code ... Does SQL require different code for the insertion to ... Did you define primary keys on your SQL Server tables? ...
    (microsoft.public.access.modulesdaovba)