同一个表上的多个外键 [英] Multiple Foreign Keys on Same Table

问题描述

我有一个订单表，其中有Employee1和Employee2（一个是

销售代表，另一个是电话营销代表）两个这些字段

需要对Employees表进行级联更新。我似乎无法在图表中创建所需的关系，而且我不确定如何最好地设置它。任何想法？


提前致谢...

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

推荐答案

不要使用图表来做到这一点。使用脚本：


alter table MyTable

add

约束FK1_MyTable外键（Employee1）引用员工

（EmployeeID）

更新级联

，约束FK2_MyTable外键（Employee2）引用员工

（EmployeeID）

更新级联


-

汤姆


-------- --------------------------------------------

Thomas A. Moreau，理学士，博士，MCSE，MCDBA

SQL Server MVP

专栏作家，SQL Server专业版

多伦多，ON加拿大
www.pinpub.com

..

" FreeToGolfAndSki" < wl*@genoagroup.com>在消息中写道

新闻：11 ********************* @ z34g2000cwc.googlegro ups.com ...




我的订单表有Employee1和Employee2（一个是

销售代表，另一个是电话销售代表）两个这些字段

需要对Employees表进行级联更新。我似乎无法在图表中创建所需的关系，而且我不确定如何最好地设置它。任何想法？


提前致谢...

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

这不是你要求的，但更好的解决方案是拥有一个

员工表，A角色表和Employeesroles表。


因此，每个人都有一个员工行。

角色将以两行开头，销售代表和电话销售

和Employeesroles将跟踪哪些人是谁b $ b代表，哪些是电话营销，哪两者都是关系。

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.

感谢您的帮助。我现在将设置触发器，但稍后会重新设计 - 这是最好的长期运行。


再次，很多人都在考虑帮助！ !!

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

这篇关于同一个表上的多个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！