Re: brain-teaser with CONSTRAINT - any SQL experts? - Mailing list pgsql-general

> 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');