constraint errors

I am running IDS 7.24.UC7 on Solaris 5.6 I am copying the production database to a development server via dbexport/dbimport. I have started to notice the following error when importing: > *** execute sqlobj > 625 - Constraint name (n141_110) already exists. This problem does not happen every time I attempt to import the database, although it has been happening more often. I have altered the table from 'not null' to null, and back to 'not null'. When run dbschema on the altered table, the constraint text is gone. > Before alter: > number varchar(24) not null constraint "informix".n141_110, > After alter: > number varchar(24) not null , I know that Informix uses indexes to enforce constraints (PK, FK), but the sysconstraints info on this guy does not have an index entry. Any ideas as to why this is, and more importantly how to fix it so it stops happening? SC

We've experienced this problem frequently when migrating databases between production and test/development. As a (perhaps less than perfect solution) the dba wrote a little script to sed the sql file created by dbexport and take out all the constraint names. This does not stop the dbimport from working. Salut, Andrew. sfcawley <sfcawley@interaccess.com> wrote in message news:7kbe30$ca1$1@news.xmission.com... > > I am running IDS 7.24.UC7 on Solaris 5.6 > > I am copying the production database to a development server via > dbexport/dbimport. I have started to notice the following error when > importing: > > > *** execute sqlobj > > 625 - Constraint name (n141_110) already exists. > > This problem does not happen every time I attempt to import the database, > although it has been happening more often. > > I have altered the table from 'not null' to null, and back to 'not null'. > When run dbschema on the altered table, the constraint text is gone. > > > Before alter: > > number varchar(24) not null constraint "informix".n141_110, > > > After alter: > > number varchar(24) not null , > > I know that Informix uses indexes to enforce constraints (PK, FK), but the > sysconstraints info on this guy does not have an index entry. > > Any ideas as to why this is, and more importantly how to fix it so it stops > happening? > > SC > -- Andrew Pearson - un animal avec beaucoup de fonctions interactives. Parlez et riez ensemble. Il connait 800 mots et bruits. Réagit à la lumière et au bruit. Ses movemements sont très réalistes! Version anglais.

In article <7kbe30$ca1$1@news.xmission.com>, sfcawley <sfcawley@interaccess.com> writes > >I am running IDS 7.24.UC7 on Solaris 5.6 > >I am copying the production database to a development server via >dbexport/dbimport. I have started to notice the following error when >importing: > >> *** execute sqlobj >> 625 - Constraint name (n141_110) already exists. > Sounds like either a bug in dbexport or database corruption.. Log a support call with Informix.. >This problem does not happen every time I attempt to import the database, >although it has been happening more often. > >I have altered the table from 'not null' to null, and back to 'not null'. >When run dbschema on the altered table, the constraint text is gone. > >> Before alter: >> number varchar(24) not null constraint "informix".n141_110, > >> After alter: >> number varchar(24) not null , > >I know that Informix uses indexes to enforce constraints (PK, FK), but the >sysconstraints info on this guy does not have an index entry. > >Any ideas as to why this is, and more importantly how to fix it so it stops >happening? > >SC > -- David Williams

Informix generates names for unnamed constraints by using the table's tabid. However, since you are migrating the tables what was tabid 114 is now another tabid and that table's constraint already has the name. The problem happens because dbschema, and dbexport, write out these generated constraint names to the schema file as if they had been entered by hand when you created the table. You can: o edit the schema and remove all those explicit generated names altogether and let new names be generated. o edit the schema and replace the generated names with sensible names based on the table's name rather than its number. o use my myschema.ec utility to generate a replacement schema, it uses a different naming scheme so it eliminates such clashes (just one reason I have maintained the thing all these years). Myschema has an option to generate a dbload compatible script, it is in the package utils2_ak in the IIUG Software Repository. Art S. Kagel sfcawley wrote: > > I am running IDS 7.24.UC7 on Solaris 5.6 > > I am copying the production database to a development server via > dbexport/dbimport. I have started to notice the following error when > importing: > > > *** execute sqlobj > > 625 - Constraint name (n141_110) already exists. > > This problem does not happen every time I attempt to import the database, > although it has been happening more often. > > I have altered the table from 'not null' to null, and back to 'not null'. > When run dbschema on the altered table, the constraint text is gone. > > > Before alter: > > number varchar(24) not null constraint "informix".n141_110, > > > After alter: > > number varchar(24) not null , > > I know that Informix uses indexes to enforce constraints (PK, FK), but the > sysconstraints info on this guy does not have an index entry. > > Any ideas as to why this is, and more importantly how to fix it so it stops > happening? > > SC

Hi, last week we faced the same problem (on both NT4/SP3 w/ IDS 7.23.TC9 and Solaris 2.6 w/ 7.24.UC3). I was told by Informix support that this is a bug in dbexport and the only workaround is to remove the constraint parts from the sql file (I did it in vi with ":1,$s/not null constraint.*/not null,/g"). This bug is fixed in 7.24+ on NT; I'm not sure about Solaris because we'll go for 7.31 and there it's fixed. For versions 7.30- contact Informix support. Just my $ 0.02 worth... :-) Regards, Stephan. In article <7kbe30$ca1$1@news.xmission.com>, sfcawley <sfcawley@interaccess.com> wrote: > > I am running IDS 7.24.UC7 on Solaris 5.6 > > I am copying the production database to a development server via > dbexport/dbimport. I have started to notice the following error when > importing: > > > *** execute sqlobj > > 625 - Constraint name (n141_110) already exists. > > This problem does not happen every time I attempt to import the database, > although it has been happening more often. > > I have altered the table from 'not null' to null, and back to 'not null'. > When run dbschema on the altered table, the constraint text is gone. > > > Before alter: > > number varchar(24) not null constraint "informix".n141_110, > > > After alter: > > number varchar(24) not null , > > I know that Informix uses indexes to enforce constraints (PK, FK), but the > sysconstraints info on this guy does not have an index entry. > > Any ideas as to why this is, and more importantly how to fix it so it stops > happening? > > SC > > -- Stephan Stresing MR Informatik GmbH mailto:st@mr-informatik.de Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.