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

On Thu, 13 Mar 2003 22:58:14 +0000, Tom <tom(at)vms7(dot)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.

Servus
Manfred