pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
10+ messages / 6 participants
[nested] [flat]
* Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
@ 2005-09-27 00:45 Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 01:03 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Tom Lane <tgl@sss.pgh.pa.us>
2005-09-28 16:24 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT codeWarrior <gpatnude@hotmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Ferindo Middleton Jr @ 2005-09-27 00:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
@ 2005-09-27 01:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-27 02:53 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 15:33 ` Re: Why doesn't the SERIAL data type automatically have a Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 2 replies; 10+ messages in thread
From: Tom Lane @ 2005-09-27 01:03 UTC (permalink / raw)
To: fmiddleton@verizon.net; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why doesn't the SERIAL data type automatically have a UNIQUE
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 01:03 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-09-27 02:53 ` Ferindo Middleton Jr <fmiddleton@verizon.net>
1 sibling, 0 replies; 10+ messages in thread
From: Ferindo Middleton Jr @ 2005-09-27 02:53 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why doesn't the SERIAL data type automatically have a
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 01:03 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-09-27 15:33 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-04 22:52 ` Re: Why doesn't the SERIAL data type automatically have a Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 1 reply; 10+ messages in thread
From: Scott Marlowe @ 2005-09-27 15:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: fmiddleton@verizon.net; pgsql-sql
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... :)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why doesn't the SERIAL data type automatically have a
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 01:03 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Tom Lane <tgl@sss.pgh.pa.us>
2005-09-27 15:33 ` Re: Why doesn't the SERIAL data type automatically have a Scott Marlowe <smarlowe@g2switchworks.com>
@ 2005-10-04 22:52 ` Jim C. Nasby <jnasby@pervasive.com>
2005-10-04 23:23 ` Re: Why doesn't the SERIAL data type automatically have a Richard Huxton <dev@archonet.com>
2005-10-04 23:50 ` Re: Why doesn't the SERIAL data type automatically have a Ferindo Middleton Jr <fmiddleton@verizon.net>
0 siblings, 2 replies; 10+ messages in thread
From: Jim C. Nasby @ 2005-10-04 22:52 UTC (permalink / raw)
To: Scott Marlowe <smarlowe@g2switchworks.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; fmiddleton@verizon.net; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why doesn't the SERIAL data type automatically have a
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 01:03 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Tom Lane <tgl@sss.pgh.pa.us>
2005-09-27 15:33 ` Re: Why doesn't the SERIAL data type automatically have a Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-04 22:52 ` Re: Why doesn't the SERIAL data type automatically have a Jim C. Nasby <jnasby@pervasive.com>
@ 2005-10-04 23:23 ` Richard Huxton <dev@archonet.com>
2005-10-04 23:54 ` Re: Why doesn't the SERIAL data type automatically have a Ferindo Middleton Jr <fmiddleton@verizon.net>
1 sibling, 1 reply; 10+ messages in thread
From: Richard Huxton @ 2005-10-04 23:23 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Tom Lane <tgl@sss.pgh.pa.us>; fmiddleton@verizon.net; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why doesn't the SERIAL data type automatically have a
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 01:03 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Tom Lane <tgl@sss.pgh.pa.us>
2005-09-27 15:33 ` Re: Why doesn't the SERIAL data type automatically have a Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-04 22:52 ` Re: Why doesn't the SERIAL data type automatically have a Jim C. Nasby <jnasby@pervasive.com>
2005-10-04 23:23 ` Re: Why doesn't the SERIAL data type automatically have a Richard Huxton <dev@archonet.com>
@ 2005-10-04 23:54 ` Ferindo Middleton Jr <fmiddleton@verizon.net>
0 siblings, 0 replies; 10+ messages in thread
From: Ferindo Middleton Jr @ 2005-10-04 23:54 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; Scott Marlowe <smarlowe@g2switchworks.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why doesn't the SERIAL data type automatically have a
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 01:03 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Tom Lane <tgl@sss.pgh.pa.us>
2005-09-27 15:33 ` Re: Why doesn't the SERIAL data type automatically have a Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-04 22:52 ` Re: Why doesn't the SERIAL data type automatically have a Jim C. Nasby <jnasby@pervasive.com>
@ 2005-10-04 23:50 ` Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-10-05 00:06 ` Re: Why doesn't the SERIAL data type automatically have a Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 1 reply; 10+ messages in thread
From: Ferindo Middleton Jr @ 2005-10-04 23:50 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why doesn't the SERIAL data type automatically have a
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 01:03 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Tom Lane <tgl@sss.pgh.pa.us>
2005-09-27 15:33 ` Re: Why doesn't the SERIAL data type automatically have a Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-04 22:52 ` Re: Why doesn't the SERIAL data type automatically have a Jim C. Nasby <jnasby@pervasive.com>
2005-10-04 23:50 ` Re: Why doesn't the SERIAL data type automatically have a Ferindo Middleton Jr <fmiddleton@verizon.net>
@ 2005-10-05 00:06 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 10+ messages in thread
From: Jim C. Nasby @ 2005-10-05 00:06 UTC (permalink / raw)
To: Ferindo Middleton Jr <fmiddleton@verizon.net>; +Cc: Scott Marlowe <smarlowe@g2switchworks.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
@ 2005-09-28 16:24 ` codeWarrior <gpatnude@hotmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: codeWarrior @ 2005-09-28 16:24 UTC (permalink / raw)
To: pgsql-sql
"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;
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2005-10-05 00:06 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-27 00:45 Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 01:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-09-27 02:53 ` Re: Why doesn't the SERIAL data type automatically have a UNIQUE Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-09-27 15:33 ` Re: Why doesn't the SERIAL data type automatically have a Scott Marlowe <smarlowe@g2switchworks.com>
2005-10-04 22:52 ` Re: Why doesn't the SERIAL data type automatically have a Jim C. Nasby <jnasby@pervasive.com>
2005-10-04 23:23 ` Re: Why doesn't the SERIAL data type automatically have a Richard Huxton <dev@archonet.com>
2005-10-04 23:54 ` Re: Why doesn't the SERIAL data type automatically have a Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-10-04 23:50 ` Re: Why doesn't the SERIAL data type automatically have a Ferindo Middleton Jr <fmiddleton@verizon.net>
2005-10-05 00:06 ` Re: Why doesn't the SERIAL data type automatically have a Jim C. Nasby <jnasby@pervasive.com>
2005-09-28 16:24 ` codeWarrior <gpatnude@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox