CODEWITHSUNDEEP
mysqlsqlkey
dvdciri
dvdciri

Reputation: 451

Error: Cannot add foreign key constraint on CREATE TABLE

When i run this, i get the error : Cannot add foreign key constraint Can anyone help me? thanks

CREATE SCHEMA `Rubik's_Cubes`;
USE `Rubik's_Cubes`;

/*Tabella Collezionista*/
CREATE TABLE Collezionista(
    id INTEGER auto_increment PRIMARY KEY,
    nome TEXT NOT NULL,
    cognome TEXT NOT NULL,
    telefono INTEGER NOT NULL,
    ranking INTEGER NOT NULL UNIQUE,
    id_ogg_pref INTEGER DEFAULT NULL,

    FOREIGN KEY (id_ogg_pref) REFERENCES Oggetto(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

/*Tabella Possiede*/
CREATE TABLE Possiede(
    id_collezionatore INTEGER NOT NULL,
    id_oggetto INTEGER NOT NULL,
    tempo_sol DOUBLE DEFAULT NULL,

    FOREIGN KEY (id_collezionatore) REFERENCES Collezionista(id)
        ON DELETE CASCADE
        ON UPDATE NO ACTION,

    FOREIGN KEY (id_oggetto) REFERENCES Oggetto(id)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);

/*Tabella Oggetto*/
CREATE TABLE Oggetto(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    nome TEXT NOT NULL,
    id_tipo INTEGER NOT NULL,
    id_tipo2 INTEGER DEFAULT NULL,
    valore DOUBLE NOT NULL,
    descrizione TEXT NOT NULL,
    detentore_record INTEGER DEFAULT NULL,
    tempo_record DOUBLE DEFAULT NULL,

    FOREIGN KEY (id_tipo) REFERENCES Tipologia(id)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,

    FOREIGN KEY (id_tipo2) REFERENCES Tipologia(id)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,

    FOREIGN KEY (detentore_record) REFERENCES Collezionista(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

/*Tabella Tipologia*/
CREATE TABLE Tipologia(
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    nome TEXT NOT NULL,
    descrizione TEXT NOT NULL,
    provenienza TEXT NOT NULL
);

Upvotes: 0

Views: 1004

Answers (2)

abhijitcaps
abhijitcaps

Reputation: 594

Your sequence of executing the Create statements are not correct. You try to add the constrainst related to a table which doesn't exists yet.

Example: Your are trying to create the Collezionista table and add foreign key contraint to table Oggetto, before even creating table Oggetto. So, you need to create the reference table or Parent table first, then create the child table.

Also, tables Oggetto and Collezionista are inter dependent. You need to remove FOREIGN KEY from table Oggetto and add as alter at last.

Pleas follow below sequence of execution:'

CREATE TABLE Tipologia(
id INTEGER AUTO_INCREMENT PRIMARY KEY,
nome TEXT NOT NULL,
descrizione TEXT NOT NULL,
provenienza TEXT NOT NULL
);

CREATE TABLE Oggetto(
id INTEGER AUTO_INCREMENT PRIMARY KEY,
nome TEXT NOT NULL,
id_tipo INTEGER NOT NULL,
id_tipo2 INTEGER DEFAULT NULL,
valore DOUBLE NOT NULL,
descrizione TEXT NOT NULL,
detentore_record INTEGER DEFAULT NULL,
tempo_record DOUBLE DEFAULT NULL,
FOREIGN KEY (id_tipo) REFERENCES Tipologia(id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
FOREIGN KEY (id_tipo2) REFERENCES Tipologia(id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE,
);


CREATE TABLE Collezionista(
id INTEGER auto_increment PRIMARY KEY,
nome TEXT NOT NULL,
cognome TEXT NOT NULL,
telefono INTEGER NOT NULL,
ranking INTEGER NOT NULL UNIQUE,
id_ogg_pref INTEGER DEFAULT NULL,
FOREIGN KEY (id_ogg_pref) REFERENCES Oggetto(id)
ON DELETE SET NULL
ON UPDATE CASCADE
);


CREATE TABLE Possiede(
id_collezionatore INTEGER NOT NULL,
id_oggetto INTEGER NOT NULL,
tempo_sol DOUBLE DEFAULT NULL,
FOREIGN KEY (id_collezionatore) REFERENCES Collezionista(id)
ON DELETE CASCADE
ON UPDATE NO ACTION,
FOREIGN KEY (id_oggetto) REFERENCES Oggetto(id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
);


ALTER TABLE Oggetto
ADD FOREIGN KEY (detentore_record) REFERENCES Collezionista(id)
ON DELETE SET NULL
ON UPDATE CASCADE

Hope it helps you.

Thanks,
Abhijit Das

Upvotes: 0

Asenar
Asenar

Reputation: 7030

You try to add a constraint related to a table which doesn't exists yet.

You should remove this on your create tables statements :

FOREIGN KEY (id_ogg_pref) REFERENCES Oggetto(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE

then add it at the end with an alter table . See that example for the 1st table: 

/*Tabella Collezionista*/
ALTER TABLE Collezionista
  ADD FOREIGN KEY (id_ogg_pref) REFERENCES Oggetto(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

Upvotes: 1

Related Questions