Fw: dbimport reports constraint that already exists and exits...

Hi Listers, I get this error during a dbimport of a database that was previously exported: *** execute sqlobj 625 - Constraint name (n218_4834) already exists. An example of the field's dbschema was defined as follows: coentfincble decimal(5,0) not null constraint "owner".n218_4834, The solution was to modify the dbschema file as follows: coentfincble decimal(5,0) not null, I removed all references to constraints, except from the primary keys. My question is how are not null constraints defined? Are they created automatically by Informix? What is the impact if any of removing the references? TIA, Denmark W.

On Tue, 21 Nov 2000 16:23:42 -0600, "Denmark B. Weatherburn" <dweatherb@btl.net> wrote: > >Hi Listers, > > >I get this error during a dbimport of a database that was previously >exported: > >*** execute sqlobj >625 - Constraint name (n218_4834) already exists. > >An example of the field's dbschema was defined as follows: >coentfincble decimal(5,0) not null constraint "owner".n218_4834, > >The solution was to modify the dbschema file as follows: >coentfincble decimal(5,0) not null, > >I removed all references to constraints, except from the primary keys. My >question is how are not null constraints defined? Are they created >automatically by Informix? Yes, when you specify NOT NULL then informix create CONSTRAINT for it. Format is n = NOT NULL 218 = tabid 4834 = ? some number From x.y version (I don't remember) if tabid in constraint name is equal real tabid in systables (=automacaly created) dbschema (dbexport) NOT print constraint. Then during create NOT NULL, constraint name will be automaticaly created. Solution : use this new version or put to constraint own name. >What is the impact if any of removing the references? > > >TIA, > >Denmark W. Warning: my english is poor! -- Jiri Lisicky CD KMZP Olomouc e-mail: lisicky@datis.cdrail.cz Videnska 15 phone: +420-068-472-2272 Olomouc, Czech Republic >>> cestina ISO-8859-2 Compatible <<<

Until you can get the version of IDS that is immune to this problem, run the following sed script through your <database>.sql file before dbimport-ing. s/not\\ null\\ constraint.*,/not\\ null,/ s/not\\ null\\ constraint.*/not\\ null/ ... which removes the constraint names. This did the trick for us. HTH Brett Randall "Denmark B. Weatherburn" wrote: > > Hi Listers, > > I get this error during a dbimport of a database that was previously > exported: > > *** execute sqlobj > 625 - Constraint name (n218_4834) already exists. > > An example of the field's dbschema was defined as follows: > coentfincble decimal(5,0) not null constraint "owner".n218_4834, > > The solution was to modify the dbschema file as follows: > coentfincble decimal(5,0) not null, > > I removed all references to constraints, except from the primary keys. My > question is how are not null constraints defined? Are they created > automatically by Informix? > > What is the impact if any of removing the references? > > TIA, > > Denmark W.

"Denmark B. Weatherburn" wrote: > > Hi Listers, > > I get this error during a dbimport of a database that was previously > exported: > > *** execute sqlobj > 625 - Constraint name (n218_4834) already exists. > > An example of the field's dbschema was defined as follows: > coentfincble decimal(5,0) not null constraint "owner".n218_4834, > > The solution was to modify the dbschema file as follows: > coentfincble decimal(5,0) not null, > > I removed all references to constraints, except from the primary keys. My > question is how are not null constraints defined? Are they created > automatically by Informix? Yes the NOT NULL constraints are created automatically by Informix when you include the NOT NULL attribute with a name that represents the tabid and a sequential constraint number preceded by a letter indicating the type of constraint. Unfortunately dbexport (and dbschema for that matter) output these default constraint names for NOT NULL constraints but during an import these names MAY conflict with the autogenerated names of other constraints on another table which accidentally now has the same tabid as the offending table did on the exporting server. The solutions are several: o Always NAME you NOT NULL constraints yourself (this is tedious) o Use awk or sed to remove the constraint names, or the entire constraint declaration leaving just the NOT NULL attribute, from the schema file before dbimporting o Use my dbschema replacement utility, myschema, with its dbexport compatibility flag (-l) to generate a new shema file for dbimport to use. Myschema will do serveral things to prevent all of the naming clashes that dbexport/dbimport can experience by not generating constraints for NOT NULL columns, explicitely creating indexes for all constraints that need them, No outputting explicitely any constraint name that was autogenerated by the engine, optionally removing ownership clauses from all objects so that the resulting database can be owned by the user running the dbimport. Myschema is part of the package utils2_ak found in the IIUG Software Repository. > What is the impact if any of removing the references? None, they will be autogenerated again at create time. Art S. Kagel > TIA, > > Denmark W.