Yes foreign key can be null as told above by senior programmers... I would add another scenario where Foreign key will required to be null.... suppose we have tables comments, Pictures and Videos in an application which allows comments on pictures and videos. In comments table we can have two Foreign Keys PicturesId, and VideosId along with the primary Key CommentId. So when you comment on a video only VideosId would be required and pictureId would be null... and if you comment on a picture only PictureId would be required and VideosId would be null...

1 -是的，因为至少SQL Server 2000。

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

Yes foreign key can be null as told above by senior programmers... I would add another scenario where Foreign key will required to be null.... suppose we have tables comments, Pictures and Videos in an application which allows comments on pictures and videos. In comments table we can have two Foreign Keys PicturesId, and VideosId along with the primary Key CommentId. So when you comment on a video only VideosId would be required and pictureId would be null... and if you comment on a picture only PictureId would be required and VideosId would be null...

外键可以为NULL吗?

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

PostgreSQL-CREATE TABLE A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table. MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table. MATCH PARTIAL is not yet implemented. (Of course, NOT NULL constraints can be applied to the referencing column(s) to prevent these cases from arising.)

例子:

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 < > fiddle演示

简单地说，实体之间的“非识别”关系是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中没有任何记录的记录。

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

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