Unique index and null values

[John]

Any suggestions on how to get this to work:

My table includes a index that requires three combined fields to be
unique. However, sometimes the third field will be null....thereby
excluding the record from the uniqueness requirement.

Details:
tbl_Product_Lic ensor
Product_ID
Licensor_ID
Sublicensor_ID

A product will always have only one licensor, which may or may not
also have a sublicensor. How do I make this three field combo a unique
requirement when one can be null?

TIA...

[Phil Stanton]

Re: Unique index and null values

Why not make it zero??


"John" <soundneedle@ho tmail.com> wrote in message
news:90fab935.0 404170108.28bfd 5d2@posting.goo gle.com...[color=blue]
> Any suggestions on how to get this to work:
>
> My table includes a index that requires three combined fields to be
> unique. However, sometimes the third field will be null....thereby
> excluding the record from the uniqueness requirement.
>
> Details:
> tbl_Product_Lic ensor
> Product_ID
> Licensor_ID
> Sublicensor_ID
>
> A product will always have only one licensor, which may or may not
> also have a sublicensor. How do I make this three field combo a unique
> requirement when one can be null?
>
> TIA...[/color]

[rkc]

Re: Unique index and null values


"John" <soundneedle@ho tmail.com> wrote in message
news:90fab935.0 404170108.28bfd 5d2@posting.goo gle.com...[color=blue]
> Any suggestions on how to get this to work:
>
> My table includes a index that requires three combined fields to be
> unique. However, sometimes the third field will be null....thereby
> excluding the record from the uniqueness requirement.
>
> Details:
> tbl_Product_Lic ensor
> Product_ID
> Licensor_ID
> Sublicensor_ID
>
> A product will always have only one licensor, which may or may not
> also have a sublicensor. How do I make this three field combo a unique
> requirement when one can be null?[/color]

Can't you just set Required to no in the properties sheet?

You won't be able to enforce referential integrity for that field.

[Salad]

Re: Unique index and null values

John wrote:
[color=blue]
> Any suggestions on how to get this to work:
>
> My table includes a index that requires three combined fields to be
> unique. However, sometimes the third field will be null....thereby
> excluding the record from the uniqueness requirement.
>
> Details:
> tbl_Product_Lic ensor
> Product_ID
> Licensor_ID
> Sublicensor_ID
>
> A product will always have only one licensor, which may or may not
> also have a sublicensor. How do I make this three field combo a unique
> requirement when one can be null?
>
> TIA...[/color]

If a product can only have 1 licensor, who cares what the sublicensor id
is except for informational purposes?

Personally, I would have an autonumber field called ProdLicID and make
it a primary key. I would index on Product and Licensor and maybe
SubLicensor fields. You could even make the indexes Unique if that
floats your boat.

The word that comes to mind with your scheme is PITA.

[Mike MacSween]

Re: Unique index and null values

"Salad" <oil@vinegar.co m> wrote in message
news:tUdgc.1479 2$k05.7853@news read2.news.pas. earthlink.net.. .[color=blue]
> John wrote:
>[color=green]
> > Any suggestions on how to get this to work:
> >
> > My table includes a index that requires three combined fields to be
> > unique. However, sometimes the third field will be null....thereby
> > excluding the record from the uniqueness requirement.
> >
> > Details:
> > tbl_Product_Lic ensor
> > Product_ID
> > Licensor_ID
> > Sublicensor_ID
> >
> > A product will always have only one licensor, which may or may not
> > also have a sublicensor. How do I make this three field combo a unique
> > requirement when one can be null?[/color][/color]

Can't see the original post so I'll have to tag this onto Salad's reply.

Question is. What does it all mean? Frequently here people post questions
like this when it appears that the basic design is flawed.

A table in a database is usually the result of defining that purely
theoretical thing a relation which is usually the implementation of an
entity. So what does tbl_Product_Lic ensor represent. Mmmm....usually a table
with 2 ID columns is a junction table (let's ignore Sublicensor_ID for now)
which is the usual way to implement a many to many join, MEANING, in this
case that many products can have many licensors. But you say that isn't the
case. See what I'm talking about, it's the actual meaning in the problem
domain that you need to get sorted.

So if each product will always have only one licensor, why not:

tlbProduct
ProductID
more product stuff...
LicensorID (foreign key to tblLicensor)

Can a Licensor licence more than one product?

If not then the above isn't correct, and you probably need a one to one. Or
just a single table, including licensor data as well as product data.

If it is many to one, product to licensor, does each product ALWAYS have a
licensor (in which case LicenceID in tblProduct is not allowed null)?

What is a sub_licensor? What do they do? What is their real life
relationship to products and licensors? When we know that we might then have
some idea how to represent them in the database.

Mike

[John]

Re: Unique index and null values

Thanks for the replies, and to Mike for the insightful response. There
is in fact a one-to-one relationship between a product and a licensor;
I will add a licensor_ID field into the product table instead of using
a juncture table. I kind of "inherited" bad data---a product would
occasionally have more than one licensor, which turned out to be
incorrect data.

Anyway, the product_id is really what will make the
product/relationship a unique record/relationship. I was just stuck
into a mindset of there being a many-to-many relationship based on the
(erroneous) data.

Thanks again...