SQL 对同一个表的列强制外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8768118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Enforce a foreign-key constraint to columns of same table
提问by pop stack
How to enforce a constraint of foreign key on columns of same table in SQL while entering values in the following table:
在下表中输入值时，如何在 SQL 中对同一表的列强制执行外键约束：
employee:
员工：
- empid number,
- manager number (must be an existing employee)
- 空号，
- 经理编号（必须是现有员工）
采纳答案by instanceOfObject
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
Hope it helps!
希望能帮助到你！
回答by Ben
Oracle call this a self-referential integrity constraint. The documentation is herefor a description,
Oracle 将此称为自引用完整性约束。文档是here的描述，
You create a self-referential constraint in the same manner you would a normal one:
您可以按照与普通约束相同的方式创建自引用约束：
alter table employees
add constraint employees_emp_man_fk
foreign key ( manager_no )
references employees ( emp_id )
on delete set null
;
I'm assuming that your manager_nois nullable. I've added set null here as a delete cascadewould probably wipe out a significant amount of your table.
我假设你manager_no是可以为空的。我在此处添加了 set null ，因为它delete cascade可能会清除大量表格。
I can't think of a better way of doing this. Deleting a manager should not result in the deletion of all their employees so you have to set nulland have a trigger on the table to alert you to anyone with no manager.
我想不出更好的方法来做到这一点。删除经理不应导致其所有员工的删除，因此您必须set null并在表上有一个触发器来提醒您任何没有经理的人。
I always like thissite, which is good for simple references. and don't forget to have an index on the FK as well or Tomwill yell at you :-).
我一直很喜欢这个网站，它适合简单的参考。并且不要忘记在 FK 上也有一个索引，否则汤姆会冲你大喊大叫 :-)。
One can also utilise standard Oracle syntax to create a self-referential FK in the create table statement, which would look like the following.
还可以利用标准 Oracle 语法在 create table 语句中创建自引用 FK，如下所示。
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
);
EDIT:
编辑：
In answer to @popstack's comment below:
回答@popstack 在下面的评论：
Whilst you can do this in one statement not being able to alter a table is a fairly ridiculous state of affairs. You should definitely analyze a table that you're going to be selecting from and you will still want an index on the foreign key ( and possibly more columns and / or more indexes ) otherwise whenever you use the foreign key you're going to do a full table scan. See my link to asktom above.
虽然您可以在一个语句中做到这一点，但不能更改表是一种相当荒谬的事态。您绝对应该分析您要从中选择的表，并且您仍然需要外键上的索引（可能还有更多列和/或更多索引），否则每当您使用外键时，您都会这样做全表扫描。请参阅我上面的 asktom 链接。
If you're unable to alter a table then you should, in descending order of importance.
如果您无法更改表格，那么您应该按照重要性的降序进行更改。
- Find out how you can.
- Change your DB design as a FK should have an index and if you can't have one then FKs are probably not the way to go. Maybe have a table of managers and a table of employees?
- 找出你可以怎样做。
- 改变你的数据库设计，因为 FK 应该有一个索引，如果你不能有一个，那么 FK 可能不是要走的路。也许有一张经理表和一张员工表？
回答by pawan kumar
SELF REFERENCES QUERY...
自我参考查询...
Alter table table_name ADD constraints constraints_name foreign key(column_name1,column_name2..) references table_name(column_name1,column_name2...) ON DELETE CASCADE;
EX- ALTER TABLE Employee ADD CONSTRAINTS Fr_key( mgr_no) references employee(Emp_no) ON DELETE CASCADE;
前任- ALTER TABLE Employee ADD CONSTRAINTS Fr_key( mgr_no) references employee(Emp_no) ON DELETE CASCADE;
