Re: [SQL] Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
Ferindo Middleton Jr [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
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;
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[SQL] Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
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
Re: [SQL] Why doesn't the SERIAL data type automatically have a UNIQUE CONSTRAINT
Ferindo Middleton Jr [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Why doesn't the SERIAL data type automatically have a UNIQUE
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 [EMAIL PROTECTED] 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 ---(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
