Thread: Foreign key split across two tables

Foreign key split across two tables

From
Raju Mathur
Date:
Hi,

Checked out the complete manual but I can't see a simple answer for
this one:

I have an invoice which I'm representing as two tables in the schema:
the invoice header and the line items.  One of the tables referred is
the customer table, which uses customer number and location as the
key.  My problem is that the customer number is constant for the whole
invoice and defined in the header, while the location varies by line
item and is defined in the line item table; hence the foreign key
(customer) is split across the invoice header and the invoice line
item tables.

Is there any way to validate that each line item contains a valid
customer+location value?  Last resorts would include defining a
C/Perl/Tcl function, defining the customer code in the line item table
or inheriting the line item table from the header table, but these
solutions are unattractive due to either aesthetic or normalisation
shortcomings.

Regards,

-- Raju
--
Raju Mathur          raju@kandalaya.org           http://kandalaya.org/