Mysql – How to insert data into tables which reference each other?(MySQL)

constraintforeign keyinsertMySQL

I'm a student and right now I have a problem that I couldn't solve by myself(after searching on the Internet).

I'm not going to give my «homework» because I want to learn what I'm doing but I will use an example.

I have the following diagram:
enter image description here

And the following:

Employee(ID,DepartamentID,Floor)
foreign key(DepartamentID, Floor) references Departament(DepartamentID,Floor)

Departament(DepartamentID,Floor, Attendant)
foreign key Attendant references Employee(ID)

So with this guidelines, my actual SQL code is:

CREATE TABLE Employee(
    ID varchar(25) not null,
    DepartamentID varchar(25),
    Floor varchar(25),
    CONSTRAINT pk_ID PRIMARY KEY (ID),
) ENGINE=InnoDB;

CREATE TABLE Departament(
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,
    Attendant varchar(25) not null,
    CONSTRAINT pk_dept PRIMARY KEY (DepartamentID, Floor),
    CONSTRAINT fk_att FOREIGN KEY (Attendant) REFERENCES Employee (ID),
) ENGINE=InnoDB;

ALTER TABLE Employee
ADD CONSTRAINT fk_dept FOREIGN KEY (DepartamentID, Floor) REFERENCES Departament (DepartamentID, Floor);

I think, this way, I accomplish what I have to accomplish as the information given says, but when I try to insert data like:

INSERT INTO Employee (ID, DepartamentID,Floor) VALUES ('123456789-Z', 'IT', 'roof');

or

INSERT INTO Departament (DepartamentID, Floor, Attendant) VALUES ('IT', 'roof', '123456789-Z');

I get the following message:

1452 – Cannot add or update a child row: a foreign key constraint fails

After searching and searching, I realized that is not possible to insert data into a table that expects data from another table and that another table is empty.

I thought that I could do the alter table before the inserts but the teacher wants a create.sql file and a inserts.sql file, separately, so I cannot do it this way.

I need to find a solution to accomplish this and stackexchange is my last hope.

Thanks to anyone that read this, thank you very much.

PD: Sorry if the question is too long, I tried to explain it the best I can(english is not my native language) but if I have to explain something again, sure I will!

Best Answer

Not sure I understood the task, but here is some food for thought for an alternative schema:

CREATE TABLE Departament(
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,
        CONSTRAINT pk_dept PRIMARY KEY (DepartamentID, Floor),
) ENGINE=InnoDB;


CREATE TABLE Employee(
    ID varchar(25) not null,
    DepartamentID varchar(25),
    Floor varchar(25),
        CONSTRAINT pk_ID PRIMARY KEY (ID),
        CONSTRAINT fk_dept FOREIGN KEY (DepartamentID, Floor) 
            REFERENCES Departament (DepartamentID, Floor)
) ENGINE=InnoDB;

CREATE TABLE Attendant (
    ID varchar(25) not null,
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,

    CONSTRAINT pk_attendent PRIMARY KEY (ID),
    CONSTRAINT ak_attendent UNIQUE (DepartamentID, Floor),
    CONSTRAINT fk_... REFERENCES Employee ...,
    CONSTRAINT fk_... REFERENCES Department ...,
) ENGINE=InnoDB;
Related Question