Drop & Recreate table problems

Hi folks, In a particular SP, I drop a table, and then call another procedure which creates the same table. However, I always get the exception -625 "Constraint name already exists". Why is this? I thought that everytime a table is dropped, all the constraints associated with it are also dropped. INFORMIX-OnLine Version 7.20.UC2 George Mathew Senior Software Engineer I.T.Solutions

george wrote: > > Hi folks, > > In a particular SP, I drop a table, and then call another procedure which > creates the same table. However, I always get the exception -625 "Constraint > name already exists". Why is this? I thought that everytime a table is > dropped, all the constraints associated with it are also dropped. > > INFORMIX-OnLine Version 7.20.UC2 > > George Mathew > Senior Software Engineer > I.T.Solutions First of all, each CONSTAINT will get a name. The problem is, that if You don't name a CONSTRAINT in Your CREATE- Statement, Informix creates a name using the ID of the table and the ID of the constraint. So it can happen, that this name is already used. So You better give each CONSTRAINT inside Your CREATE-, ALTER- etc. statements explicit CONTRAINT-names. E.g. CREATE TABLE xxx ( field1 INTEGER, PRIMARY KEY (field1) CONSTRAINT ct_xxx1 ) Note, that a "NOT NULL" is a constraint also. Wolfgang

FWIW: This situation usually occurs when you have used dbschema -ss to generate a schema for a table which was subsequently dropped and recreated using that schema. Dbschema explicitely outputs the generated dummy constraint names, which are based on the tabid, for the table and on recreation the table will get another tabid so that the constraint names no longer coincide. Other tables created later, or after a database recreation, or duplicating the table on another system, the constraint names can clash with other generated constraints for some other table that coincidentally has the same tabid the new tables used to have on the other server. This is why myschema.ec generates its own constraint names NOT based on tabid but on tabname when there is not an explicit contraint name. Art S. Kagel Wolfgang Zager wrote: > > george wrote: > > > > Hi folks, > > > > In a particular SP, I drop a table, and then call another procedure which > > creates the same table. However, I always get the exception -625 "Constraint > > name already exists". Why is this? I thought that everytime a table is > > dropped, all the constraints associated with it are also dropped. > > > > INFORMIX-OnLine Version 7.20.UC2 > > > > George Mathew > > Senior Software Engineer > > I.T.Solutions > > First of all, each CONSTAINT will get a name. > The problem is, that if You don't name a CONSTRAINT in Your CREATE- > Statement, Informix creates a name using the ID of the table and the ID > of the constraint. So it can happen, that this name is already used. > > So You better give each CONSTRAINT inside Your CREATE-, ALTER- etc. > statements explicit CONTRAINT-names. E.g. > > CREATE TABLE xxx > ( > field1 INTEGER,> > > PRIMARY KEY (field1) CONSTRAINT ct_xxx1 > ) > > Note, that a "NOT NULL" is a constraint also. > > Wolfgang