"Ferindo Middleton Jr" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Is there some reason why the SERIAL data type doesn't automatically have a > UNIQUE CONSTRAINT. It seems that the main reason for using it is so that > the value for this field keeps changing automatically and is never null so > any one record can be identified using it- So why not imply that it is > always be UNIQUE anyway. I mean, if you were to force another value on a > SERIAL field that already had that same value, the would through the > sequence tracking the the fields current value off any way, so it just > makes sense to me to not let a serial field be duplicated. Let's take a > poll. Is there anyone out there who actually uses the SERIAL data type who > would not want it to be UNIQUE? > > Ferindo > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
You are correct... serials don't have unique constraints unless they are also defined as a primary key... It seems to me that all you need to do is make your serial value a primary key in your DDL... (which is the same as defining a unique constraint...) consider the following: CREATE TABLE sys_test ( id serial NOT NULL PRIMARY KEY, txt text not null ) WITH OIDS; INSERT INTO sys_test(txt) VALUES ('A'); INSERT INTO sys_test(txt) VALUES ('B'); -- INSERT statement #3 throws an expected error.... INSERT INTO sys_test(id, txt) VALUES (1, 'C'); // THROWS UNIQUE CONTRAINT ERROR AS EXPECTED !!! SELECT * FROM sys_test; ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster