469,945 Members | 2,285 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,945 developers. It's quick & easy.

Foreign key on the same table update problem

Hello,
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.

Nov 15 '06 #1
1 5645

na*****************@gmail.com a écrit :
Hello,
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
Please,I need a quick response if possible!

Nov 17 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jeremiah Jacks | last post: by
2 posts views Thread by Gunnar Vøyenli | last post: by
10 posts views Thread by Shawn Chisholm | last post: by
11 posts views Thread by FreeToGolfAndSki | last post: by
5 posts views Thread by Bob Stearns | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.