Re: Index Creation Questions

Octav Chiriac wrote: > > Mark D. Stock wrote: > >Schultheis, Carol L wrote: > >> > >> Can someone answer some basic general questions? > >> > >> 1. what is the difference between creating a unique index on a column > >> via the create unique index statement > >> versus using the primary key constraint when creating the table. > > > >The second one creates a unique index AND allows you to use referential > >integrity. > > > > Sorry, IMHO the UNIQUE INDEX _allows_ using of foreign keys on it. > The difference is that PRIMARY KEYs don't allow the columns with > NULL's. May be I'm wrong. Yes, that's right. Informix extends the SQL standard to allow a foreign key to reference any set of columns with a unique constraint on them. The standard allows references to the primary key only. In practice references to the primary key almost always do the job and are to be preferred as they are standard. If you need a non-primary key reference then your schema is not properly normalised. There is another difference: you may need to have two unique constraints. A typical example is a table with a SERIAL column which is the primary key used for joining and another long text identifier for humans to use, which is also unique but not the primary key. > > >> 2. what is the difference between creating an index on a column via the > >> create index statement > >> versus using the alter table and adding a foreign key constraint.> > > >The second one creates an index AND implements automatic referential > >integrity. > > > >> 3. what does the dbexport/dbimport facility do when indices are created > >> by using constraints versus > >> the create index statement, specifically when the dbimport creates > >> the table, is the data then loaded > >> before the constraints create the index??????? > > > >All constraints are created using ALTER TABLE statements AFTER the data > >is loaded into the table. > > > >Hope that helps, > >-- > >Mark. > > > >+----------------------------------------------------------+-----------+ > >|Mark D. Stock - Informix SA http://www.informix.com |//////// /| > >|mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| > >|http://www.iiug.org +-----------------------------------+//// / ///| > >| Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| > >| Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| > >|Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| > >+----------------------+-----------------------------------+-----------+ > > -- > Octav Chiriac Phone: (373) 2 21 20 96 > NetInfo S.R.L. Fax: (373) 2 21 20 96 > Chisinau (373) 2 24 00 83 > Moldova, Republic of mailto:com@netinfo.moldova.net -- Peter Lancashire Information Systems Specialist, Bayer plc Eastern Way, Bury St Edmunds, Suffolk, IP32 7AH, UK Tel: +44-1635-562258, Fax: +44-1635-562281 --- If all else fails, read the instructions. All opinions are my own and not those of Bayer plc. My Internet plumbing does not allow me to mail and post news together. Sorry. --- Join Infuse, the UK Informix User Group at http://www.infuse.org.uk/