mysql - 无法使用 ON DELETE SET DEFAULT 创建外键

Question

我无法使用 ON DELETE SET DEFAULT 创建外键，但是如果我使用 ON DELETE CASCADE 那么这里所有的工作都是我的 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     
);

奇怪的是，如果我使用 ON DELETE CASCADE ....有什么想法吗？

Answer 1

来自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.

您可以尝试 SET NULL，但我不确定这是否会给您带来其他问题。