Иностранные ключи в MySQL?

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

В этом случае я столкнулся с камнем преткновения, это внешние ключи в MySQL. Я не могу найти много чего другого в том, что они существуют в схеме хранения InnoDB, которую имеет MySQL.

Что представляет собой простой пример внешних ключей, реализованных в MySQL?

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

CREATE TABLE `posts` (
`pID` bigint(20) NOT NULL auto_increment,
`content` text NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`uID` bigint(20) NOT NULL,
`wikiptr` bigint(20) default NULL,
`cID` bigint(20) NOT NULL,
PRIMARY KEY  (`pID`),
Foreign Key(`cID`) references categories,
Foreign Key(`uID`) references users
) ENGINE=InnoDB;

Ответ 1

Предполагая, что ваши категории и таблица пользователей уже существуют и содержат соответственно cID и uID в качестве первичных ключей, это должно работать:

CREATE TABLE `posts` (
`pID` bigint(20) NOT NULL auto_increment,
`content` text NOT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`uID` bigint(20) NOT NULL,
`wikiptr` bigint(20) default NULL,
`cID` bigint(20) NOT NULL,
PRIMARY KEY  (`pID`),
Foreign Key(`cID`) references categories(`cID`),
Foreign Key(`uID`) references users(`uID`)
) ENGINE=InnoDB;

Имя столбца требуется в предложении references.

Ответ 2

Отредактировано: Роберт и Винко заявляют, что вам нужно объявить имя столбца с ссылкой в ограничении внешнего ключа. Это необходимо в InnoDB, хотя в стандартном SQL вам разрешено указывать имя столбца с ссылкой, если оно имеет одно и то же имя в родительской таблице.

Одна идиосинкразия, с которой я столкнулся в MySQL, заключается в том, что объявление внешнего ключа будет терпеть неудачу в нескольких случаях:

Ваша установка MySQL не включает механизм innodb
Ваш конфигурационный файл MySQL не включает механизм innodb
Вы не объявляете свою таблицу с помощью модификатора таблицы ENGINE = InnoDB
Столбец внешнего ключа - это не тот же тип данных, что и столбец первичного ключа в ссылочной таблице.

К сожалению, MySQL не сообщает, что ему не удалось создать ограничение внешнего ключа. Он просто игнорирует запрос и создает таблицу без внешнего ключа (если вы SHOW CREATE TABLE posts, вы не увидите объявления внешнего ключа). Я всегда думал, что это плохая функция MySQL!

Совет: целочисленный аргумент для целых типов данных (например, BIGINT (20)) не требуется. Он не имеет никакого отношения к размеру хранилища или диапазону столбца. BIGINT всегда одного размера, независимо от аргумента, который вы ему даете. Число ссылается на то, сколько цифр MySQL будет заполнять столбец, если вы используете модификатор столбца ZEROFILL.

Ответ 3

В этом есть код, показывающий, как создавать внешние ключи самостоятельно, и в CREATE TABLE.

Вот один из более простых примеров из этого:

CREATE TABLE parent (id INT NOT NULL,
                 PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                      ON DELETE CASCADE
) ENGINE=INNODB;

Ответ 4

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

SHOW CREATE TABLE posts;

Ответ 5

Предыдущие ответы касаются ограничения внешнего ключа. Хотя ограничение внешнего ключа, безусловно, полезно для поддержания ссылочной целостности, сама концепция "внешнего ключа" является фундаментальной для реляционной модели данных, независимо от того, используете ли вы ограничение или нет.

Всякий раз, когда вы выполняете equijoin, вы приравниваете внешний ключ к чему-то, обычно к ключу, который он ссылается. Пример:

select *
from 
   Students
inner join
   StudentCourses
on Students.StudentId = StudentCourses.StudentId

StudentCourses.StudentId - это внешний ключ, ссылающийся на Students.StudentId.