I have two main tables <Persons> with PersID PK and <Services> with ServID PK and aditional "shared table" <Addresses> intended for saving addresses for both Persons and Services. Addresses table has compound primary key (ItemID,Type,No) where ItemID is ID of Person or Service and Type is discriminator ['P','S'] definig if this address in Addresses table belongs to Person or Service. Each Person/Service could have more addresses what is defined by No part of compound PK. So there are one-to-many relationships between Person/Service and Addresses tables.
The quesion is how to create referential integrity constraint for this Addresses table? For example if I want cascade delete of Person with all its addresses from Addresses table?
Arcs: Address has 2 mutually exclusive foreign key constraints:
create table persons (person_id int primary key, ... );
create table services (service_id int primary key, ... );
create table addresses (address_id int primary key, person_id references persons, service_id references services,
check ((person_id is not null and service_id is null) or (person_id is null and service_id is not null)));
To be more concrete I share the Addresses table among four entities (persons,organizations,inventories,services). Each of these entities can have more than one address in Addresses table. Using one field (column) TYPE ['P','O','I','S'] I can define to which entity a particular address in Addresses table belongs. But I do not have referential constaint and therefore cannot implement cascade delete feature (what is my intention).
If I want to use Arc, do I have to have four additional fields (mutually exclusive) in my Addresses table (PersID,OrgID,InvID,ServID,No) without TYPE discriminator?