Oninit cdi / Archive
Talk to an expert →

Problem adding a foreign key

Posted in 2004

Topics: Error Codes & Troubleshooting, Server Administration, Triggers, Constraints & Referential Integrity, Transactions, Locking & Isolation, Platform-Specific Issues

Malcolm Perrior — — source: IIUG Forums & Mailing Lists

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 cdi)

↪ replying to Malcolm Perrior
Uri Haham — — source: IIUG Forums & Mailing Lists

Hi When you create foreign key Informix create under it an index. Uri Malcolm Per.... wrote: > 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 cdi) > > >*********************************************** >This Mail Was Scanned By Mail-seCure System in > Matrix Herzeliya >*********************************************** > > > >

↪ replying to Uri Haham
Gerd Kaluzinski — — source: IIUG Forums & Mailing Lists

Informix creates an index on proj_request, but on the referenced table contract, whre the error message comes from the primary key is already there. Why take an exlusice lock on that table where the index needs no modification ? Uri Haham wrote: >Hi > >When you create foreign key Informix create under it an index. > >Uri > >Malcolm Per.... wrote: > > > >>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 cdi) >> >> >>*********************************************** >>This Mail Was Scanned By Mail-seCure System in >> Matrix Herzeliya >>*********************************************** >> >> >> >> >> >> > > > > > -- Mit freundlichen Grüßen Gerd Kaluzinski TAUSCHEN SIE KOSTENLOS IHRE ALTE ORACLE, SYBASE, MSSQL, ... - Datenbank gegen die neue INFORMIX DYNAMIC SERVER 9 ein !!! Für nähere Informationen stehen wir Ihnen gerne zur Verfügung. ACHTUNG: Dieses Angebot gilt nur befristete Zeit ! \\\\\\\\|// (o o) --------------------------------------------------ooO-(_)-Ooo--- Gerd Kaluzinski mailto:support@bytec.de http://www.bytec.de BYTEC GmbH Telefon: 07541-585-1019 Hermann-Metzger-Str. 7 Fax : 07541-585-2019 88045 Friedrichshafen Ooo. -------------------------------------------------.ooO----( )--- ( ) (_/ \\\\_)

↪ replying to Gerd Kaluzinski
Clifton M. Bean — — source: IIUG Forums & Mailing Lists

You are asking for a foreign key to be added, thus you are wanting to make sure that all values in the foreign key be within the key on the contract table. The contract table needs to be LOCKED in order for the foreign key values being checked can be verified as being within the key in the contract table ... and to make sure THAT LIST OF VALUES does not change during the creation of the foreign key. Hope that brings in the sunshine (on a rainy day in Dallas, TX). Clifton "Gerd Kaluzi...." <gerd.kaluzinski@bytec.de> wrote: Informix creates an index on proj_request, but on the referenced table contract, whre the error message comes from the primary key is already there. Why take an exlusice lock on that table where the index needs no modification ? Uri Haham wrote: >Hi > >When you create foreign key Informix create under it an index. > >Uri > >Malcolm Per.... wrote: > > > >>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 cdi) >> >> >>*********************************************** >>This Mail Was Scanned By Mail-seCure System in >> Matrix Herzeliya >>*********************************************** >> >> >> >> >> >> > > > > > -- Mit freundlichen Grüßen Gerd Kaluzinski TAUSCHEN SIE KOSTENLOS IHRE ALTE ORACLE, SYBASE, MSSQL, ... - Datenbank gegen die neue INFORMIX DYNAMIC SERVER 9 ein !!! Für nähere Informationen stehen wir Ihnen gerne zur Verfügung. ACHTUNG: Dieses Angebot gilt nur befristete Zeit ! \\\\\\\\|// (o o) --------------------------------------------------ooO-(_)-Ooo--- Gerd Kaluzinski mailto:support@bytec.de http://www.bytec.de BYTEC GmbH Telefon: 07541-585-1019 Hermann-Metzger-Str. 7 Fax : 07541-585-2019 88045 Friedrichshafen Ooo. -------------------------------------------------.ooO----( )--- ( ) (_/ \\\\_)

Glossary