CODEWITHSUNDEEP
mysqlsqldatabasedatabase-design
bukowski
bukowski

Reputation: 1943

Can't create foreign key with ON DELETE SET DEFAULT

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?

Upvotes: 5

Views: 7917

Answers (1)

MatBailie
MatBailie

Reputation: 86765

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.

Upvotes: 4

Related Questions