On Sun, 23 Jan 2005, William Yu wrote:
> Ralph van Etten wrote:
> > I agree that a serial would be better.
> >
> > But I think there are situations where a serial isn't convenient
> > Like when you want an primary key which consists of the current
> > year and an sequence number. Like ('05', 1), ('05', 2), ('05', 3) etc.
> > With a sequence you must write extra code to reset the sequence every year
> > and you get into trouble if someone inserts data from the previous year...
> > A 'MAX(id)+1' is much simpler and cleaner then.
>
> My personal experience is trying to get primary keys to "mean" something
> is a pain in the ass. In your example, I'd much rather stick with serial
> as the primary key and store the year/sequence in another "display"
> field. Think about this situation:
Ok, but then I have the same problem. The "display" field has to be
unique and it needs to use a SELECT MAX in an INSERT query and this gives
the same problem. Only not with the primary key but with possible
duplicate values in the "display" field.
CREATE TABLE test (
pk SERIAL,
disp1 CHAR(2),
disp2 INT,
UNIQUE(disp1, disp2),
PRIMARY KEY(pk)
)
and insert with something like:
INSERT INTO test (disp1, disp2)
SELECT 'XX', COALESCE(MAX(disp2)+1, 1)
FROM test WHERE disp1 = 'XX';
This gives the same serialization errors.
Ralph.
