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:
And the following:
Floor) references Departament(DepartamentID,Floor)
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');
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!