Re: Having an optional foreign key (ie. sometimes NULL) ?

> On Thu, 13 Mar 2003 22:58:14 +0000, Tom <tom@vms7.com> wrote:
>>"some products will only be available to a single
>>client whereas other products will be available to all clients".
>>
>>  I thought the best way to get around this would be to have the
>> client as a
>>foreign key in products but for products available to all clients this
>> won't  work.
>
> It will work, if you let NULL represent "available to all clients":
>
> CREATE TABLE client (id INT PRIMARY KEY, name TEXT);
> INSERT INTO client VALUES (1, 'Joe''s Insurance Company');
> INSERT INTO client VALUES (2, 'Second Client');
>
> CREATE TABLE product (id INT, name TEXT, cid INT REFERENCES client);
> INSERT INTO product VALUES (11, 'for Joe', 1);
> INSERT INTO product VALUES (22, 'for all', NULL);
>
> It's perfectly legal to have a nullable foreign key column.
>

Does "will only be available to a single client" refer always to the one
same particular client, or are there multiple clients that have unique,
exclusive access to different subsets of the product list?

~Berend Tober