CREATE TABLE employee(
emp_id INT PRIMARY KEY,
first_name VARCHAR(40),
last_name VARCHAR(40),
birth_day DATE,
Sex VARCHAR(1),
super_id INT,
branch_id INT
);
ALTER TABLE employee
ADD COLUMN salary INT AFTER sex;
ALTER TABLE employee
ADD CONSTRAINT FOREIGN KEY (branch_id) references branch(branch_id) ON DELETE SET NULL;
CREATE TABLE branch (
branch_id INT PRIMARY KEY,
branch_name VARCHAR(40),
mgr_id INT ,
mgr_start_date DATE,
FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL
);
— I MADE TWO TABLES AND TRIED TO INSERT SOME VALUES:
INSERT INTO employee VALUES(100,’David’,’Wallace’,’1967-11-17′,’M’,250000,NULL,NULL);
INSERT INTO branch VALUES(1,’Corporate’,100,’2006-02-09′);
UPDATE employee
SET branch_id = 1
WHERE emp_id = 100;
— BUT I AM ENCOUNTERING AN ERROR THAT IS :
01317:05:44UPDATE employee SET branch_id = 1 WHERE emp_id = 100Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (company
.employee
, CONSTRAINT employee_ibfk_1
FOREIGN KEY (branch_id
) REFERENCES employee
(emp_id
) ON DELETE SET NULL)0.016 sec
CAN ANYONE SUGGEST SOME SOLUTIONS?
CAN ANYONE TELL WHY THE ERROR MESSAGE IS COMING?