请为我澄清两件事:


外键可以为NULL吗?
外键可以重复吗?

据我所知,不应在外键中使用NULL,但是在我的某些应用程序中,我可以在Oracle和SQL Server中输入NULL,而且我也不知道为什么。

评论

@Adrian:据我所知,外键不能为null,但在sql server和oracle中为null。你能解释为什么吗?

@Jams-阅读答案中的链接。

由于答案和问题很有用,因此无法删除。随时编辑问题以进行改进。

请把关于重复的问题分开。下面仅回答有关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

#12 楼

我认为一个表的外键也是其他表的主键,因此它不允许空值,因此外键中没有空值是没有问题的。