Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT

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