在一列上指定外键，并在另一列中指定值 [英] 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，但我现在正在寻找另一个解决方案（如果可能，当然）。

有更好的方法来做一个想要的东西（作为一个更好的数据库设计），请分享你的想法。

我想这是一个相当普遍的问题，所以如果有文章或类似问题在这个网络或一些一般情况下解决方案，请随时分享。

例如： c> / p>

  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屋！