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]

Re: Multiple Foreign Keys on Same Table

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

https://www.pinpub.com

..
"FreeToGolfAndS ki" <wlr@genoagroup .com> wrote in message
news:1143301309 .330185.72720@z 34g2000cwc.goog legroups.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]

Re: Multiple Foreign Keys on Same Table

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 "Telemarket ing"
and Employeesroles would track the relationships of which people were
reps, and which were telemarketing, and which were both.

[FreeToGolfAndSki]

Re: Multiple Foreign Keys on Same Table

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]

Re: Multiple Foreign Keys on Same Table

Tom Moreau (tom@dont.spam. me.cips.ca) writes:[color=blue]
> 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
>[/color]

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@sommarsk og.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

[Tom Moreau]

Re: Multiple Foreign Keys on Same Table

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

https://www.pinpub.com

..
"Erland Sommarskog" <esquel@sommars kog.se> wrote in message
news:Xns9791CE3 7C445BYazorman@ 127.0.0.1...
Tom Moreau (tom@dont.spam. me.cips.ca) writes:[color=blue]
> 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
>[/color]

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@sommarsk og.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

[pb648174]

Re: Multiple Foreign Keys on Same Table

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]

Re: Multiple Foreign Keys on Same Table

pb648174 (google@webpaul .net) writes:[color=blue]
> 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.[/color]

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, esquel@sommarsk og.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

[pb648174]

Re: Multiple Foreign Keys on Same Table

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

[--CELKO--]

Re: Multiple Foreign Keys on Same Table

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]

Re: Multiple Foreign Keys on Same Table

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

[--CELKO--]

Re: Multiple Foreign Keys on Same Table

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