473,585 Members | 2,638 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(locat ion_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 5755

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(locat ion_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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2620
by: Jeremiah Jacks | last post by:
I just upgraded to MySQL 4.0.14-standard for RedHat Linux and am using = the pre-compiled binaries. I have a database with INNODB tables. When I insert a row into one of the child tables, I get the following = MySQL error: INSERT INTO product_access_level (product_id,access_level_id) VALUES
2
39418
by: Gunnar Vøyenli | last post by:
Hi! For the sake of simplicity, I have three tables, Employee, Department and Work Employee >---- Department \ / \ / ^ ^ Work
31
3352
by: Robert Brown | last post by:
Let's say I have a type hierarchy: (just an example) the general entity customer: CREATE TABLE customer(customer_id int, customer_name varchar(250), customer_type int) three specific customer subtypes: 1 - business, 2 - home, 3 - university
0
1418
by: Scott Ribe | last post by:
I've got a problem which I think may be a bug in Postgres, but I wonder if I'm missing something. Two tables, A & B have foreign key relations to each other. A 3rd table C, inherits from A. A stored procedure updates a row in C, adds a row each in B & C. I get an integrity violation. All the foreign keys are deferrable, and the stored...
5
5007
by: Gerald Khin | last post by:
I encountered a performance problem with UPDATE statement and foreign key constraints. There are two tables involved: A parent table A and a child table B: create table A( ID CHAR(15) NOT NULL CONSTRAINT APK PRIMARY KEY, val NUMERIC(10)) create table B(ID CHAR(15) NOT NULL CONSTRAINT BPK PRIMARY KEY,
2
8097
by: Ev | last post by:
I have a database table in SQL Server that has a self join. In C# I have a DataTable with a self-join. I have defined a foreign key constraint on the DataTable for the self join. The AcceptRejectRule is set to Cascade Table Structure: OrderID (Identity field) OrderDesc OrderParent (this is the self-join - it is a foreign key to OrderID) ...
10
2070
by: Shawn Chisholm | last post by:
Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the foreign keys are locked (or evaluated) in for a particular table? Deferring the locks is unfortunately not a good option for me... Thanks, Shawn ---------------------------(end of...
11
10132
by: FreeToGolfAndSki | last post by:
Hi, I have an Orders Table that has Employee1 and Employee2 (one is the sales rep the other is the telemarketing rep) Both of these fields need to cascade update against the Employees table. I can't seem to create the desired relationship in a Diagram and I'm not sure how best to set this up. Any ideas? Thanks in advance...
5
2487
by: Bob Stearns | last post by:
For good and sufficient reasons I wish to insure that a primary key of table 1 is not a primary key of table 2. The following does not work: ALTER TABLE IS3.AUCTION_SUPER_CATEGORIES ADD CONSTRAINT code_not_fk check(code not in (select code from IS3.AUCTION_CATEGORIES where auction_id=auction_id)) Is there any way other than a trigger...
1
2127
by: poopsy | last post by:
hello all i am getting confused with foreign key constraints.. i have the following table: -- -- Table structure for table `reviewer` -- CREATE TABLE `reviewer` (
0
7900
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8192
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7943
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8204
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5705
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5382
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3828
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2338
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1167
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.