外键可以为NULL吗?
外键可以重复吗?
据我所知,不应在外键中使用
NULL
,但是在我的某些应用程序中,我可以在Oracle和SQL Server中输入NULL
,而且我也不知道为什么。#1 楼
简短的答案:是的,它可以为NULL或重复。首先要记住,外键仅要求该字段中的值必须首先存在于另一个表(父表)中。这就是FK的全部定义。根据定义,Null不是值。空值表示我们尚不知道这个值是什么。让我给你一个现实的例子。假设您有一个存储销售建议的数据库。进一步假设每个提案仅分配了一个销售人员和一个客户。因此,您的提案表将具有两个外键,一个具有客户ID,一个具有销售代表ID。但是,在创建记录时,并不总是分配销售代表(因为尚无人可以自由处理),因此填写了客户ID,但是销售代表ID可能为空。换句话说,当您在输入数据时可能不知道空值,但是您确实知道表中需要输入的其他值时,通常需要具有空FK的能力。要在FK中允许空值,通常要做的就是在具有FK的字段上允许空值。空值与其作为FK的想法是分开的。
它是否唯一与表是否与父表具有一对一或一对多关系有关。现在,如果您具有一对一关系,则可以将所有数据都放在一个表中,但是如果表太宽或数据位于不同主题上(员工-保险示例@tbone例如),那么您想要带有FK的单独表。然后,您可能想使此FK兼作PK(保证唯一性)或对其施加唯一约束。
大多数FK都是一对多的关系,这就是您从FK得到的,而无需在字段上增加其他约束。因此,您有一个订单表和一个订单详细信息表。如果客户一次订购十个项目,则他有一个订单和十个订单明细记录,这些记录包含与FK相同的orderID。
评论
因此,这比拥有一个名为“ Unassigned”的假销售人员要好吗?
–托马斯·韦勒(Thomas Weller)
2014年9月19日在8:48
一条评论。空值为不了解SQL(mis)如何处理3VL的人们在查询中留下很多错误的余地。如果某个特定的r-table确实不需要销售人员,则不要包含该记录。一个单独的表可以是“ ProposalAssignedTo”或带有适当约束的类似表。然后,查询编写者可以加入该表,并为提案没有销售人员时我们想做的事情提供自己的逻辑。 NULL不仅意味着“我们不知道”,还可以用于很多事情(这就是为什么它几乎总是一个坏主意的原因)
– N West
16-2-4在17:29
@nWest,我不允许不称职的人查询我的数据库,任何不知道如何处理null的开发人员都不称职。有时在特定字段的初始数据输入时数据未知,但那时需要其他字段。
–HLGEM
16年4月4日在18:12
@ThomasWeller引用假销售员(“未分配”)会使问题更严重。我假设您的销售员表有多个列...?未分配先生的社会保险号码是多少?他被分配到哪个部门?谁是他的老板?我希望您能理解我的观点:创建“未分配”销售人员时,您会很快发现,您在一个表中将NULL换成了另一个表中的多个NULL。
–吉利
17年4月21日在3:10
@ThomasWeller如果/当您需要本地化界面时,也会遇到问题。
– tobiv
17年5月5日在11:30
#2 楼
1-是,至少从SQL Server 2000开始。2-是,只要它不是
UNIQUE
约束或链接到唯一索引。评论
该链接已死。
– Mike G
19年5月7日在14:14
#3 楼
从马口中:即使没有与主键或UNIQUE键匹配的
,外键也允许键值全部为NULL
否外键上的约束
当外键上没有定义其他约束时,子表中任何数量的行
都可以引用相同的父键值。
此模型允许外键为空。 ...
NOT NULL外键上的约束
当
外键上不允许使用空值时,子表中的每一行都必须显式引用a父键中的
值,因为外键
中不允许使用空值。
子表中任意数量的行都可以引用相同的父键
,因此该模型在父键和外键之间建立了一对多关系。但是,child
表中的每一行都必须引用一个父键值。不允许在外键中缺少
值(null)。上一节中的相同示例可以用来说明这种关系。
但是,在这种情况下,员工必须参考特定的
部门。
外键上的UNIQUE约束
在外键上定义了UNIQUE约束时,子表中只有一行可以引用给定的父键值。该模型允许
外键中的空值。
外键。例如,假设employee表中有一个名为MEMBERNO的列,它引用了公司保险计划中的雇员成员编号。此外,名为INSURANCE的表具有名为MEMBERNO的主键
,该表的其他列分别保留
有关员工保险单的信息。
employee表中的MEMBERNO必须既是外键又是唯一键: FOREIGN KEY约束)
为了确保每个员工都有唯一的会员编号(
UNIQUE密钥约束)
UNIQUE和NOT NULL约束外键
当在外键上同时定义UNIQUE
和NOT NULL约束时,子表中只有一行
可以引用给定的父键值,因为外键允许,子表中的每一行
必须显式引用父键中的值。
请参见: 11g链接
#4 楼
是的,外键可以是空的,就像上面的高级程序员所说的那样。我要添加另一种情况,即外键将需要为空...。假设我们在一个应用程序中具有表注释,图片和视频,对图片和视频发表评论。在注释表中,我们可以有两个外键PicturesId和VideosId以及主键CommentId。因此,当您在视频上发表评论时,仅视频ID是必填项,而PictureId将为null ...,如果您在图片上发表评论,则仅需PictureId,而视频ID为null ...
评论
我认为有解决此问题的更好方法。您可以有两列,而不是创建新列,即“ id”和“ type”,其中将包含外键表的id和名称。例如,id = 1,type = Picture将表示到ID为1的Picture表的链接。使用此解决方案的优点是,当将注释添加到其他表时,您将不必创建新列。缺点是在数据库级别没有外键约束,而该约束必须是应用程序级别。
–Agent47DarkSoul
16-4-22在19:32
@Agent:我们在生产使用中有了这个“解决方案”。不要这样做,太可怕了。进行查询变得很混乱,“如果是类型1,则联接到该表,否则联接到此表”。这对我们来说是一场噩梦。我们最终做了这个答案说的话,并为每种连接类型创建了一个新列。创建列很便宜。几乎唯一的缺点是许多列使Toad难以使用,但这只是Toad的缺点。
–user128216
16年4月29日在11:16
@FighterJet Rails提供了一个很棒的ORM框架,该框架使用此解决方案甚至可以处理复杂的查询。
–Agent47DarkSoul
16年4月29日在14:07
@Agent:也许可以,但是如果您可以简化它,为什么还要使其复杂?也许“噩梦”是一个错误的用词:非常不便。我们并没有遭受数据完整性问题的困扰。
–user128216
16年4月29日在15:06
#5 楼
这取决于此foreign key
在您的关系中所起的作用。关系中的常规属性,则可以为NULL。#6 楼
这是使用Oracle语法的示例:首先让我们创建一个表COUNTRY
CREATE TABLE TBL_COUNTRY ( COUNTRY_ID VARCHAR2 (50) NOT NULL ) ;
ALTER TABLE TBL_COUNTRY ADD CONSTRAINT COUNTRY_PK PRIMARY KEY ( COUNTRY_ID ) ;
创建表PROVINCE
CREATE TABLE TBL_PROVINCE(
PROVINCE_ID VARCHAR2 (50) NOT NULL ,
COUNTRY_ID VARCHAR2 (50)
);
ALTER TABLE TBL_PROVINCE ADD CONSTRAINT PROVINCE_PK PRIMARY KEY ( PROVINCE_ID ) ;
ALTER TABLE TBL_PROVINCE ADD CONSTRAINT PROVINCE_COUNTRY_FK FOREIGN KEY ( COUNTRY_ID ) REFERENCES TBL_COUNTRY ( COUNTRY_ID ) ;
这在Oracle中运行得很好。请注意,第二个表中的COUNTRY_ID外键没有“ NOT NULL”。
现在在PROVINCE表中插入一行,仅指定PROVINCE_ID就足够了。但是,如果您也选择指定COUNTRY_ID,则它必须已经存在于COUNTRY表中。
#7 楼
默认情况下,外键没有约束,外键可以为null和重复。在创建表/更改表时,如果添加任何唯一性约束或不为null,则只有它不允许为空/重复值。
#8 楼
简而言之,实体之间的“非识别”关系是ER模型的一部分,在设计ER图时在Microsoft Visio中可用。这是强制实施“零或大于零”或“零或一”类型的实体之间的基数的必要条件。请注意,基数是“零”,而不是“一对多”中的“一”。假设一个实体-A中的一个记录/对象“可以”或“可能不”具有一个值,作为对另一个实体-B中的记录的引用。由于,实体A的一条记录有可能将自己标识为另一实体B的记录,因此,实体B中应有一个列,以具有实体B的记录。如果实体A中没有记录标识实体B中的记录(或对象),则此列可以为“ Null”。
在面向对象(真实世界)范式中,B类对象的存在并不一定依赖于A类对象(强烈耦合),这意味着Class- B与Class-A松耦合,因此Class-A可以“包含”(包含)Class-A的对象,与Class-B的对象的概念必须具有(Composition)Class-A的对象,以创建其(B类的对象)。
从SQL查询的角度来看,您可以查询实体B中所有不为“ null”的记录,以获取为实体B保留的外键B.这将为实体A中的行带来具有特定对应值的所有记录,或者所有具有Null值的记录将成为实体B中的实体A中没有任何记录的记录。
#9 楼
外键可以为NULL吗?
针对单列方案的现有答案。如果我们考虑使用多列外键,则可以使用SQL标准中定义的
MATCH [SIMPLE | PARTIAL | FULL]
子句来使用更多选项:给定匹配类型的引用表和引用列的索引。共有三种匹配类型:“完全匹配”,“部分匹配”和“简单匹配”(默认设置)。除非所有外键列都为空,否则MATCH FULL将不允许多列外键的一列为空;如果它们全为空,则不需要该行在引用表中具有匹配项。 MATCH SIMPLE允许任何外键列为空;如果它们中的任何一个为null,则不需要该行在引用表中具有匹配项。 MATCH PARTIAL尚未实现。(当然,可以将NOT NULL约束应用于引用列以防止出现这些情况。)
示例:
CREATE TABLE A(a VARCHAR(10), b VARCHAR(10), d DATE , UNIQUE(a,b));
INSERT INTO A(a, b, d)
VALUES (NULL, NULL, NOW()),('a', NULL, NOW()),(NULL, 'b', NOW()),('c', 'b', NOW());
CREATE TABLE B(id INT PRIMARY KEY, ref_a VARCHAR(10), ref_b VARCHAR(10));
-- MATCH SIMPLE - default behaviour nulls are allowed
ALTER TABLE B ADD CONSTRAINT B_Fk FOREIGN KEY (ref_a, ref_b)
REFERENCES A(a,b) MATCH SIMPLE;
INSERT INTO B(id, ref_a, ref_b) VALUES (1, NULL, 'b');
-- (NULL/'x') 'x' value does not exists in A table, but insert is valid
INSERT INTO B(id, ref_a, ref_b) VALUES (2, NULL, 'x');
ALTER TABLE B DROP CONSTRAINT IF EXISTS B_Fk; -- cleanup
-- MATCH PARTIAL - not implemented
ALTER TABLE B ADD CONSTRAINT B_Fk FOREIGN KEY (ref_a, ref_b)
REFERENCES A(a,b) MATCH PARTIAL;
-- ERROR: MATCH PARTIAL not yet implemented
DELETE FROM B; ALTER TABLE B DROP CONSTRAINT IF EXISTS B_Fk; -- cleanup
-- MATCH FULL nulls are not allowed
ALTER TABLE B ADD CONSTRAINT B_Fk FOREIGN KEY (ref_a, ref_b)
REFERENCES A(a,b) MATCH FULL;
-- FK is defined, inserting NULL as part of FK
INSERT INTO B(id, ref_a, ref_b) VALUES (1, NULL, 'b');
-- ERROR: MATCH FULL does not allow mixing of null and nonnull key values.
-- FK is defined, inserting all NULLs - valid
INSERT INTO B(id, ref_a, ref_b) VALUES (1, NULL, NULL);
db <>小提琴演示
#10 楼
我认为最好考虑表格中可能存在的基数。我们可以将最小基数设为零。如果为可选,则来自相关表的元组的最小参与度可能为零,现在您将必须允许将外键值设置为null。 。
#11 楼
外键的概念基于引用主表中已经存在的值的概念。这就是为什么在另一个表中将其称为外键的原因。这个概念称为参照完整性。如果将外键声明为空字段,则将违反引用完整性的逻辑。它指的是什么?它只能引用主表中存在的内容。因此,我认为将外键字段声明为null是错误的。评论
它可以引用“ nothing”,或者您还不知道它的值是否为NULL,但是引用完整性表示的是,如果引用“ something”,则它必须存在。
– Yaxe
19-10-30在8:44
评论
@Adrian:据我所知,外键不能为null,但在sql server和oracle中为null。你能解释为什么吗?@Jams-阅读答案中的链接。
由于答案和问题很有用,因此无法删除。随时编辑问题以进行改进。
请把关于重复的问题分开。下面仅回答有关NULL的问题。