Search Postgresql Archives

Re: brain-teaser with CONSTRAINT - any SQL experts?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



how about using 2 tables with according unique/primary key constraints and a 
view to actually access the data (mixing the 2 tables into one) ?


On Saturday 08 October 2005 22:36, Miles Keaton wrote:
> 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

-- 
	UC

--
Open Source Solutions 4U, LLC	2570 Fleetwood Drive
Phone:  +1 650 872 2425		San Bruno, CA 94066
Cell:   +1 650 302 2405		United States
Fax:    +1 650 872 2417

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux