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

From Adam Lawrence
Subject Re: brain-teaser with CONSTRAINT - any SQL experts?
Date
Msg-id 004f01c5cd51$741cb290$3701a8c0@Adam
Whole thread Raw
In response to brain-teaser with CONSTRAINT - any SQL experts?  (Miles Keaton <mileskeaton@gmail.com>)
List pgsql-general
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
>



pgsql-general by date:

Previous
From: Rick Morris
Date:
Subject: Re: Oracle buys Innobase
Next
From: Miles Keaton
Date:
Subject: Re: brain-teaser with CONSTRAINT - any SQL experts?