Hey
Could you write specific functions "insert"/"update" that people use when
they update the data in the db, that checks for the constraints you are
talking about. So the functions would take in the input data and then would
scan the table to make sure there is no "book" with the same "isbn" that
also has different name, if it did your function could raise an error
message.
I realise you said you actually wanted to put the constraint on the table,
but I just thought I would suggest this anyway.
Cheers
Adam
Adam Lawrence
Mediasculpt
Direct Line: +64 6 3546038
Email: adam@mediasculpt.com
----- Original Message -----
From: "Miles Keaton" <mileskeaton@gmail.com>
To: <pgsql-general@postgresql.org>
Sent: Monday, October 10, 2005 4:25 PM
Subject: Re: [GENERAL] brain-teaser with CONSTRAINT - any SQL experts?
> > 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');
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>