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,
--
View this message in context: http://www.nabble.com/Better-alternative-for-Primary-Key-then-serial---tp14289409p14289409.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
