"Ferindo Middleton Jr" <fmiddleton@verizon.net> wrote in message
news:4338961E.20100@verizon.net...
> 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;