在一列上指定外键,并在另一列中指定值 [英] Specify foreign key on one column and the value of another column
问题描述
我有一个表格 ASSETS
,其结构如下所示:
--------------------------------------------- -------
ID(PK)| DESCRIPTION | TYPE | Do- | Do + | Dx- Dx +
--------------------------------------------- -------
TYPE
列具有外键,可能的值为 SECURITY
或 CURRENCY
(即 FX ),还有两个表: CURRENCIES
(例如, EUR
, RUB
或 USD
):
----------------------------------------- ---------------
ID(PK)| FROM(FK ASSETS.ID)| TO(FK ASSETS.ID)| VALUE
--------------------------------------------- -----------
和 SECURITIES
(例如,
MTS
, GAZP
或 VTB
):
-------------- --------------------------------------------
ID( PK)(FK ASSETS.ID)| CURRENCY(PK)(FK ASSETS.ID)| VALUE
--------------------------------------------- -------------
如何制定约束不仅在 CURRENCIES.FROM
, CURRENCIES.TO
和 SECURITIES.CURRENCY中起到外键的作用
,但也检查引用 ASSETS.TYPE
是 CURRENCY
还是 SECURITIES
还会检查 ASSETS.TYPE
是否为 SECURITIES.ID
code> SECURITY ?
我想我可以编写触发器来检查 ASSETS.TYPE
value,但我现在正在寻找另一个解决方案(如果可能,当然)。
有更好的方法来做一个想要的东西(作为一个更好的数据库设计),请分享你的想法。
我想这是一个相当普遍的问题,所以如果有文章或类似问题在这个网络或一些一般情况下解决方案,请随时分享。
CREATE TABLE CURRENCIES(
...
CONSTRAINT c_asset_from CHECK(exists(select 1 from ASSETS a where a.id = from和a.type ='CURRENCY'))
);
对 TO
字段和类似的约束 SECURITIES
为 CURRENCY
字段。
但我认为您的新设计,
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.
Answer to your original question is to use an additional CHECK
constraint like :
CREATE TABLE CURRENCIES (
...
CONSTRAINT c_asset_from CHECK(exists(select 1 from ASSETS a where a.id = from and a.type = 'CURRENCY'))
);
And similar constraion for TO
field and in SECURITIES
for CURRENCY
field.
But I think your new design, with separate FK for security
and currency
, is better design.
这篇关于在一列上指定外键,并在另一列中指定值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!