InnoDB mySQL unable to set "ON DELETE SET DEFAULT'. How to set?
Solution 1
You can't use ON DELETE SET DEFAULT
or ON UPDATE SET DEFAULT
with InnoDB
InnoDB and FOREIGN KEY Constraints
While SET DEFAULT is allowed by the MySQL Server, it is rejected as invalid by InnoDB. CREATE TABLE and ALTER TABLE statements using this clause are not allowed for InnoDB tables.
You may try ON DELETE SET NULL
if it fits your needs
If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep
Here is SQLFiddle demo
Solution 2
just use SET NULL rule and define trigger which will try to set to default value with exception handler if your default value has been deleted from master table
Nivash
Updated on June 18, 2022Comments
-
Nivash almost 2 years
I am trying to create a table named EMPLOYEE. When I use the following statements without "ON DELETE SET DEFAULT" it is working.
Here is the Error I get with "ON DELETE SET DEFAULT":
ERROR 1005 (HY000): Can't create table 'COMPANY.EMPLOYEE' (errno: 150)
Here is the DDL
CREATE TABLE EMPLOYEE ( Fname VARCHAR(15) NOT NULL, Minit CHAR, Lname VARCHAR(15) NOT NULL, Ssn CHAR(9) NOT NULL DEFAULT '123456789', Bdate DATE, ADDRESS VARCHAR(30), Sex CHAR, Salary DECIMAL(10,2), Super_Ssn CHAR(9) NOT NULL DEFAULT '123456789', Dno INT NOT NULL DEFAULT -99, PRIMARY KEY (Ssn), FOREIGN KEY (Super_Ssn) REFERENCES COMPANY.EMPLOYEE(Ssn) ON DELETE SET DEFAULT ON UPDATE CASCADE )ENGINE=InnoDB;
Please help me!!! and Thanks in advance :)