Re: question for serial types with CHECK conditions - Mailing list pgsql-hackers

From Michael Glaesemann
Subject Re: question for serial types with CHECK conditions
Date
Msg-id 5D828B62-30AA-4C60-92B4-E83E205E0286@seespotcode.net
Whole thread Raw
In response to question for serial types with CHECK conditions  ("Guido Barosio" <gbarosio@gmail.com>)
List pgsql-hackers
On Apr 28, 2007, at 10:30 , Guido Barosio wrote:

>   Now, my question is: Shouldn't postgresql avoid the creation of the
> table while a serial type contains a check condition?

My question to you is why should it? "a" SERIAL is a shorthand for  
creating an INTEGER column "a",  a sequence ("a_seq") with a  
dependency, and DEFAULT nextval(a_seq). There may be a valid reason  
someone wants to put additional constraints on the column, and I'm  
not sure why the server should second guess the DBA in this case. If  
the CHECK constraint isn't what you want, then don't include it: and  
in this case the server helpfully gave you an error which let you  
know that the CHECK constraint was not doing what you expected.

Also, the server doesn't have the smarts to look into the CHECK  
constraint and decide if it makes sense in your case. For example,  
perhaps you want to have CHECK (a > 0), which won't really do  
anything for a default sequence. However, if the sequence is changed,  
it may return negative integers, which you may not want, so in some  
cases, CHECK (a > 0) may be a valid constraint *in your case*.

The crux of the issue is that there may be valid reasons to have a  
CHECK constraint on a INTEGER (SERIAL) column, and the server is not  
(and will probably never be) smart enough to know your particular  
business rules without you telling it specifically.

Does this help clarify the situation?

Michael Glaesemann
grzm seespotcode net




pgsql-hackers by date:

Previous
From: "Guido Barosio"
Date:
Subject: Re: question for serial types with CHECK conditions
Next
From: shieldy
Date:
Subject: I have made the first step on postgresql, but got some problems