Re: Remove implicit unique index creation on SERIAL columns?

Joe Conway <mail(at)joeconway(dot)com> writes:
> I agree 100%. If you want an index, unique constraint, or primary key on
> a SERIAL, I think you should explicitly add it. SERIAL should give me a
> column that automatically increments -- no more, no less.

Hmm, do you also want to eliminate the implicit NOT NULL constraint?

I think that efficiency and orthogonality are adequate reasons for
dissociating UNIQUE from SERIAL. The efficiency argument is pretty
weak in the case of the NOT NULL part, though, so maybe backwards
compatibility should win out there.

Another line of thought: as near as I can tell, SQL92 allows defaults
and CHECK constraints to be associated with domains. Taking the
viewpoint that SERIAL is a domain would say that the DEFAULT nextval()
is okay, and CHECK NOT NULL is okay, but UNIQUE is not okay to be
implied by the domain type. Perhaps the SQL authors knew what they
were doing when they made that restriction...

(Note that although I just dissuaded Rod from actually turning SERIAL
into a domain on compatibility grounds, I don't see any reason why
we shouldn't use the spec's rules about domains to reason about how
it should work. In a slightly longer timeframe we may decide that
we do want to make it a domain.)

regards, tom lane