I can't create foreign keys with ON DELETE SET DEFAULT
, but if i use ON DELETE CASCADE
then all works here is my sql
CREATE TABLE person(
customer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) UNIQUE
);
CREATE TABLE habits(
customer_id INT AUTO_INCREMENT PRIMARY KEY,
habit VARCHAR(100) UNIQUE
);
INSERT INTO `test`.`habits` (`customer_id`, `habit`) VALUES (NULL, 'smoking'), (NULL, 'drinking');
INSERT INTO `test`.`person` (`customer_id`, `name`) VALUES (NULL, 'John'), (NULL, 'Steve');
CREATE TABLE foreigner(
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer VARCHAR(100) DEFAULT 'John',
habbit VARCHAR(100) DEFAULT 'smoking',
FOREIGN KEY (customer) REFERENCES person(name) ON DELETE SET DEFAULT ON UPDATE CASCADE,
FOREIGN KEY (habbit) REFERENCES habits(habit) ON DELETE SET DEFAULT ON UPDATE CASCADE
);
It's strange that it works if I use ON DELETE CASCADE
.... any thoughts?
From the MySQL Reference Manual...
SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.
You could try SET NULL, but I'm not sure if that will cause you other problems instead.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With