空外键 [英] Null Foreign Key
问题描述
我和现任自学成才的程序员发生争执
在数据库设计中使用空外键是可以的。
我的看法是外键的全部意义在于它不应该是可选的，它的定义是它是必要的链接到
父表和定义的一部分。如果它是可选的，它不应该是
成为表格定义的一部分，而应该在链接表中
而不是。
评论？
一般来说，在任何数据库中将NULL保持在最低限度是一种很好的做法
架构。然而，拥有可空的
外键约束并没有什么根本性的错误。通常需要的一个例子是
邻接列表层次结构，例如员工及其经理的表格：
CREATE TABLE Employees（emp_nbr INTEGER NOT NULL PRIMARY KEY，
manager_emp_nbr INTEGER NULL REFERENCES Employees（emp_nbr），emp_name
VARCHAR（30）NOT NULL UNIQUE）;
manager_emp_nbr是FK，但是对于总统，Chief Exec
或树根处的其他人来说它将是NULL。
-
David Portas
SQL Server MVP
-
Bodza Bodza（bo * **** @ hotmail.com）写道：我正在与一位现任自学成才的程序员争论说，在数据库设计中使用空外键是可以的。
我的看法是外键的重点在于它不应该是可选的，它的定义是它是<的必要链接paren t表和部分定义。如果它是可选的，那么它不应该成为表格定义的一部分，而应该在链接表中。
我同意你的程序员。有些人持有理想的数据，那么数据库中应该没有可空列，但是如果你将所有
可选列移动到链接表中，你将被淹没在表格中。
假设您在业务流程中有订单。订单有一个
的客户 - 他可能从一开始就知道。有一名员工
负责组装订单 - 但直到
订单被分配给某人才知道他。该订单稍后发货，并在货件中添加了
，因此有一个货件ID。同时
是发票ID。然而，客户支付，现在你有一个支付ID。
如果你坚持连接表，我们现在最多四个。因为支付ID是错误的，所以如果没有发票ID，你需要
用触发器强制执行该限制，而不是使用CHECK约束
在桌子上。
当然如果我们谈论OrderDetails表，那么此表中的OrderID
应为NOT NULL - 但这是因为OrderID是
该表主键的一部分。
-
Erland Sommarskog ，SQL Server MVP， so****@algonet.se
联机丛书适用于SQL Server SP3
http：/ /www.microsoft.com/sql/techinf...2000/books.asp
" David Portas"写道：
manager_emp_nbr是FK，但总统，执行官或其他人在树根处将为NULL。
嗯。就个人而言，在这个例子中我会说，树的根是
的个人是他们自己的经理。给我一个更好的例子
来说服我。
I''m having an argument with an incumbent self-taught programmer that
it is OK to use null foreign keys in database design.
My take is the whole point of a foreign key is that it''s not supposed
to be optional, it''s very definition is it''s a necessary link to the
parent table and part of the definition. If it''s optional it shouldn''t
be part of the definition of a table and should be in a linking table
instead.
Comments?解决方案In general it''s good practice to keep NULLs to a minimum in any database
schema. However there''s nothing fundamentally wrong with having a nullable
Foreign Key constraint. One example where it''s commonly required is an
adjacency list hierarchy such as a table of employees and their managers:
CREATE TABLE Employees (emp_nbr INTEGER NOT NULL PRIMARY KEY,
manager_emp_nbr INTEGER NULL REFERENCES Employees(emp_nbr), emp_name
VARCHAR(30) NOT NULL UNIQUE);
manager_emp_nbr is the FK but it will be NULL for the President, Chief Exec
or other individuals at the root of the tree.
--
David Portas
SQL Server MVP
--
Bodza Bodza (bo*****@hotmail.com) writes:I''m having an argument with an incumbent self-taught programmer that
it is OK to use null foreign keys in database design.
My take is the whole point of a foreign key is that it''s not supposed
to be optional, it''s very definition is it''s a necessary link to the
parent table and part of the definition. If it''s optional it shouldn''t
be part of the definition of a table and should be in a linking table
instead.
I agree with your programmer. There are those who hold the ideal that
there should be no nullable columns in database, but if you move all
optional columns into linking tables, you will drown in tables.
Say that you in your business process you have an Order. An Order has a
customer - he is probably known from the beginning. There is an employee
who is responsible for assembling the order - but he is not known until
the order is assigned to someone. The Order is later shipped, and added
to a shipment, and thus there is a Shipment ID. At the same time there
is an invoice ID. Yet, the customer pays, and now you have a payment ID.
If you insist on linking tables we are now up to four. Since it would
be an error to have a payment ID, if there is not an invoice ID, you need
enforce that restriction with a trigger, rather with a CHECK constraint
on the table.
Of course if we are talking about the OrderDetails table, the OrderID
in this table should be NOT NULL - but that is because OrderID is
part of the primary key of that table.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"David Portas" wrote:
manager_emp_nbr is the FK but it will be NULL for the President, Chief Exec
or other individuals at the root of the tree.
Hmmm. Personally in this example I would say that the individuals at
the root of the tree are their own managers. Give me a better example
to convince me.
这篇关于空外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
