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

Question

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.

See my answer [here](https://stackoverflow.com/a/26093733/3404097). (That particular question confusedly partly has no Student table (your ASSESTS_DATA) in its original text but has one in its Edit diagram.) Your 1st design is like the one I suggest (relationally straightforward) (1st set of bullets), and yours can be modified/CHECKed as I suggest (second set of bullets) — philipxy, Dec 25 '14 at 04:23
On a first glance it looks strange to me to treat Securities and Currencies as just two types of assets. One (Security) is something that has a value - be it a building, a mere certificate of debt or whatever - whereas the other (Currency) is just a unit of measure. I would rather couple Securities and *Money*, with Money being cash in different currencies, money in various bank accounts, etc. — Thorsten Kettner, Dec 29 '14 at 09:22
@Thorsten Kettner : _FX_ and _Securities_ have almost the same set of properties, like it is shown above and in such procedures like finding a portfolio cost or initial (+ adjusted initial) and minimum margins they are treated like an asset, not money or security. — potashin, Dec 29 '14 at 15:10

score 4 · Accepted Answer · edited Jan 04 '15 at 18:15

4

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.

edited Jan 04 '15 at 18:15

potashin

44,205
11
83
107

answered Dec 25 '14 at 09:39

ain

22,394
3
54
74

a.id = from? What does that mean? And when I try something similar I get 'Subqueries are not allowed in this context. Only scalar expressions are allowed.' Thus no select from different table. How is this working for you? – Jacob-Jan Mosselman Jun 05 '20 at 08:58
2

Different database engines support different features. The question was originally tagged as `FireBird` which support that. I guess you use mySql which doesn't – ain Jun 06 '20 at 07:59

score 2 · Answer 2 · edited May 23 '17 at 12:29

2

IMO technically the design could be criticized in two categories:

Having a dual-purpose foreign key in Asset table called type (Polymorphic Association anti-pattern).
That will violating first normal form (atomic issue), loosing referential integrity.
A solution could be simplification of the relationship by inheritance.
Having a base table for Currency and Security tables called Money,containing shared properties of them, like name.
primary key of Money table will be primary key of Currency and Security tables.
Having foreign key of Money inside Asset will be the solution.
Using surrogate identifier on Asset tables, that will result losing business logic in schema design.
I will prefer haveing composite primary key in Asset Table PK{ID, TYPE(money fk)}.
Then having check constraints on CURRENCIES and SECURITIES will solve the problem.
CURRENCIES_chk {FK.CURRENCY = FK_TO.Money && FK.CURRENCY = FK_FROM.Money} SECURITIES_chk {FK.SECURITY = FK.Money}

edited May 23 '17 at 12:29

Community

1
1

answered Dec 28 '14 at 14:35

Mohsen Heydari

7,256
4
31
46

There was an idea like: table `ASSET_TYPE` (one field `ID`, possible values `SECURITY`/`CURRENCY`), table `MONEY` (not a good name choice for this table, but in grace of the compatibility with your answer I am leaving it as it is) (two fields, `ID`, `TYPE` (`FK` to the `ASSET_TYPE.ID`)). And a trigger to insert either in the `SECURITIES` table or `CURRENCIES` (`FX`, choosing the right words) upon the `MONEY.TYPE`. I guess I can use only one trigger for solving that issue. In your design I see only one flow : I can put securities like `GAZP` in `CURRENCY` table and vice versa. – potashin Dec 31 '14 at 13:18
Sorry for my poor English. In my answer there is not any Triggering mechanism, just two check constraints exist! May be I haven't understand the domain of your design. lets flow a scenario step by step to see what will happen. First we define `GAZP` as a row in Security? – Mohsen Heydari Dec 31 '14 at 13:32
I don't think that you haven't understood the concept, I just don't know how to make sure that securities won't go to currencies table(aka fx) and vice versa.in addition, they all should be in the money table. – potashin Dec 31 '14 at 16:20
In SECURITIES table The CHECK CONSTRAINT between SECURITY.FK = ASSET.TYPE wouldn't guaranty that? – Mohsen Heydari Dec 31 '14 at 17:41

score 1 · Answer 3 · edited Dec 27 '14 at 17:47

1

You could use checks 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 might require different syntax. Take a look at: Firebird reference

edited Dec 27 '14 at 17:47

potashin

44,205
11
83
107

answered Dec 22 '14 at 13:00

VeldMuijz

605
5
18

The Firebird `CHECK` syntax is the same. – Mark Rotteveel Dec 22 '14 at 13:04
In your example you check the columns of the same table, but can you provide an example that points to the value of the column from another table? – potashin Dec 22 '14 at 13:25
@notulysses In that case you might be better off with either rethinking your model, or to use triggers. – Mark Rotteveel Dec 22 '14 at 14:29

score 1 · Answer 4 · edited Dec 27 '14 at 17:12

You can do that declaratively by changing the design of your keys and using identifying relationships.

Here is the blueprint:

enter image description here

Look how ASSET.ASSET_TYPE is propagated through both "branches", only to be merged in the SECURITY.ASSET_TYPE.

Since SECURITY.ASSET_TYPE is just one field, one SECURITY row can never connect to multiple asset types. To say it slightly differently: if ASSET and CURRENCY are connected to the same SECURITY, they must have the same ASSET_TYPE.

In addition to that, CURRENCY can never point to ASSETs of different type.

You can bring back your old surrogate keys (and other fields) into this model as necessary.

That being said, generating ASSET_NO presents some challenges.

You can just use auto-incrementing mechanism built-into your DBMS, but that would leave "holes" (i.e. two different asset types will never use the same integer, even though they technically can).
Or you can find the next value manually, but you'll have to handle concurrency in that case (either serialize insertions through locking, or retry insertion in case concurrent transaction tried the same value).

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

4 Answers4

Linked