Ссылка внешнего ключа на первичный ключ на два столбца
Я создаю базу данных, которая должна работать над MySQL, PostgreSQL и SQLite. Одна из моих таблиц имеет первичный ключ на два столбца:
CREATE TABLE tournament (
state CHAR(2) NOT NULL,
year INT NOT NULL,
etc...,
PRIMARY KEY(state, year)
);
Я хочу ссылку на tournament таблица от другой таблицы, но я хочу, чтобы эта ссылка была nullable. Вот то, как я мог бы сделать это, предположив, что у победителя не обязательно есть турнир:
CREATE TABLE winner (
name VARCHAR NOT NULL,
state CHAR(2) NULL,
year INT NULL
);
Если state является пустым, но year не, или наоборот, таблица была бы непоследовательна. Я верю следующему FOREIGN KEY ограничение фиксирует его:
ALTER TABLE winner ADD CONSTRAINT FOREIGN KEY fk (name, state) REFERENCES tournament (name, state);
Действительно ли это - надлежащий способ осуществить непротиворечивость? Эта схема правильно нормализована?
1 ответ
Правило №1: ВСЕГДА СКАЗЫВАЙТЕ БАЗУ ДАННЫХ, КОТОРУЮ ВЫ ИСПОЛЬЗУЕТЕ
Хорошо, я предлагаю вам ознакомиться с предложением ON DELETE и предложением MATCH . Поскольку Pg в значительной степени совместим с SQL, я покажу вам текущую документацию по CREATE TABLE .
Отрывок:
Эти предложения определяют ограничение внешнего ключа , которое требует, чтобы группа из одного или нескольких столбцов новой таблицы содержала только значения { {1}}, которые соответствуют значениям в указанном столбце (столбцах) некоторой строки таблицы, на которую указывает ссылка . Если refcolumn опущен , используется первичный ключ возвращаемого объекта. Столбцы, на которые указывает ссылка , должны быть столбцами ограничения уникальности или первичного ключа в указанной таблице. Обратите внимание, что ограничения внешнего ключа не могут быть определены между временными таблицами и постоянными таблицами.
Значение, вставленное в ссылающийся столбец (столбцы) , сопоставляется со значениями указанной таблицы и указанными столбцами с использованием заданного типа соответствия . . Существует три типа соответствия : ПОЛНОЕ СООТВЕТСТВИЕ, ЧАСТИЧНОЕ СООТВЕТСТВИЕ и ПРОСТОЕ СООТВЕТСТВИЕ, что также является значением по умолчанию для . MATCH FULL не позволяет одному столбцу внешнего ключа из нескольких столбцов иметь значение , если все столбцы внешнего ключа не имеют значения NULL. MATCH SIMPLE позволяет некоторым столбцам внешнего ключа быть пустыми, в то время как другие части внешнего ключа не равны . MATCH PARTIAL еще не реализован .
Кроме того, при изменении данных в столбцах, на которые указывает ссылка, определенные действия выполняются с данными в столбцах этой таблицы. Предложение ON DELETE определяет действие, выполняемое при удалении указанной строки в указанной таблице . Аналогичным образом, предложение ON UPDATE указывает действие, которое должно выполняться, когда столбец, на который указывает ссылка в указанной таблице обновляется до нового значения. {{1} }} Если строка обновлена, но столбец, на который указывает ссылка , на самом деле не изменен, никаких действий не выполняется. Ссылочные действия, кроме проверки NO ACTION не может быть отложено, даже если ограничение объявлено отложенным. Существуют следующие возможные действия для каждого предложения:
Кроме того, здесь есть главное исключение с MS SQL, которое не допускает частичное совпадение (MATCH SIMPLE и MATCH PARTIAL) поведения во внешних ключах ( значения по умолчанию и принудительное выполнение MATCH FULL). Существуют обходные пути, при которых вы создаете индекс MATCH FULL для той части таблицы, которая НЕ НУЖНА для любой из составляющих составного ключа.