Re: Foreign keys: how to turn referential integrity

On Fri, 23 Aug 2002, Jean-Christian Imbeault wrote:

> I have a table of line_items and distributor_orders somewhat like this:
>
> create table DIST_ORDER (
>
> id serial primary key,
> distributor_id integer references DISTRIBUTORS(id),
> submit_time timestamp (0) without time zone not null
> );
>
> create table MEMBER_INVOICE_LI (
>
> invoice_id integer references INVOICE_CART(id),
> id integer not null, --line item number
> dist_order_id integer references DIST_ORDER(id),
> prod_id char(12) references PRODUCTS(id),
> quantity int2 not null,
> price integer not null,
> shipped boolean not null,
>
> primary key (invoice_id, id)
> );
>
> The problem I have is that distributor orders are only generated at the
> end of the day, so when a customer creates an order, a line item is
> created *but* there is no distributor order to assign it to yet.
>
> How can I set the constraint to check referential integrity or let the
> value be null?
>
> Is this possible? Is it recommendable?

Are you running into a problem? NULLs should be allowed in single column
references barring a separate not null constraint (it's a little more
complicated for multiple column foreign keys)