Thread: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
From
Ferindo Middleton Jr
Date:
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
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 ...
regards, tom lane
You're right, Tom. I'm sure someone has a use for a serial field that isn't unique. I just assumed that it was. I guess I didn't read the documentation closely enough. At any rate, I had a table using a serial field that I had to restore to a previous date when I noticed that I forgot to set the sequence to the most recent value... user continued adding data to this table and it started causing some problems. It just seems like most situations would want it unique... to ensure integrity. But I guess you need to choose constraint for built-in data types that follow more of a one-size-fits-all philosophy. And hey, how hard can it be to add the word UNIQUE when I'm creating tables? Ferindo 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 ... > > regards, tom lane > >
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... :)
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... :)
Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
From
"codeWarrior"
Date:
"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;
Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
From
"codeWarrior"
Date:
"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;
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? -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim C. Nasby wrote: >>>>Is there some reason why the SERIAL data type doesn't automatically have >>>>a UNIQUE CONSTRAINT. >>> >>>It used to, and then we decoupled it. [snip] > Arguably it would have been better to make the default case add either > UNIQUE or PRIMARY KEY with a way to over-ride. Arguably SERIAL shouldn't be a type at all since it's nothing to do with defining a set of values. If you were being clean about it you'd have to have something like "mycol INTEGER SERIAL UNIQUE", then wire SERIAL to a generator function for the type in question. > 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? Don't forget the NOT NULL too. Perhaps simpler to have a PGIDENT pseudo-type that implies "UNIQUE NOT NULL" and then explain the difference in the docs. -- Richard Huxton Archonet Ltd
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
Richard Huxton wrote: > Jim C. Nasby wrote: >>>>> Is there some reason why the SERIAL data type doesn't >>>>> automatically have a UNIQUE CONSTRAINT. >>>> >>>> It used to, and then we decoupled it. > [snip] >> Arguably it would have been better to make the default case add either >> UNIQUE or PRIMARY KEY with a way to over-ride. > > Arguably SERIAL shouldn't be a type at all since it's nothing to do > with defining a set of values. If you were being clean about it you'd > have to have something like "mycol INTEGER SERIAL UNIQUE", then wire > SERIAL to a generator function for the type in question. > >> 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? > > Don't forget the NOT NULL too. Perhaps simpler to have a PGIDENT > pseudo-type that implies "UNIQUE NOT NULL" and then explain the > difference in the docs. > > -- > Richard Huxton > Archonet Ltd > I like Richard's idea. That seems to be the best way to go. Ferindo Sleekcollar
On Tue, Oct 04, 2005 at 07:50:28PM -0400, Ferindo Middleton Jr wrote: > 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 I don't think either assertion is true. I'd bet most of the developers actually do normally use an index on a serial, since it's normally used as a PK. And while people can be a bit terse with their replies, I wouldn't say you were blasted. :) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461