Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

InnoDB mySQL unable to set "ON DELETE SET DEFAULT'. How to set?

Tags:

sql

mysql

innodb

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 :)

like image 278
Nivash Avatar asked Jan 06 '14 05:01

Nivash


People also ask

How do I use the default delete set?

ON DELETE SET DEFAULT SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified.

What is the default for on delete?

For an ON DELETE or ON UPDATE that is not specified, the default action is always NO ACTION .

Is on delete cascade by default?

Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.

How do you update a table that has a foreign key?

Login to the SQL Server using SQL Server Management Studio, Navigate to the Keys folder in the child table. Right click on the Keys folder and select New Foreign Key. Edit table and columns specification by clicking … as shown in the below image. Select the parent table and the primary key column in the parent table.


1 Answers

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

like image 110
peterm Avatar answered Sep 22 '22 18:09

peterm