主键(a，b)与主键(b，a)不同吗？

Question

我有一个employee和一个supervisor表。主管是雇员。employee表如下所示：

employee_id | employee_name
1           | Freeman
2           | Manfred

supervisor表如下所示(字段是employee表的外键)：

supervisor_id | employee_id
1             | 2
2             | 1

我实现了一个复合主键(supervisor_id，employee_id)，它没有阻止更新。

我如何才能防止上述情况的发生？雇员不能监督其主管。

Answer 1

如果没有需要通知的用户界面，那么验证失败的事件，siride使用触发器的解决方案是一个很好的解决方案，Gordon解决方案也会工作。一个更简单的解决方案，也可以通知用户界面，数据验证失败的方法是使用存储过程更新表.

CREATE PROCEDURE AddSupervisor
(
   @supervisor_id int,
   @employee_id
)
AS
    INSERT INTO supervisor
    SELECT @supervisor_ID, @employee_id
    WHERE
    NOT EXISTS
    (
        SELECT 1 FROM supervisor
        WHERE
            supervisor_id = @employee_id AND
            employee_id = @supervisor_id
    ) AND NOT EXISTS  -- EDIT - Add logic to stop inserts for employees who already have a supervisor
    (
        SELECT 1 FROM supervisor
        WHERE
            employee_id = @employee_id
    )

    SELECT @@ROWCOUNT

如果没有插入行，则在末尾选择@@ROWCOUNT将返回0，如果插入一行，则返回1。您可能会认为，您可以将此答案与一个或多个触发器或约束结合起来，以确保通过使用存储的proc以外的其他工具来更新表，不会绕过验证。

编辑:如果员工只能有一个主管，而不是有一个单独的主管表，那么您应该只在employee表中有一个supervisor_id列。拥有一个具有复合密钥的单独的主管表将满足许多关系，例如监督多名员工和员工有多个主管。

Answer 2

这在主键或检查约束中是不容易实现的。

• 主键是唯一的约束，它们指定任何行都不能具有与任何其他行相同的主键。但它并没有说某些组合是无效的。
• Check约束可以使用更复杂的逻辑，但只适用于即将插入的行中列的值。您的问题需要查看其他行。(但请参阅Gordon Linoff关于如何在约束条件下实现此操作的答案-尽管它有点不透明)。

解决办法是使用触发器。触发器可以检查表中的其他行，以查看员工是否已经受到监视并取消事务。

从这里开始：https://msdn.microsoft.com/en-us/library/ms189799.aspx

Answer 3

你可以用几种方式做到这一点。一种是对值的排序施加检查约束，并使用唯一约束(或主键)。这将是：

alter table supervisor add constraint chk_supervisor_employee check (supervisor_id < employee_id);

这就创建了功能。但它不能在语义上实现你想要的。相反，您可以添加列以获得最小和最大的列，然后添加唯一的约束：

alter table supervisor add minse (case when supervisor_id < employee_id then supervisor_id else employee_id end);
alter table supervisor add maxse (case when supervisor_id < employee_id then employee_id else supervisor_id end);
alter table supervisor add constraint unq_supervisor_employee unique(minse, maxse);