Re: Error in creating table. (Newbie)

Tony Tyrwhitt-Drake (tonytd@ttyrwhit.demon.co.uk) wrote: : In article <3ubte4$br@news.unicomp.net>, smeacham@conline.com (Steven D. Meacham) says: : > : >I am trying to build my first schema on Informix after just having : >battled installation. We're using Informix On-Line 5.x on SCO Unix. : > : >Use the Informix utility to load my .sql file containing definitions : >for the new database schema. The following table is the first in my : >sql file. No table or key exists that needs to be dropped at this : >point. : > : >It returns an error that I'm creating a duplicate key. : <snipped> : When you specify -unique- against a field in your schema, Informix : very kindly creates an unique index for you. Thus if / when you : later try to create a unique index on the same field you get the : duplicate key message. : The way around this, is either do not use the -unique- against : the field, but create your own index, or bung in the -unique- : and enjoy the automatically created index 8-> Quoting from the Reference Manual (page 7-71): Since constraints are enforced through indexes, you cannot create an index (using the CREATE INDEX statement) for a column that is of the same type as the constraint place on that column. For example, if there is a unique constraint on a column, you can create neither a unique index for that column nor a duplicate ascending index. As the previous writer said, you can either create your own index or assign the UNIQUE constraint to the field. The advantage to the latter is that you can implement data integrity constraints and turn effective checking off and on (i.e. SET CONSTRAINTS). However, I prefer the former because then you can drop and recreate the index whenever you want. You lose control of the index if Informix internally defines it. ------------ Curtis Kling NEC America, Irving TX kling@esd.dl.nec.com