-2

Please refer to this 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);

I have multiple tables that have composite Primary Key.

All PK are PRIMARY KEY (master_id, id)

And now I try to add Foreign Keys to these tables.

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;

But it throws error Cannot add foreign key constraint.

Here parent.master_id always must stay NOT NULL while c1_id and c2_id can be set to NULL.

Is it possible to achieve this kind of Foreign Key setup?
I found that there is MATCH SIMPLE option that allows composite key to be partial NULL, but how to apply it for ON DELETE SET NULL?

Justinas
  • 41,402
  • 5
  • 66
  • 96
  • It's nice that you thought of making the post tidy by hiding the code in a snippet but it doesn't add much benefit since the code needs to be read & isn't very big & it makes the code bigger when shown. It would be helpful if you clearly explained what the opening link & snippet button/link are--we have to click & read to find out what they are & how they are related. You also don't make it clear that the problem is that the null cascade violates the "must" you mention. PS Please don't delete & repost questions, edit them. – philipxy Jul 29 '20 at 21:18
  • We can expect that this is a faq. Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. PS Match simple mode is the default & usually the only mode implemented. In all modes, one null makes a FK constraint succeed. But match modes don't affect cascading. – philipxy Jul 29 '20 at 21:23

1 Answers1

0

Both referring Column are NOT NULL, so you can't use ON DELETE SET NULL, this violates the 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;
nbk
  • 45,398
  • 8
  • 30
  • 47
  • Yeah they know, although they didn't present the problem clearly, they want an alternative. – philipxy Jul 29 '20 at 21:17
  • there is no alternative to that, no default values, so only a noaction cascaed or restrict but no alternative. – nbk Jul 29 '20 at 21:19