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 (AT) gmail (DOT) com> wrote:

Hello.
You might also check if yours AUTO_REPREPARE onconfig var is turned on,
since it reduces lots of 710 errors that previously versions generated.

Regards.

Em 02/08/2011 03:39, Keith Simmons escreveu:

--

Alexandre Marini

Tecnologia da Informação - DBA

msn: alexandre_marini (AT) hotmail (DOT) com

SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix

<Cert-Info-Mgmt_color.jpg>

IBM Certified System Administrator - Informix Dynamic Server V10 / V11 /
V11.70

IBM Information Management Informix Technical Professional v3

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 (AT) gmail (DOT) com> wrote:

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.


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.