对同一张表的列强制执行外键约束

Question

如何在 SQL 中强制执行同一表格列上的外键约束，同时输入以下表格中的值：

employee：

• empid number，
• manager number（必须是现有的员工）

- Pop Stack

Answer 1

Oracle称之为自引用完整性约束。具体文档说明请参考此处。

你可以像创建普通约束一样创建自引用约束：

alter table employees
  add constraint employees_emp_man_fk
      foreign key ( manager_no )
      references employees ( emp_id )
   on delete set null
      ;

我假设你的 manager_no 可为空。我在这里添加了设置为 null，因为使用 delete cascade 会清空大量表格数据。

我想不出更好的方法。删除经理不应导致所有下属员工一起被删除，所以你必须将其设置为 null，并在表上创建触发器，以便提醒任何没有经理的员工。

我总是喜欢这个网站（链接地址），它适用于简单的参考。不要忘记在外键上建立索引，否则Tom会批评你 :-)

我们还可以利用标准的 Oracle 语法，在创建表语句中创建自引用的外键，如下所示。

create table employees
 ( emp_id number
 , other_columns ...
 , manager_no number
 , constraint employees_pk 
    primary key (emp_id)
 , constraint employees_man_emp_fk
    foreign key ( manager_no )
    references employees ( emp_id )
    on delete set null
 );

编辑：

回复@popstack的评论：

虽然你可以在一个语句中完成这个操作，但不能够修改表是一个相当荒谬的情况。你应该绝对分析一个你要选择的表，并且你仍然需要在外键上建立索引（可能是更多的列和/或索引），否则每次使用外键时都会进行完整的表扫描。参见我上面提到的asktom链接。

如果你无法修改表，那么按重要性递减的顺序应该：

1. 找出如何修改。
2. 更改数据库设计，因为FK应该有一个索引，如果你不能拥有一个索引，那么FK可能不是正确的方法。也许有一个经理的表和一个员工的表？

- Ben

---

可能有一种解决方案，但在我的情况下，不允许使用“触发器”或“修改”。只需要简单的“创建表”和“插入”即可。现在怎么办？ - Pop Stack

@popstack，我有太多要说了，所以我在答案中添加了一个编辑。 - Ben

很确定你需要为外键约束定义一个唯一或外部键约束来进行引用。而且你可以省略 ON DELETE 子句，它的功能类似于其他关系型数据库管理系统中的 ON DELETE RESTRICT 子句，防止删除具有子记录的父记录。 - Adam Musch

@AdamMusch，你当然对于PK是正确的。我已经更新了答案。不过我不喜欢在删除后什么都不做；如果你这样做，它与“设置为空”相比没有任何好处，并且会在表中留下非明显孤立的记录。 - Ben

@Ben：如果在Oracle中不指定ON DELETE CASCADE或ON DELETE SET NULL，它将阻止您删除父项，从而通过抛出ORA-02292来防止非明显的孤立记录。 - Adam Musch

Answer 2

自引用查询...

Alter table table_name ADD constraints constraints_name foreign key(column_name1,column_name2..) references table_name(column_name1,column_name2...) ON DELETE CASCADE;

例子- ALTER TABLE 员工 ADD CONSTRAINTS Fr_key( 管理员编号) references 员工(员工编号) ON DELETE CASCADE;

- pawan kumar

Answer 3

CREATE TABLE TABLE_NAME (
    `empid_number`    int     (  11) NOT NULL auto_increment,   
    `employee`        varchar ( 100) NOT NULL               ,
    `manager_number`  int     (  11) NOT NULL               ,
     PRIMARY KEY  (`empid_number`),
     CONSTRAINT `manager_references_employee`
     FOREIGN KEY (`manager_number`) REFERENCES (`empid_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

希望能对你有所帮助！

- instanceOfObject

---

有什么其他的方法或建议可以用于Oracle吗？ - Pop Stack

我从未尝试过，但我认为提供TABLE_NAME而不是“parent_table_name”应该可以工作！ - instanceOfObject

这不是Oracle的有效语句。 - user330315

我建议使用MySQL。如所说，我对Oracle没有任何了解。 - instanceOfObject

为什么你在一个关于Oracle的问题中选择了一个只适用于MySQL的答案？你应该取消选中这个答案并选择Ben提供的另一个答案。 - Sled