5

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!

Community
  • 1
user47579
  • 177
  • 1
  • 2
  • 5

1 Answers1

3

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;
Lennart - Slava Ukraini
  • 23,240
  • 3
  • 32
  • 69
  • Hello Lennart. Thanks for your answer first. I would like to do what you suggest! But the problem is that I have:
    Employee(ID,DepartamentID,Floor) foreign key(DepartamentID, Floor) references Departament(DepartamentID,Floor) 
    Departament(`DepartamentID`,`Floor`, Attendant)

    foreign key Attendant references Employee(ID)

    So I cannot create an extra table :/

     – user47579 Jun 07 '14 at 05:08
  • Ah, sorry about that. I'll have another look – Lennart - Slava Ukraini Jun 07 '14 at 05:40
  • I've updated the example. – Lennart - Slava Ukraini Jun 07 '14 at 05:46
  • Thanks for your efforts Lennart, is it possible to use only two tables instead of three? I mean, not using the Attendant one you suggest. – user47579 Jun 07 '14 at 05:55
  • You can use a transaction like: `insert into employee (id ) values ('Lennart'); insert into department (id, floor,attendant) values ('Whatever','45', 'Lennart'); update employee set (departmentid, floor) = ('Whatever','45') where id = 'Lennart'; – Lennart - Slava Ukraini Jun 07 '14 at 06:01
  • Oh my god Lennart, I forgot I could do that! I solved it with the transaction!

    Thank you really much, I put your answer like accepted but in reality what I accept is the transaction comment!

    Thanks again, really, you saved my ass :P

     – user47579 Jun 07 '14 at 06:54