Specify the foreign key in one column and the value of another column

Question

I have an ASSETS table that has a structure as shown below:

 ---------------------------------------------------- ID (PK) | DESCRIPTION | TYPE | Do- | Do+ | Dx- | Dx+ ---------------------------------------------------- 

Column

TYPE has a foreign key, possible values ​​are SECURITY or CURRENCY (i.e. FX), also I have two more tables: CURRENCIES (e.g. EUR , RUB or USD ):

 -------------------------------------------------------- ID (PK)| FROM (FK ASSETS.ID) | TO (FK ASSETS.ID) | VALUE -------------------------------------------------------- 

and SECURITIES (e.g. MTS , GAZP or VTB ):

 ---------------------------------------------------------- ID (PK)(FK ASSETS.ID)| CURRENCY (PK)(FK ASSETS.ID) | VALUE ---------------------------------------------------------- 

How can I make a constraint that not only acts as a foreign key in CURRENCIES.FROM , CURRENCIES.TO and SECURITIES.CURRENCY , but also checks if the ASSETS.TYPE link ASSETS.TYPE CURRENCY , and in SECURITIES also checks if the ASSETS.TYPE link for SECURITIES.ID equal to SECURITY ?

I think I can write triggers to check the value of ASSETS.TYPE , but now I'm looking for a different solution (if possible, of course).

If there are better ways to do what you need (like a better database design), share your ideas.

PS I suppose this is a fairly common problem, so if you have articles about it or similar questions asked on this network or some general solutions, feel free to share.

Answer 1

The answer to your original question is to use an additional CHECK constraint, for example:

 CREATE TABLE CURRENCIES ( ... CONSTRAINT c_asset_from CHECK(exists(select 1 from ASSETS a where a.id = from and a.type = 'CURRENCY')) ); 

And a similar restriction for the TO field and in the SECURITIES field for CURRENCY .
But I think your new design with separate FKs for security and CURRENCY is the best design.

Answer 2

IMO technically design can be criticized in two categories:

• The presence of a dual-use foreign key in the type asset table ( Polymorphic Association vs. Pattern ).
  This will break the first normal form (atomic release), losing referential integrity.
  The solution may be to simplify the relationship of inheritance. a table for currency and security tables called Money , containing common properties, such as name .
  The primary key of the Money table will be the primary key of the Currency and Security tables.
  The solution will have a foreign key Money inside Asset .
• Using a surrogate identifier in asset tables will result in a loss of business logic in the design of the schema.
  I prefer to have a composite primary key in the asset table PK{ID, TYPE(money fk)} . Check restrictions on CURRENCIES and SECURITIES to solve the problem.
  CURRENCIES_chk {FK.CURRENCY = FK_TO.Money && FK.CURRENCY = FK_FROM.Money} SECURITIES_chk {FK.SECURITY = FK.Money}
  
  

Answer 3

You can use validations for this. Do you want to hardcode these values?

 CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') ) 

Source: W3schools

And using firebird may require a different syntax. Take a look at: Firebird Link

Answer 4

You can do this declaratively by redesigning your keys and using identifying relationships.

Here is the drawing:

See how ASSET.ASSET_TYPE propagates through both branches, just for merging into SECURITY.ASSET_TYPE .

Since SECURITY.ASSET_TYPE is only one field, a single SECURITY line can never connect to multiple asset types. To say it a little differently: if ASSET and CURRENCY connected to the same SECURITY , they must have the same ASSET_TYPE .

In addition to this, CURRENCY never point to another type of ASSET .

If necessary, you can return the old surrogate keys (and other fields) to this model.

---

Saying that generating ASSET_NO presents some problems.

• You can simply use the auto-incrementing mechanism built into your DBMS , but this will leave holes (i.e. two different types of assets will never use the same integer, even if they can technically).
• Or you can find the next value manually, but you have to handle concurrency in this case (either serialize the inserts with a lock, or insert a retry if the simultaneous transaction tried the same value).