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)