Может ли внешний ключ ссылаться на уникальный код?

Ответ 1

От Документация по MySQL:

InnoDB позволяет ограничению внешнего ключа ссылаться на не уникальный ключ. Это расширение InnoDB для стандартного SQL.

Тем не менее, существует основополагающая причина, чтобы избежать внешних ключей на неисторических столбцах ссылочной таблицы. То есть, какова должна быть семантика "ON DELETE CASCADE" в этом случае?

Документация далее сообщает:

Обработка ссылок на внешние ключи нестандартными ключами или ключами, которые содержат значения NULL, нечетко определена (...). Вам рекомендуется использовать внешние ключи, которые ссылаются только на UNIQUE (включая PRIMARY) и NOT NULL.

Ответ 2

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

Ответ 3

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

Если вы используете внешние ключи, которые не указывают на первичный ключ, вам также может понадобиться создать (не уникальный) индекс для столбца (ов), на который ссылаются ради производительности.

В зависимости от используемой СУБД. Я думаю, что некоторые делают это для вас неявно или используют некоторые другие трюки. RTM.

Ответ 4

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

Пример: две таблицы, КОЛЛЕДЖИ и СТУДЕНТЫ, содержат столбец под названием ZIPCODE.

Если мы быстро проверим

SELECT * FROM COLLEGES JOIN STUDENTS ON COLLEGES.ZIPCODE = STUDENTS.ZIPCODE

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

Но наша схема не имеет такой таблицы. Однако, поскольку наша схема не имеет такой таблицы, это не означает, что таких данных не существует. где-то, на земле USPO, есть только такой стол. И оба COLLEGES.ZIPCODE и STUDENTS.ZIPCODE являются ссылками на эту таблицу, даже если мы ее не признаем.

Это больше связано с философией данных, чем с практикой построения баз данных, но это наглядно иллюстрирует что-то фундаментальное: данные имеют характеристики, которые мы обнаруживаем, а не только характеристики, которые мы изобретаем. Конечно, то, что мы обнаруживаем, может быть тем, что кто-то изобрел. Это, безусловно, случай с ZIPCODE.

Ответ 5

PostgreSQL также отказывается от этого (в любом случае, даже если это возможно, это не значит, что это хорошая идея):

essais=> CREATE TABLE Cities (name TEXT, country TEXT);
CREATE TABLE
essais=> INSERT INTO Cities VALUES ('Syracuse', 'USA');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Syracuse', 'Greece');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Paris', 'France');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Aramits', 'France');
INSERT 0 1
essais=> INSERT INTO Cities VALUES ('Paris', 'USA');
INSERT 0 1

essais=> CREATE TABLE People (name TEXT, city TEXT REFERENCES Cities(name));
ERROR:  there is no unique constraint matching given keys for referenced table "cities"

Ответ 6

В какой базе данных мы говорим? В SQL 2005 я не могу создать ограничение внешнего ключа, которое ссылается на столбец, который не имеет уникального ограничения (первичный ключ или иначе).

create table t1
(
  id int identity,
  fk int
);

create table t2
(
  id int identity,
);

CREATE NONCLUSTERED INDEX [IX_t2] ON [t2] 
(
    [id] ASC
);
ALTER TABLE t1 with NOCHECK
ADD CONSTRAINT FK_t2 FOREIGN KEY (fk)
    REFERENCES t2 (id) ;


Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 't2' 
that match the referencing column list in the foreign key 'FK_t2'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Если вы действительно можете это сделать, у вас будет эффективно отношение "многие ко многим", что невозможно без промежуточной таблицы. Мне было бы действительно интересно узнать об этом подробнее...

См. этот связанный вопрос и ответы.

Ответ 7

Necromancing.

Как уже говорили другие, вы не должны ссылаться на не уникальный ключ как внешний ключ.

Но вместо этого вы можете вместо этого (без исключения каскадной опасности) добавить ограничение проверки (по крайней мере, в MS-SQL).

Это не совсем то же самое, что и внешний ключ, но, по крайней мере, это предотвратит внесение недействительных/потерянных/мертвых данных.

См. здесь для справки (вам нужно будет перенести код MS-SQL в синтаксис MySQL):
Внешний ключ к непервичному ключу

Изменить:
В поисках причин для downvote, согласно Mysql CHECK Constraint, MySQL действительно не поддерживает ограничения CHECK.

Вы можете определить их в своем DDL-запросе по причинам совместимости, но они просто игнорируются...

Но, как упоминалось выше, вы можете создать триггер BEFORE INSERT и BEFORE UPDATE, который вызовет ошибку, когда требования данных не будут удовлетворены, что в основном одно и то же, за исключением того, что это еще больше беспорядок.

Что касается вопроса:

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

В любой разумной РСУБД это верно.

Тот факт, что это возможно в MySQL, является еще одной причиной, почему
MySQL - это разумная СУБД.
Это может быть быстро, но жертвовать ссылочной целостностью и качеством данных на алтаре скорости не является моей идеей качества-rdbms.

На самом деле, если это не соответствует ACID, это не совсем (правильно функционирующая) РСУБД вообще.