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?

Answer 1

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.