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?