Re: how to create an index on serial column

CREATE TABLE repetitious(col01 SERIAL); INSERT INTO repetitious VALUES(NULL); -- Repeat N times INSERT INTO repetitious VALUES(1); -- Repeat M times INSERT INTO repetitious VALUES(0); INSERT INTO repetitious VALUES(0); INSERT INTO repetitious VALUES(0); SELECT col01, COUNT(*) FROM repetitious GROUP BY col01; I have N rows with nulls, M rows with the value 1, and one row with each of the values 2, 3, and 4. Try it! The schema editor (Table option in the ISQl/DB-Access ring menus) does automatically create a unique index on a serial column, and automatically enforces the NOT NULL constraint. As demonstrated by the sample code above, neither the NOT NULL constraint nor the unique index is created when you execute plain SQL statements. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> >From: Sally Woolrich <Sally@excelsis.demon.co.uk> >Date: Mon, 28 Aug 95 11:01:42 GMT >X-Informix-List-Id: <news.16592> > >In article <41l8co$rtu@eccdb1.pms.ford.com> > mreed@ese721 "Michael Reed - Radiix" writes: > >> Steve Morrow (smorrow@dotrisc.cfr.usf.edu) wrote: >> : A unique index is automatically built on a serial column. >> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ >> This is not true. >> A serial column doesn't guarantee uniqueness. An index is not auto- >> matically built. >> >> The only way to guarantee uniqueness is to build a unique index >> on that column. I can't answer why alter table is giving him trouble, though. > >I think that it *is* true that each value in a serial column is unique, >but it is *not* true that there is an index unless you explicitly >build one - certainly if using 'CREATE TABLE' statements to create >a table (there is another way of creating them hidden *somewhere* >in the 'dbaccess/'isql' ring menu structures).