I have a table ASSETS
that has a structure as it is shown below :
----------------------------------------------------
ID (PK) | DESCRIPTION | TYPE | Do- | Do+ | Dx- | Dx+
----------------------------------------------------
TYPE
column has a foreign key, possible values are SECURITY
or CURRENCY
(i.e. FX), also I have two more tables : CURRENCIES
(for example, EUR
, RUB
or USD
) :
--------------------------------------------------------
ID (PK)| FROM (FK ASSETS.ID) | TO (FK ASSETS.ID) | VALUE
--------------------------------------------------------
and SECURITIES
(for example, MTS
, GAZP
or VTB
) :
----------------------------------------------------------
ID (PK)(FK ASSETS.ID)| CURRENCY (PK)(FK ASSETS.ID) | VALUE
----------------------------------------------------------
How I can make a constraint, that not only acts like foreign key in CURRENCIES.FROM
, CURRENCIES.TO
and SECURITIES.CURRENCY
,but also checks if referring ASSETS.TYPE
is CURRENCY
, and in SECURITIES
also checks if referring ASSETS.TYPE
for SECURITIES.ID
is SECURITY
?
I guess I can write triggers to check ASSETS.TYPE
value, but I am searching for another solution right now (if it is possible, of course).
If there are better ways to do the things a want (as a better database design), please, share your ideas.
P.S. I guess it is quite a common problem, so if there are articles about it or similar questions asked on this network or some general-case-solutions, feel free to share.