Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Separate foreign keys with shared ID space
Let's say I have a type hierarchy: (just an example)
the general entity customer:
CREATE TABLE customer(customer_id int, customer_name varchar(250), customer_type int)
three specific customer subtypes: 1 - business, 2 - home, 3 - university
represented by the following three tables (being subtypes, they share ID space for customer_id)
CREATE TABLE business_customer(customer_id int, business_sector int,
... )
CREATE TABLE home_customer(customer_id int, household_income_bracket
int, ...)
CREATE TABLE university_customer(customer_id int, number_students int,
...)
Is it possible to make a foreing key constraint that says:
the customer_id in busineness_customer table must reference only those customer_id in the customer table where customer_type == 1?
the customer_id in home_customer table must reference only those customer_id in the customer table where customer_type == 2?
Thanks!