brain-teaser with CONSTRAINT - any SQL experts?

From: Miles Keaton <mileskeaton(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: brain-teaser with CONSTRAINT - any SQL experts?
Date: 2005-10-09 05:36:33
Message-ID: 59b2d39b0510082236h3ebbd74fm1c3ff1ecd9991746@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm stuck on a brain-teaser with CONSTRAINT:

Imagine a table like "lineitems" in a bookstore - where you don't need
an ISBN to be unique because a book will be in buying history more
than once.

But you DO need to make sure that the ISBN number is ONLY matched to
one book name - NOT to more than one book name.

This is OK:
isbn name
1234 Red Roses
1234 Red Roses

This is OK: (two books can have the same name)
isbn name
1234 Red Roses
5555 Red Roses

This is NOT OK: (an isbn must be tied to one book only!)
isbn name
1234 Red Roses
1234 Green Glasses

I know it's tempting to say, "just link a separate table for the book
and don't store the book name" but let's just pretend that's not an
option - because I'm not actually dealing with books : I just made up
this simplified version of something at work, where we can't change
the table : both isbn and name MUST be in the table, and what I'm
trying to do is put a CONSTRAINT on the table definition to protect
against user error, by making sure that any entered isbn is only tied
to one book-name in that table.

Thoughts?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Uwe C. Schroeder 2005-10-09 05:50:10 Re: Oracle buys Innobase
Previous Message Tom Lane 2005-10-09 05:30:34 Re: Oracle buys Innobase