From: "codeWarrior" <gpatnude@hotmail.com>
Subject: Re: Why doesn't the SERIAL data type automatically have a UNIQUE
	CONSTRAINT
Date: Wed, 28 Sep 2005 09:24:24 -0700
Organization: Hub.Org Networking Services
Lines: 53
Message-ID: <dheg0c$16ka$1@news.hub.org>
References: <4338961E.20100@verizon.net>
To: pgsql-sql@postgresql.org


"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;