请参阅此SQLFiddle
CREATE TABLE `parent` (
`id` varchar(64) NOT NULL,
`master_id` varchar(64) NOT NULL,
`c1_id` varchar(64) DEFAULT NULL,
`c2_id` varchar(64) DEFAULT NULL,
PRIMARY KEY (`master_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `child_1` (
`id` varchar(64) NOT NULL,
`master_id` varchar(64) NOT NULL,
PRIMARY KEY (`master_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `child_2` (
`id` varchar(64) NOT NULL,
`master_id` varchar(64) NOT NULL,
PRIMARY KEY (`master_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO child_1 (`id`, `master_id`)
VALUES
(1, 'm1');
INSERT INTO child_2 (`id`, `master_id`)
VALUES
(2, 'm2'),
(3, 'm1');
INSERT INTO parent (`id`, `master_id`, `c1_id`, `c2_id`)
VALUES
(4, 'm1', null, null),
(5, 'm1', 1, null),
(6, 'm2', null, 2);
我有多个具有复合主键的表。
所有PK均为PRIMARY KEY (master_id, id)
现在我尝试将外键添加到这些表中。
ALTER TABLE `parent`
ADD CONSTRAINT parent_fk_1
FOREIGN KEY (`master_id`, `c1_id`)
REFERENCES child_1 (`master_id`, `id`)
ON UPDATE CASCADE
ON DELETE SET NULL;
但它抛出错误Cannot add foreign key constraint
。
这里parent.master_id
必须始终保持NOT NULL
,而c1_id
和c2_id
可以设置为NULL
。
有可能实现这种外键设置吗
我发现有MATCH SIMPLE
选项允许复合密钥是部分NULL
,但如何将其应用于ON DELETE SET NULL
?
两个引用列都是NOT NULL,因此不能使用ON DELETESET NULL
,这违反了NOT NULL
ALTER TABLE `parent`
ADD CONSTRAINT parent_fk_1
FOREIGN KEY (`master_id`, `c1_id`)
REFERENCES child_1 (`master_id`, `id`)
ON UPDATE CASCADE;