ON DELETE CASCADE含NULL列时失效，求简便解决方法

SQL中NULL的比较规则是NULL = NULL返回UNKNOWN，而外键关联的匹配条件要求结果为TRUE。你创建的外键基于(Column1, Column2)，当父表和子表的Column2都是NULL时，两者不会被视为匹配的关联记录，因此删除父表记录时，级联删除不会触发子表的删除操作。

方案1：用特殊标记值替代NULL #

将Column2设为非空，用一个业务上不可能出现的数值（比如-9999）代替NULL，这样唯一约束和外键就能正常匹配，级联删除生效：

CREATE TABLE Parent (
  Column1 INT NOT NULL,
  Column2 INT NOT NULL DEFAULT (-9999) -- 设定默认值，避免手动输入
)

CREATE TABLE Child (
  Column1 INT NOT NULL,
  Column2 INT NOT NULL DEFAULT (-9999),
  AdditionalInfo INT NOT NULL
)

ALTER TABLE Parent ADD CONSTRAINT UQ UNIQUE CLUSTERED (Column1, Column2) 

ALTER TABLE Child
ADD CONSTRAINT [FK_Parent_Child] FOREIGN KEY(Column1, Column2) 
REFERENCES Parent(Column1, Column2)
ON DELETE CASCADE

INSERT INTO Parent (Column1, Column2) VALUES(1, -9999)
INSERT INTO Child (Column1, Column2, AdditionalInfo) VALUES(1, -9999, 1)

DELETE FROM Parent WHERE Column1=1 AND Column2 = -9999

SELECT * FROM Child -- 结果为空，符合预期

方案2：通过计算列转换NULL #

保留原字段的NULL属性，给父表和子表添加持久化计算列，将NULL转换为特定值，基于计算列创建唯一约束和外键：

CREATE TABLE Parent (
  Column1 INT NOT NULL,
  Column2 INT NULL,
  Column2_NotNull AS ISNULL(Column2, -9999) PERSISTED -- 持久化计算列，支持索引
)

CREATE TABLE Child (
  Column1 INT NOT NULL,
  Column2 INT NULL,
  Column2_NotNull AS ISNULL(Column2, -9999) PERSISTED,
  AdditionalInfo INT NOT NULL
)

ALTER TABLE Parent ADD CONSTRAINT UQ UNIQUE CLUSTERED (Column1, Column2_NotNull) 

ALTER TABLE Child
ADD CONSTRAINT [FK_Parent_Child] FOREIGN KEY(Column1, Column2_NotNull) 
REFERENCES Parent(Column1, Column2_NotNull)
ON DELETE CASCADE

INSERT INTO Parent (Column1, Column2) VALUES(1, NULL)
INSERT INTO Child (Column1, Column2, AdditionalInfo) VALUES(1, NULL, 1)

DELETE FROM Parent WHERE Column1=1 AND Column2 IS NULL

SELECT * FROM Child -- 结果为空，符合预期

方案3：用触发器实现级联删除 #

如果不想修改表结构，可以给父表创建删除触发器，手动处理NULL的匹配逻辑：

CREATE TABLE Parent (
  Column1 INT NOT NULL,
  Column2 INT NULL
)

CREATE TABLE Child (
  Column1 INT NOT NULL,
  Column2 INT NULL,
  AdditionalInfo INT NOT NULL
)

ALTER TABLE Parent ADD CONSTRAINT UQ UNIQUE CLUSTERED (Column1, Column2) 

ALTER TABLE Child
ADD CONSTRAINT [FK_Parent_Child] FOREIGN KEY(Column1, Column2) 
REFERENCES Parent(Column1, Column2)

-- 创建触发器处理NULL匹配
CREATE TRIGGER trg_Parent_Delete ON Parent
AFTER DELETE
AS
BEGIN
  SET NOCOUNT ON;
  DELETE c
  FROM Child c
  JOIN deleted d ON c.Column1 = d.Column1
  WHERE (c.Column2 IS NULL AND d.Column2 IS NULL) OR c.Column2 = d.Column2;
END

INSERT INTO Parent (Column1, Column2) VALUES(1, NULL)
INSERT INTO Child (Column1, Column2, AdditionalInfo) VALUES(1, NULL, 1)

DELETE FROM Parent WHERE Column1=1 AND Column2 IS NULL

SELECT * FROM Child -- 结果为空，符合预期

内容的提问来源于stack exchange，提问作者andarvi