Sharing composite foreign key based on the same columns across multiple tables in MS SQL Server

I have database with multiple tables which contains information about different instruments (Currencies, Funds, Equities etc.). All instruments have Type and Id fields which represents unique key for instrument. And I have one more table InstrumentMeta with collects statistic information about all instruments which has pair (Type,Id) as a primary key.

1. Instrument_1 - Type(int), Id(string), Values, ...
2. Instrument_2 - Type(int), Id(string), Code, ...
3. …
4. InstrumentMeta - Type(int), Id(string), PerformanceValue1, PerformanceValue2, PerformanceValue3

Is it possible to create foreign keys which based on the the same pair (Type, Id) for InstrumentMeta table and all Instrument_ tables?

Answer

The short answer is “No!” This has nothing to do with composite keys, it is true for all keys. A foreign key must specify the ONE table that is referenced by the key.

However, you must have one “master” table that contains all the different combinations of key values. From what I see, that looks like it may be the meta table as it must contain all the Type/Id values. So that is the master table and all the “instrument_x” tables are the subtables.

The subtables contain the FK referencing back to the master table. Here’s an example.

create table Meta(
    Type    varchar( 16 ) not null,
    ID      smallint not null,
    <other meta fields>,
    constraint PK_Meta primary key( Type, ID ),
    constraint CK_Meta_Type check( Type in( 'Currency', 'Fund', 'Equity' ))
);
create table Currencies(
    Type    varchar( 16 ) not null,
    ID      smallint not null,
    <other currency fields>,
    constraint PK_Currencies primary key( Type, ID ),
    constraint CK_Currencies_Type check( Type = 'Currency' ),
    constraint FK_Currencies_Meta foreign key( Type, ID )
        references Meta( Type, ID )
);
create table Funds(
    Type    varchar( 16 ) not null,
    ID      smallint not null,
    <other fund fields>,
    constraint PK_Funds primary key( Type, ID ),
    constraint CK_Funds_Type check( Type = 'Fund' ),
    constraint FK_Funds_Meta foreign key( Type, ID )
        references Meta( Type, ID )
);

and so forth.

This design forces you to enter a “Currency:42” row in the meta table before you can create ID 42 in the Currencies table. But that is probably a Very Good Idea anyway.