SQL Server: создание таблицы с многостолбцовым внешним ключом

Мне нужно создать набор таблиц в SQL Server.

Это:

  • chips, для идентификации чипа, имплантируемого животному
  • signals, потому что каждый чип генерирует сигнал один раз в час
  • signal receivers, станции, которые могут принимать сигнал, если чип находится в их радиусе
  • signal receivings, для сигналов, принятых приемником

У меня проблема с созданием этих таблиц.

Это мой код:

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 ) 
    );

Моя проблема в том, что я не знаю, как сделать правильный ключ для SIGNAL_RECEIVERS. Я хочу, чтобы это было (Id_chip, Signal_no, ID_receiver), потому что только тогда оно было бы уникальным, но SQL выводит ошибку:

Количество столбцов в списке ссылочных столбцов для внешнего ключа «FK__SIGNAL_R__Si_no__5C187C73» не соответствует количеству столбцов первичного ключа в ссылочной таблице «SIGNALS».

Может ли кто-нибудь помочь мне в этом?


sql sql-server foreign-keys create-table multiple-columns
person Simon    schedule 06.01.2014    source источник
comment
Внешний ключ любой таблицы должен ТОЧНО соответствовать первичному ключу, на который он ссылается. Кажется, вы имеете в виду SINALS, первичный ключ которого состоит из (ID_chip, Signal_no), поэтому ваш внешний ключ также должен точно соответствовать этим двум столбцам. То, что вы используете в качестве первичного ключа для SIGNAL_RECEIVERS, на самом деле не зависит от других таблиц. И, конечно же, вы всегда можете определить этот двухстолбцовый FK как SIGNALS, а затем по-прежнему поместить UNIQUE CONSTRAINT в три упомянутых вами столбца (чтобы обеспечить уникальность этих кортежей)   -  person marc_s    schedule 06.01.2014
comment
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) ); а что не так с этим делом?   -  person Simon    schedule 06.01.2014
comment
Ваш FOREIGN KEY перечисляет два столбца, а первичный ключ, на который он ссылается, состоит из трех столбцов — это НЕ совпадение! Если вы хотите сослаться на PK, состоящий из трех столбцов, то ваш внешний ключ ДОЛЖЕН также содержать все эти три столбца.   -  person marc_s    schedule 06.01.2014

Ответы (2)


arrow_upward
6
arrow_downward

Ваша проблема не в первичном ключе на SIGNAL_RECEIVINGS, это будет работать нормально. Ваша проблема в том, что у вас есть id_chip с внешним ключом на SIGNALS и Signal_no с внешним ключом на SIGNALS, оба ссылаются на один столбец. Учитывая, что PK на SIGNALS является составным ключом, ваш FK на SIGNAL_RECEIVINGS также должен быть составным для таблицы.

Вам нужно создать единый FK, указывающий на оба столбца:

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 ) 
  );

Тем не менее, я думаю, что ваше использование составных первичных ключей выйдет из-под контроля, как вы используете их повсюду. Довольно скоро (продолжая код, который у вас есть) вы получите составные ключи, которые потенциально могут иметь длину в десятки столбцов. Я бы направил вас к этому ответу на другой вопрос с подробным описанием того, почему простое добавление столбца Identity в ваш код поможет упростить ваши ключи немного вниз.

Составные ключи могут быть удобны в местах, где данные в любом случае должны отображаться в нескольких местах, но если вы добавляете эти столбцы просто для удовлетворения ограничений ключей, то один столбец Identity подойдет гораздо лучше.

person Obsidian Phoenix    schedule 06.01.2014

arrow_upward
1
arrow_downward

Попробуй это:

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)
);
person Amir Keshavarz    schedule 06.01.2014