Re: Drop & Recreate table problems

Also FWIW - never let Informix generate the names for you, create them by hand (OK via scripts) and name them something meaningful to you - it does make life easier when moving DBs a round Paul Watson WF Software Ltd Tel: +44 1436 674729 Fax: +44 1436 678729 www.wfsoftware.com/informix # If you broke it, hide the evidence >>> "Art S. Kagel" <kagel@bloomberg.net> 09/30/99 06:30PM >>> 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 ********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **********************************************************************