brain-teaser with CONSTRAINT - any SQL experts?

Поиск
Список
Период
Сортировка
От Miles Keaton
Тема brain-teaser with CONSTRAINT - any SQL experts?
Дата
Msg-id 59b2d39b0510082236h3ebbd74fm1c3ff1ecd9991746@mail.gmail.com
обсуждение исходный текст
Ответы Re: brain-teaser with CONSTRAINT - any SQL experts?  ("Uwe C. Schroeder" <uwe@oss4u.com>)
Re: brain-teaser with CONSTRAINT - any SQL experts?  (Oliver Elphick <olly@lfix.co.uk>)
Re: brain-teaser with CONSTRAINT - any SQL experts?  (Keary Suska <hierophant@pcisys.net>)
Re: brain-teaser with CONSTRAINT - any SQL experts?  (Miles Keaton <mileskeaton@gmail.com>)
Список 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?

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Oracle buys Innobase
Следующее
От: "Uwe C. Schroeder"
Дата:
Сообщение: Re: Oracle buys Innobase