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

Jim C. Nasby wrote:
> On Tue, Sep 27, 2005 at 10:33:14AM -0500, Scott Marlowe wrote:
>   
>> On Mon, 2005-09-26 at 20:03, Tom Lane wrote:
>>     
>>> Ferindo Middleton Jr <fmiddleton@verizon.net> writes:
>>>       
>>>> Is there some reason why the SERIAL data type doesn't automatically have 
>>>> a UNIQUE CONSTRAINT.
>>>>         
>>> It used to, and then we decoupled it.  I don't think "I have no use for
>>> one without the other" translates to an argument that no one has a use
>>> for it ...
>>>       
>> I have to admit, right after the change was made, I was of the opinion
>> that no one would ever need that.  Then, a few months later, it was
>> exactly what I needed for some project...  :)
>>     
>
> Arguably it would have been better to make the default case add either
> UNIQUE or PRIMARY KEY with a way to over-ride.
>
> If newbies are getting burned maybe it would be useful to toss a NOTICE
> or maybe even WARNING when a serial is created without a unique
> constraint of some kind?
>   
Based on the feedback I received after I made that original post, it 
seemed most people don't use SERIAL with a unique constraint or primary 
key and  I was blasted for making such  a suggestion. I'm sorry... It 
only seemed logical to me to do so and I thought other's would think the 
same. After giving it further thought to it and thinking about the 
broader scope that the developers would need to employ to the overall 
body of people using this database, it does now make more sense to me to 
just not include it at all and leave it to the admin to deploy it using 
what ever schema he/she sees fit...

I don't think a NOTICE or a WARNING is necessary. People can read 
documentation. You should probably just stress more so that they 
actually read the docs rather than putting warnings and the like in place.

When I first wrote the article I was a little falsely alarmed because I 
had thought that I didn't read the documentation and deployed a bunch of 
table using a serial without constraining them to some kind of UNIQUE 
property... but I later realized it was just this one table that I 
didn't do it with and had accidentally duplicated the fields integer 
value during manual INSERTS/RESTORES/BACKUPS etc. and the like to my db.

Ferindo