Foreign key on the same table update problem
am 15.11.2006 15:25:17 von nassim.bouayad.aghaHello,
here is my table declaration :
CREATE TABLE staff_member(
pay_id varchar(64) NOT NULL default '',
manager_pay_id varchar(64),
location_name varchar(64),
first_name varchar(64) NOT NULL default '',
last_name varchar(64) NOT NULL default '',
e_mail varchar(64) NOT NULL default '',
primary key (pay_id),
FOREIGN KEY (manager_pay_id) REFERENCES staff(pay_id)
ON DELETE SET NULL
ON UPDATE CASCADE
,
FOREIGN KEY (location_name) REFERENCES locations(location_name)
ON DELETE SET NULL
ON UPDATE CASCADE,
type ENUM('Manager', 'Normal') NOT NULL default 'Normal',
is_instructor ENUM('N', 'Y') NOT NULL default 'N'
);
A staff member has the 'manager_pay_id' field that points to another
staff member ('pay_id').This works perfectly but when I try to update
the 'pay_id' of a staff member that is pointed by another staff_member,
the update fails with error 1451.This not append with delete.Any idea?
Thank you in advance.
Best regard.
Nassim.