CODEWITHSUNDEEP
mysqlsqldatabase-designforeign-key-relationshipddl
piupiu34
piupiu34

Reputation: 21

error with "ON DELETE SET NULL" mysql mamp

i'm trying to add a "ON DELETE SET NULL" on my sql script, but it doesn't work...
Here is my table:

enter image description here

Serie table is for description of the serie and type table is for the type of this series (drama, comedy, etc.) The sql script is: 

    #------------------------------------------------------------
    #        Script MySQL.
    #------------------------------------------------------------


    #------------------------------------------------------------
    # Table: serie
    #------------------------------------------------------------

    CREATE TABLE serie(
            id_serie            Int NOT NULL ,
            original_name_serie Varchar (256) ,
            french_name_serie   Varchar (256) ,
            creation_date_serie Int ,
            end_date_serie      Int ,
            status_serie        Varchar (256) ,
            duration_serie      Int ,
            synopsis_serie      Varchar (2024) ,
            countries_serie     Varchar (128) ,
            PRIMARY KEY (id_serie )
    )ENGINE=InnoDB;


    #------------------------------------------------------------
    # Table: type
    #------------------------------------------------------------

    CREATE TABLE type(
            id_type   Int NOT NULL ,
            name_type Varchar (128) ,
            PRIMARY KEY (id_type )
    )ENGINE=InnoDB;


    #------------------------------------------------------------
    # Table: have
    #------------------------------------------------------------

    CREATE TABLE have(
            id_type  Int ,
            id_serie Int NOT NULL ,
            PRIMARY KEY (id_type ,id_serie )
    )ENGINE=InnoDB;

    ALTER TABLE have ADD CONSTRAINT FK_have_id_type FOREIGN KEY (id_type) REFERENCES type(id_type) ON DELETE SET NULL;
    ALTER TABLE have ADD CONSTRAINT FK_have_id_serie FOREIGN KEY (id_serie) REFERENCES serie(id_serie) ON DELETE CASCADE;

With this script, I want when I delete a 'type' in the type table, the value of 'type' in 'have' table passed to 'null', this is why I add "ON DELETE SET NULL". But it doesn't work, I have: "#1005 - Can't create table 'zz.#sql-16527_272' (errno: 150) (Détails…) "

I think it's maybe i tried to put a null value in a primary key in the 'have' table and it's not possible... But who can I do this?

Upvotes: 1

Views: 2419

Answers (2)

Mureinik
Mureinik

Reputation: 311808

The id_type column in the type table is the table's primary key, and thus cannot be set to null.

One way to solve this could be to change the logic of the foreign key (e.g., use on delete cascade, or just allow deletions to fail if the referenced row still exists).

Another option would be to remove the primary key from the type table and instead just have a unique key on that column, thus allowing nulls. You'd also have to remove the explicit not nullconstraint on this column.

Upvotes: 3

MrTux
MrTux

Reputation: 34002

The column type is defined to be NOT NULL, therefore you cannot insert NULL. Remove this from the definition and it should work.

Upvotes: 2

Related Questions