Foreign Key Constraint over two tables I am trying to put the following entity-model into practice:


We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in Store1, 20€ rent in Store2)
A Payment Type is a categroy like (rent, maintenance, legal fees, licenses) and each category is assigned a certaing account_type (e.g. budget_legal, budget_monthly, budget_extraordinary ) where multiple Payment types can have the same account_type
We have accounts: An account belongs to a certain store for a certain account type. So Store1 can have account-types budget_legal, budget_monthly, while Store2 has account types budget_legal, budget_monthly and budget_extra


Relationship Model:



Image Created with http://draw.io/

Now I want a constraint that each payment must belong to an existing account. I want to guarantee the following:


When I change the account_type of a Payment_type, all payments of this type will then "belong" to the respective accounts.
If I delete an account I want to delete all payments in this account ( like delete cascade in a foreign key )
I can only insert/update Payments so that each payment refers to a valid store_id/account_type, which exists in the accounts table.




Possible solutions which I tried but don't really work:

I tried to add the account_type to payments, but then I have doubled the field - and when I change a Payment type I will have to change the referenced payments via trigger. - But in the best case I don't want triggers to enforce data integrity - and I don't want duplicate data storage.

I tried to create a foreign key constraint over multiple tables / a view which joins payment with payment_types. Unfortunately this seems to be impossible in Oracle DBMS

I tried to create a virtual column on Payments, which calculates the value from payment_types, but virtual columns can only reference columns in the same table.
Some notes first:


Considering that Accounts has a unique constraint on (account_type, account_store), a Payment seem to be associated with only one Account. That suggests that the FK from Payments should reference Accounts (and not Stores). This change will also solve issue #2.
Adding account_type to Payments (and modifying accordingly the foreign key to Payment_types) seems like the most sensible approach and it will solve issue #3.
The two changes together will solve issue #1.
Oracle does not have ON UPDATE CASCADE option, so this issue (updating the account_type of a Payment_type to update the related Payments) needs to be addressed by either an update trigger or by a stored procedure (that will also check that related Accounts also exist (or are added) for all Stores that have Payments of the (updated) account_type.


The suggested design, assuming you also have an Account_types table (I renamed a few columns but that is up to you and your preferences / conventions):

Account_types
---------------
account_type_id  PK


Payment_types
---------------
account_type_id  PK, FK -&gt; Account_types
payment_type_id  PK


Stores
--------
store_id         PK


Accounts
---------------
account_type_id  PK, FK1 -&gt; Account_types
account_id       PK
store_id             FK2 -&gt; Stores


Payments
---------------
payment_id       PK
amount
account_type_id      FK1, FK2 
payment_type_id      FK1 -&gt; Payment_types
account_id           FK2 -&gt; Accounts


Note how both foreign keys from Payments use composite keys.