Multiple Foreign Keys on Same Table

[FreeToGolfAndSki]

Hi,

I have an Orders Table that has Employee1 and Employee2 (one is the
sales rep the other is the telemarketing rep) Both of these fields
need to cascade update against the Employees table. I can't seem to
create the desired relationship in a Diagram and I'm not sure how best
to set this up. Any ideas?

Thanks in advance...

[Tom Moreau]

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

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"FreeToGolfAndSki" <wl*@genoagroup.com> wrote in message
news:11*********************@z34g2000cwc.googlegro ups.com...
Hi,

I have an Orders Table that has Employee1 and Employee2 (one is the
sales rep the other is the telemarketing rep) Both of these fields
need to cascade update against the Employees table. I can't seem to
create the desired relationship in a Diagram and I'm not sure how best
to set this up. Any ideas?

Thanks in advance...

[Doug]

It isn't what you asked for, but a better solution is to have an
Employees table, A "roles" table, and a Employeesroles table.

So, one employee row for every person.
Roles would start with two rows, "Sales Rep" and "Telemarketing"
and Employeesroles would track the relationships of which people were
reps, and which were telemarketing, and which were both.

[FreeToGolfAndSki]

Thanks for all the help. I will set up triggers for now but will
redesign later - that's best in the long run.

Again, many thinks for the help!!!

[Erland Sommarskog]

Tom Moreau (to*@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Tom Moreau]

I would have tried the code out, but alas - no DDL.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...
Tom Moreau (to*@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

The whole cascading delete issue is a major pain in the ass that I
can't believe MS didn't fix in SQL 2005. If we can manually write
triggers to do so, the SQL Server should just be able to handle the
*possibility* of cascading paths and just throw an error when there
actually *were* cascading paths.

[Erland Sommarskog]

pb648174 (go****@webpaul.net) writes:

The whole cascading delete issue is a major pain in the ass that I
can't believe MS didn't fix in SQL 2005. If we can manually write
triggers to do so, the SQL Server should just be able to handle the
*possibility* of cascading paths and just throw an error when there
actually *were* cascading paths.

I remember in 1998 when I attendend a roadshow for SQL Server 7, and I
complained to a Technical Evangelist that it was a pity that SQL 7 would
not have cascading updates and deletes. His reply was that he cried the
day the feature was cut.

When I eventually arrived on SQL 2000 (we were stuck on SQL 6.5 a little
too long), I no longer had any desire for them. All our foreign constraints
are NO ACTION, and we don't use triggers to implement cascading deletes.
Cascading updates? We hardly ever update primary keys. (When it happens,
it's a matter of special-case jobs).

That said, the restrictions on cascading updates/deletes in SQL Server
are indeed a bit ridiculous, and some of them have a smell of that
the SQL Server team ran out of time for SQL 2000, and had to be more
conservative than necessary. But that's nor really an excuse for SQL 2005.

Anyway, what you should to is to go
http://lab.msdn.microsoft.com/productfeedback/ and submit a suggestion
that the rules for cascading should be relaxed for the next release.
I thought that there would already be such suggestions, but strangely
there is not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[pb648174]

I have created a new one... Peoples rise up!

[--CELKO--]

This problem is harder than people think to do in the general case.
Ever have a course in Graph Theory? Remember some of the NP-Complete
problems at the end of that course?

Consider a set of paths between two nodes { A -> B, B -> A }. Pretty
easy to see that you can have an endless loop with just two tables. But
I will need to dtect ALL cycles of ANY size in any schema to avoid this
for the genral case

Consider a set of paths on one node { A -> A } , the smallest cycle
possible. You wipe out a whole table, set everything to one value or
hang in a loop. A.x changes A.y, and A.y changes A.x .. what does the
table look like after the constraints fire?

Consider a set of paths among three nodes { A -> B, A -> C, B-> C }
since C can be changed by both A and B, which one takes effect in a
declarative language whose statements are supposed to be independent of
an order of execution?

An early version of DB2 would allow this particular set of REFERENCES,
but the results were unpredictable -- the last change would persist.
The reason that you can do some of this with triggers is that they are
procedural and have a fixed order of execution. Of course the
optimizer cannot use them in a plan and it is non-declarative.

[pb648174]

Well that's why they get paid more than I do...

[--CELKO--]

I am afraid that throwing money at an NP-Complete problem is not a good
idea :)