Re: Better alternative for Primary Key then serial??

On Tuesday 11 December 2007 9:42 pm, pilzner wrote:
> Hi - I'm new to PostGres, but have used MSSQL for about a year. I'm going
> through the documentation, but after reading about serials have a lot of
> worries about keeping referential integrity in place and other things.
> Specifically, here are a few scenarios:
>
> a.)
> CREATE TABLE TestTable (
> TestID SERIAL NOT NULL PRIMARY KEY,
> TestData varchar(20) NOT NULL
> );
>
> INSERT INTO TestTable(TestData) VALUES ('Data1');
> INSERT INTO TestTable(TestData) VALUES ('Data2');
> INSERT INTO TestTable(TestData) VALUES ('Data3');
>
> UPDATE TestTable SET TestID = 10 WHERE TestID = 1;
>
>
> Ok, red flag for me right here. The above works just fine. Granted, if
> another table referenced the row w/ TestID = 1, it should violate foreign
> key constraints and error out. However, with the use of serial, this is
> going to run into another problem, down the road right?? Demonstrated here:
>
> b.)
> CREATE TABLE TestTable2 (
> TestID SERIAL NOT NULL PRIMARY KEY,
> TestData varchar(20) NOT NULL
> );
>
> INSERT INTO TestTable2(TestID, TestData) VALUES (1, 'DataData');
>
> INSERT INTO TestTable(TestData) VALUES ('NextData');
> --duplicate key violation occurs
>
> INSERT INTO TestTable(TestData) VALUES ('NextData');
> --Works fine
>
> To phrase what happens, the next number from serial is '1', but that number
> was already explicitly entered. The next call works, because the next
> serial number is '2'. Ideally, the first insert would -never- happen and
> TestID wouldn't ever be explicitly given a value, but if it were, its a
> guaranteed error down the road some ways.
>
> Does stuff like this cause any aches and pains to developers out there, or
> do I just need to get in a new mindset??? Also, is there a way to be sure
> the primary key is *ONLY* ever given a value by serial, and not subject to
> updates???
>
> Thanks,

Each table that has SERIAL column created gets its own sequence, so there will
be no conflict between tables. That case would only arise if you assigned the
same sequence to multiple tables using DEFAULT nextval("some_sequence") and
mixed manual updating of the sequence and auto updating. By default a
sequence will always increment forward so you will have a fresh number for
the next request. This means a sequence can have holes as it increments even
if a transaction fails. You can create a duplicate key violation within a
single table by manualling entering a SERIAL id that was already generated.
This applies to any PRIMARY KEY and is sort of the point. The best thing to
do is let the SERIAL sequence work on it own. If you want to deal with
sequences you should take a look at:
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html

As to preventing updates. You have a couple of choices.
1)Do not let that field be changed by the user. I usually in either hid the
field or prevented data entry on that field.
2) Create an ON UPDATE TRIGGER that does what you want with the field.

--
Adrian Klaver
aklaver(at)comcast(dot)net