import fails with 625 - Constraint name (n255_460)

IBM Informix Dynamic Server Version 11.50.FC6 HP-UX wuast130 B.11.23 U 9000/800 I am trying to do an import but it keeps failing with error below. Is there a way round this. I have changed constraint names 3 times now in import script but problem contines there are many system named constraints in this import file. create table "paqis".business_rule ( business_rule_id integer not null , description varchar(254) not null , primary key (business_rule_id) constraint "paqis".xpkbusiness_rule ) with crcols extent size 16 next size 16 lock mode row; *** execute sqlobj 625 - Constraint name (n255_460) already exists.

Somewhere in the schema file in the .exp directory is a NOT NULL constraint with an explicit name of N255_460. You have to remove the explicit constraint name. Best to convert all of the new style named NOT NULL constraints to the older unnamed NOT NULL clauses. This one quirk is one of the main reasons that I have maintained myschema all these years. You could replace the schema file with one generated by myschema -l. It will eliminate the problem for you. The problem is caused by previously implicitly named NOT NULL constraints mixing with explicitly named constraints. The generated names for unnamed constraints contain the tabid and constrid. If a table has been recreated by a non-in-place alter, its tabid will have changed and so it will be exported in a different order than it was originally created. That means that on import, some other table will get its tabid and sometimes it's constrid's as well causing a naming clash when the original tabid <N> tries to name its constraints explicitly. 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 Thu, Jun 14, 2012 at 10:06 PM, KARL OLIVER <karl.oliver@maf.govt.nz>wrote: > IBM Informix Dynamic Server Version 11.50.FC6 > > HP-UX wuast130 B.11.23 U 9000/800 > I am trying to do an import but it keeps failing with error below. > Is there a way round this. I have changed constraint names 3 times now in > import script but problem contines there are many system named constraints > in > this import file. > > create table "paqis".business_rule > ( > > business_rule_id integer not null , > > description varchar(254) not null , > > primary key (business_rule_id) constraint "paqis".xpkbusiness_rule > ) with crcols extent size 16 next size 16 lock mode row; > *** execute sqlobj > 625 - Constraint name (n255_460) already exists. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93406bb2560e804c279cda1

found this script on an IBM site that does what I want cat $1 > schema1.txt sed 's/null constraint ".*".n[0-9]*_[0-9]*/null /g' schema1.txt \\\\ > nonulls.sql rm schema1.txt