SQL query: cannot add or update a child row

1

Hi, I am creating a complex database using PopSQL. Here I have created a table ‘employee’, and another table ‘branch’. The table "employee’ contains a foreign key ‘branch_id’ which is the primary key of the "branch’ table.So I tried to update the branch_id of the first record in “employee” after inserting the required values into “branch”, which is 1 in this case

INSERT INTO employee VALUES(100, "David", "Wallace", "1967-11-17", "M", 250000, NULL, NULL);
INSERT INTO branch VALUES(1, "Corporate", 100, "2006-02-09");
INSERT INTO employee VALUES(101, "Jan", "Lavinson", "1961-05-11", "F", 110000, 100, NULL);

UPDATE employee
SET branch_id = 1
WHERE emp_id = 100;

But it shows that I have had a constraint error. Then I also failed to add another record that has the same branch_id value, ( if the branch_id is set to null, the record can be entred)
failure
So I re-added the foreign key of “branch_id”,


ALTER TABLE employee
ADD FOREIGN KEY(branch_id)
REFERENCES branch(branch_id)
ON DELETE SET NULL;

This query has been accepted, but I still get the same error message. Can someone tell me why, thanks a lot!

2

Wow, what a dilemma. I too have that problem with that same group of fields and values. I’m in a tutorial by Mike Dane about learning MySQL via PopSQL and I assume that you (Vyskoc) were also. I’ll be waiting with you for any suggestions.