SQL Server : creating a table with a multi-column foreign key

Question

I have to create a set of tables in SQL Server.

Those are:

chips, for identifying a chip to be implanted to an animal
signals, because every chip generates a signal once every hour
signal receivers, stations which can receive a signal if a chip is in its radius
signal receivings, for signals that have been received by a receiver

I have a problem in creating those tables.

This is my code:

CREATE TABLE CHIPS (
    ID INT PRIMARY KEY,
    ...(not related attributes)...
    );

CREATE TABLE RECEIVERS (
    ID_receiver INT PRIMARY KEY,
    ...(some other attributes, not related to the problem)...
    );

CREATE TABLE SIGNALS (
    ID_chip INT FOREIGN KEY REFERENCES CHIPS, 
    Signal_no INT, 
    Date DATETIME, 
    PRIMARY KEY (ID_chip, Signal_no)
    );

CREATE TABLE SIGNAL_RECEIVINGS (
    ID_receiver INT REFERENCES RECEIVERS ,
    Id_chip INT REFERENCES SIGNALS(ID_chip), 
    Signal_no INT REFERENCES SIGNALS(Signal_no), 
    PRIMARY KEY (Id_chip, Signal_no, ID_receiver ) 
    );

My problem is that I don't know how to make a proper key for the SIGNAL_RECEIVERS. I want it to be (Id_chip, Signal_no, ID_receiver) because only then it would be unique, but SQL prints out an error :

The number of columns in the referencing column list for foreign key 'FK__SIGNAL_R__Si_no__5C187C73' does not match those of the primary key in the referenced table 'SIGNALS'.

Could someone help me out on this one?

The foreign key of any table **must EXACTLY match** the primary key it's referring to. It seems you're referring to `SINALS` which has a primary key made up of `(ID_chip, Signal_no)` - so your foreign key must also be those two columns exactly. What you use as the **primary key** for `SIGNAL_RECEIVERS` doesn't depend on other tables, really. And of course, you can always define that two-column FK to `SIGNALS` and then still put a `UNIQUE CONSTRAINT` on the three columns you mentioned (to ensure uniqueness of those tuples) — marc_s, Jan 06 '14 at 16:59
`CREATE TABLE SIGNAL_RECEIVINGS ( ID_reciver INT REFERENCES RECEIVERS, Id_chip INT, Signal_no INT, FOREIGN KEY (Id_chip, Signal_no ) REFERENCES SIGNALS, PRIMARY KEY (Id_chipu, Signal_no , ID_receiver) );` and what's wrong with this case? — Simon, Jan 06 '14 at 17:04
Your `FOREIGN KEY` lists **two columns**, while the **primary key** it is referring to is made up of **three columns** - this is **NOT** a match! If you want to reference a PK made up from three columns, then you **foreign key** **MUST** also contain all those three columns. — marc_s, Jan 06 '14 at 17:05

score 6 · Accepted Answer · edited May 23 '17 at 11:48

Your problem isn't in the Primary Key on SIGNAL_RECEIVINGS, that will work just fine. Your problem is that you have id_chip with a foreign key to SIGNALS, and Signal_no with a foreign key to SIGNALS, both referencing a single column. Given that the PK on SIGNALS is a composite key, your FK on SIGNAL_RECEIVINGS needs to also be a composite to the table.

You need to create a single FK, pointing at both columns:

CREATE TABLE SIGNAL_RECEIVINGS (
  ID_receiver INT REFERENCES RECEIVERS ,
  Id_chip INT, 
  Signal_no INT, 
  Foreign Key (id_chip, signal_no) references Signals (id_chip, signal_no),
  PRIMARY KEY (Id_chip, Signal_no, ID_receiver ) 
  );

That said, I think your use of Composite Primary Keys is going to get out of control they way you are using them all over the place. Pretty soon (carrying on the code that you have in place), you are going to end up with composite keys potentially tens of columns long. I'd direct you to this answer from another question detailing why simply adding an Identity column to your code will help simplify your keys down a fair bit.

Composite keys can be handy in places where the data has to appear multiple places anyway, but if you are adding those columns simply to satisfy the key constraints, then a single Identity column would suit much better.

Amir Keshavarz · Answer 2 · 2014-01-06T17:10:57.360

1

Try this:

CREATE TABLE SIGNAL_RECEIVINGS (
ID_receiver INT REFERENCES RECEIVERS ,
Id_chip INT  , 
Signal_no INT , 
PRIMARY KEY (Id_chip, Signal_no, ID_receiver ) ,
foreign key (id_chip,signal_no) references signals(id_chip,signal_no)
);

edited Jan 06 '14 at 17:10

answered Jan 06 '14 at 16:58

Amir Keshavarz

3,050
1
19
26

SQL Server : creating a table with a multi-column foreign key

2 Answers2