RE: Problem adding a foreign key

>>I'm not DOING anything to the 'contract' table; and finderr for 106 says: Yes, you are "doing" something to 'contract': alter table "dba".proj_request add constraint (foreign key (cntrctid_ref) references "dba".contract constraint "dba".proj_request_fk_02); >> the only table I'm altering is 'proj_request'. True, that is the table affected by the alter statement, but how is informix gonna "understand" the constraint if it doesn't look at (lock?) the contract table as it puts the foreign key on the proj_request table?... When informix builds an index, it locks the table... Foreign keys are fancy indices... Not surprising that informix would lock both tables as it builds info for the constraint. My explanation isn't very techie.... I'm sure one of the real guru's can give us the "deep dive" on this one... Thanks, NJ -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Malc P Sent: Monday, November 22, 2004 6:44 AM To: informix-list@iiug.org Subject: Problem adding a foreign key IDS 9.30HC5; HP-UX 11i Not a problem, really, (I can kick the users off) but I can't understand why issuing this: set isolation to dirty read;alter table "dba".proj_request add constraint (foreign key (cntrctid_ref) references "dba".contract constraint "dba".proj_request_fk_02); ... produces this: 242: Could not open database table (dba.contract). 106: ISAM error: non-exclusive access. I'm not DOING anything to the 'contract' table; and finderr for 106 says: "The ISAM processor has been asked to add or drop an index but it does not have exclusive access. ... For SQL products, the database server returns this error when an exclusive lock is required on a table. For example, this error appears when a second user tries to alter a table that the first user has locked." But AFAICS, the only table I'm altering is 'proj_request'. Confused of Tunbridge Wells. (Also posting to the ids list) ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================ sending to informix-list