Search code examples
mysqlsqlmariadbmariasql

Can't create table 'mydb.contact' (errno: 150)

Please, this is my sql script

#-- creation de la table user
CREATE TABLE IF NOT EXISTS user(
  iduser int AUTO_INCREMENT,
  nom VARCHAR(50) NOT NULL,
  prenom VARCHAR(50) ,
  adressemail VARCHAR(200) NOT NULL,
  motdepasse VARCHAR(200) NOT NULL,
  CONSTRAINT pk_user PRIMARY KEY(iduser)
);


#-- creation de la table user
CREATE TABLE IF NOT EXISTS contact(
  idcontact INT AUTO_INCREMENT,
  nom VARCHAR(45) NOT NULL,
  prenom VARCHAR(45),
  adressemail VARCHAR(200) UNIQUE,
  user_iduser INT NOT NULL,
  CONSTRAINT pk_contact PRIMARY KEY(idcontact),
  CONSTRAINT fk_contact_userIduser_user FOREIGN KEY (user_iduser) REFERENCES user(iduser) ON DELETE SET NULL ON UPDATE CASCADE
);

When i executing it on my maria db database, I get this error:

Can't create table 'mydb.contact' (errno: 150)

Solution

  • In your foreign key constraint you have set the action on delete to set null, but the column user_iduser does not allow null values as it is specified as not null which makes the constraint invalid. Either change the column to allow null values or change the delete action in the constraint.

    The online MySQL manual even has a warning about this:

    If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.