Posted by Erland Sommarskog on 03/25/06 21:16

Tom Moreau (tom@dont.spam.me.cips.ca) writes:
> Don't use the diagram to do this. Use a script:
>
> alter table MyTable
> add
> constraint FK1_MyTable foreign key (Employee1) references Employees
> (EmployeeID)
> on update cascade
> , constraint FK2_MyTable foreign key (Employee2) references Employees
> (EmployeeID)
> on update cascade
>

Alas, this leads to the multiple cascade paths error:

CREATE TABLE Employees (EmployeeID int NOT NULL PRIMARY KEY)

CREATE TABLE MyTable (OrderID int NOT NULL PRIMARY KEY,
Employee1 int NULL,
Employee2 int NULL)
go
alter table MyTable
add
constraint FK1_MyTable foreign key (Employee1) references Employees
(EmployeeID)
on update cascade
, constraint FK2_MyTable foreign key (Employee2) references Employees
(EmployeeID)
on update cascade

go
DROP TABLE MyTable, Employees

Adding a Roles table as usggested by Doug may be a good idea, but it
is not going to resolve this problem, as long as both salesrep and
telemarketing rep are defined in the same table.

The simplest solution, is probably to have employeeids that cannot
change.

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

[Back to original message]