> I would create a multi-column unique index on the table. This should solve
> the problem mentioned although you may still have an integrity issue if a
> "book" name is mistyped.
Hm?
This sounds promising, except it's the exact opposite of what I need.
Is this what you meant?
CREATE TABLE lineitems (code int, name varchar(12), UNIQUE (code, name));
Because that breaks the whole idea where I should be able to have many
lines with the same item:
insert into lineitems(code, name) VALUES (123, 'bob');
INSERT 35489 1
insert into lineitems(code, name) VALUES (123, 'bob');
ERROR: duplicate key violates unique constraint "lineitems_code_key"
What I want is for that situation, above, to NOT make an error.
But this, below, should:
insert into lineitems(code, name) VALUES (123, 'bob');
insert into lineitems(code, name) VALUES (123, 'xxx');