外键可以为NULL和/或重复吗?

请为我澄清两点:

  1. 外键可以为NULL吗?
  2. 外键可以被复制吗?

正如我所知，NULL不应该用于外键，但在我的一些应用程序中，我能够在Oracle和SQL Server中输入NULL，我不知道为什么。

564364 次浏览
小开

1 - __abc0

2 -是的，只要它不是UNIQUE约束或链接到唯一的索引。

小开

来自马的口:

外键允许键值全部为NULL，即使没有 匹配主键或唯一键

对外键没有限制

当外键上没有定义其他约束时，任何数字 子表中的行可以引用相同的父键值。 该模型允许外键. ...

为空

外键的NOT NULL约束

当不允许输入空值时 外键，子表中的每一行都必须显式引用 值，因为在外部中不允许有空值 关键。< / p >

子表中的任意行都可以引用同一个父表 键值，因此该模型建立了一对多关系 在父键和外键之间。然而，每一行都在子 表必须有一个父键值的引用;缺少一个 值(null)在外键中是不允许的。同样的例子 上一节可以用来说明这种关系。 但是，在这种情况下，员工必须有一个特定的引用 部门。< / p >

外键的唯一约束

当UNIQUE约束为 定义在外键上，子表中只有一行可以 引用给定的父键值。类型中允许为空 外键。< / p > 这个模型在父节点之间建立了一对一的关系 中允许未确定值(null)的外键 外键。例如，假设雇员表有一个列 命名为MEMBERNO，指的是 公司保险计划。同样，名为INSURANCE的表也有一个主表 key命名为MEMBERNO，而表的其他列保持各自的 与员工保险政策有关的信息。会员号码 员工表必须是外键和唯一键:

  • 强制EMP_TAB和EMP_TAB之间的引用完整性规则 保险表(外键约束)

    • .

  • 为了保证每个员工都有一个唯一的会员号(即 唯一键约束)

外键上的UNIQUE和NOT NULL约束

当两者都是UNIQUE 和NOT NULL约束被定义在外键上，只有一行 在子表中可以引用给定的父键值，并且因为 在子节点的每一行中，外键中不允许有NULL值 表必须显式引用父键中的值

看到这个:

Oracle 11g link

小开
最佳答案

简单回答:是的，它可以是NULL或重复。

我想解释为什么外键可能需要为空，或者可能需要唯一或不唯一。首先记住，外键只是要求该字段中的值必须首先存在于另一个表(父表)中。这就是所有FK的定义。Null根据定义不是一个值。Null意味着我们还不知道值是什么。

让我给你一个真实的例子。假设您有一个存储销售建议的数据库。进一步假设每个提案只分配了一个销售人员和一个客户。因此，提案表将有两个外键，一个带有客户ID，另一个带有销售代表ID。但是，在创建记录时，并不总是分配销售代表(因为还没有人有空处理它)，因此客户ID被填写，但销售代表ID可能为空。换句话说，当您在输入数据时可能不知道它的值，但您确实知道表中需要输入的其他值时，通常需要具有空FK的能力。要在FK中允许为空，通常你所要做的就是在具有FK的字段上允许为空。空值与FK的概念是分开的。

它是否唯一与该表与父表的关系是一对一还是一对多有关。现在，如果您有一个一对一的关系，您可以将所有数据都放在一个表中，但是如果表变得太宽，或者数据涉及不同的主题(例如@tbone给出的雇员-保险示例)，那么您需要使用FK分隔表。然后，您可能想让这个FK也成为PK(这保证了唯一性)，或者对它设置一个惟一的约束。

大多数FK是一对多关系，这就是你从FK中得到的，而不需要在字段上增加进一步的约束。例如，你有一个订单表和订单明细表。如果客户一次订购10件商品，那么他有一个订单和10个订单详细记录，其中包含与FK相同的orderID。

小开

下面是一个使用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表中。

小开

我认为一个表的外键也是另一个表的主键。所以它不允许空值。所以外键没有空值的问题。

小开

是的外键可以是空的，如上所述，高级程序员…我将添加另一种场景，其中外键将被要求为空.... 假设我们在一个允许对图片和视频进行评论的应用程序中有表格注释、图片和视频。在注释表中，我们可以有两个外键PicturesId和VideosId以及主键CommentId。所以当你评论一个视频时，只有VideosId是必需的，而pictureId是null…如果你评论一张图片，只有PictureId是必需的，VideosId是null…< / p >

小开

简单地说，实体之间的“非识别”关系是ER-Model的一部分，在设计ER-Diagram时可以在Microsoft Visio中使用。这需要在类型为“0或大于0”或“0或1”的实体之间强制执行基数。注意基数中的“0”，而不是“一对多”中的“1”。

现在，一个非识别关系的例子，其中基数可能是“零”(非识别)，当我们说一个实体- a中的记录/对象“可能”或“可能”有一个值作为对另一个实体- b中的记录/s的引用。

由于实体a的一条记录有可能向其他实体b的记录标识自己，因此实体b中应该有一列具有实体b记录的标识值。如果实体a中没有记录标识实体b中的记录或对象，则此列可能为“Null”。

在面向对象(现实世界)范式中，b类对象的存在不一定依赖于a类对象(强耦合)，这意味着b类与a类是松散耦合的，因此a类可以“包含”a类对象(Containment)，而不是b类对象必须有a类对象(Composition)的概念，才能创建b类对象。

从SQL Query的角度来看，可以查询实体b中所有“非空”的外键为实体b保留的记录。这将为实体a中的行带来具有特定对应值的所有记录，或者所有具有Null值的记录将是实体b中实体a中没有任何记录的记录。

小开

默认情况下，对外键没有约束，外键可以为null和重复。

在创建表/修改表时，如果你添加了唯一性或非空的约束，那么只有它不允许空值/重复值。

小开

我认为最好是考虑我们在表中可能的基数。 我们可以让最小的基数为0。当它是可选的，相关表的元组的最小参与可以是零，现在你面临着外键值被允许为空的必要性

但答案是，这完全取决于业务。

小开

这取决于foreign key在你的关系中扮演什么角色。

  1. 如果这个foreign key在你的关系中也是一个key attribute，那么它不能为NULL
  2. 如果这个foreign key在你的关系中是一个正常的属性，那么它可以是NULL。
小开

外键的思想是基于引用一个已经存在于主表中的值的概念。这就是为什么在另一个表中它被称为外键。这个概念被称为参考完整性。如果外键被声明为空字段，它将违反引用完整性的逻辑。它指的是什么?它只能引用主表中存在的内容。因此，我认为将外键字段声明为null是错误的。

小开

外键可以为NULL吗?

现有的答案集中在单列场景。如果考虑多列外键，则使用SQL Standard中定义的MATCH [SIMPLE | PARTIAL | FULL]子句有更多选项:

PostgreSQL-CREATE TABLE

插入到引用列中的值将使用给定的匹配类型与引用表和引用列的值进行匹配。有三种匹配类型:match FULL、match PARTIAL和match SIMPLE(这是默认的)。完全匹配将不允许多列外键中的一列为空，除非所有外键列都为空;如果它们都为空，则该行不需要在引用表中有匹配项。搭配简单的允许任何外键列为空;如果它们中的任何一个为空，则不要求在引用表中有匹配的行。匹配部分尚未实现。

(当然，可以对引用列应用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<>小提琴demo


提交答案