2

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!

1

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;
  • 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 7 '14 at 5:08
  • Ah, sorry about that. I'll have another look – Lennart Jun 7 '14 at 5:40
  • I've updated the example. – Lennart Jun 7 '14 at 5: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 7 '14 at 5: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 Jun 7 '14 at 6:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.