Problems when adding foreign keys

Hi ! We are using version 11.50.FC6W2 on HP-UX 11.31 I have on several occasions run into a problem when adding foreign keys, the problem is the following. I have a table A with a primary key. I now add a table B that has a foreign key constraint to the primary key on table A. Problem 1. If it is not possible to lock table A in exclusive mode I can't add the foreign key. Why is there a need for an exclusive lock on table A in this situation ? Problem 2. If I succed to add the foreign key I get -710 errors when trying to update table A.Why the -710 error on table A ? The solution I have found is to do an update statistics on table A. Is there other ways around the problem ? The behavior might have changed in later versions. TIA Ulf

Ulf Any change of the table structure requires an exclusive lock and adding a constraint (such as a foreign key) is such a change. -710 simply indicates the structrue of the table has changed. by adding a foreign key !!, since the last time that process accessed the table. If you had restarted the process, so that it connected to the new 'version' of the table , you would not have seen the error. Also it should not appear on any new connections. Keith On 2 August 2011 07:56, Ulf <ulf.akerberg@gmail.com> wrote: > > Hi ! > > We are using version 11.50.FC6W2 on HP-UX 11.31 > > I have on several occasions run into a problem when adding foreign > keys, the problem is the following. > > I have a table A with a primary key. I now add a table B that has a > foreign key constraint to the primary key on table A. > > Problem 1. If it is not possible to lock table A in exclusive mode I > can't add the foreign key. Why is there a need for an exclusive lock > on table A in this situation ? > > Problem 2. If I succed to add the foreign key I get -710 errors when > trying to update table A.Why the -710 error on table A ? > The solution I have found is to do an update statistics on table A. Is > there other ways around the problem ? > > > The behavior might have changed in later versions. > > TIA > > Ulf > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

Ulf: See my responses below: Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 2, 2011 at 2:56 AM, Ulf <ulf.akerberg@gmail.com> wrote: > > Hi ! > > We are using version 11.50.FC6W2 on HP-UX 11.31 > > I have on several occasions run into a problem when adding foreign > keys, the problem is the following. > > I have a table A with a primary key. I now add a table B that has a > foreign key constraint to the primary key on table A. > > Problem 1. If it is not possible to lock table A in exclusive mode I > can't add the foreign key. Why is there a need for an exclusive lock > on table A in this situation ? > This is because Informix wants to validate all of the foreign keys in the independent table (so table A) and does not want to have to deal with the keys changing (specifically being deleted after having been verified) during the check. > > Problem 2. If I succed to add the foreign key I get -710 errors when > trying to update table A.Why the -710 error on table A ? > The solution I have found is to do an update statistics on table A. Is > there other ways around the problem ? > This is because you are updating using prepared statements that were prepared before the foreign key constraint on table B existed. Since the update MAY change columns in table A that are foreign keys in table B now the query plan that was prepared is now invalid so the -710 error is returned. The fix, as Alexandre pointed out, is to enable AUTO_REPREPARE in your ONCONFIG file and bounce the server. This will cause the engine to automatically reprepare any query plan that has been invalidated by structural changes in the database schema such as the one you are experiencing. With AUTO_REPREPARE enabled the number of -710 that are still returned is very small. > > > The behavior might have changed in later versions. > > TIA > > Ulf > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >