MySQL таблица со столбцом varchar в качестве внешнего ключа

Question

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

CREATE TABLE network_classes (
    id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
    category VARCHAR(80) NOT NULL,
    PRIMARY KEY(id),
    KEY `key_1` (`id`,`category`)
)
ENGINE=InnoDB;


CREATE TABLE networks (
    id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(80) NOT NULL,
    director_id TINYINT(3) UNSIGNED NULL,
    director_name VARCHAR(100) NULL,
    description VARCHAR(1000) NULL,
    last_modified TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    user_id SMALLINT UNSIGNED NULL,
    PRIMARY KEY(id),
    KEY `networks_fk1` (`category`),
    CONSTRAINT `networks_fk1` FOREIGN KEY (`category`) REFERENCES `network_classes` (`category`) ON DELETE NO ACTION,
    INDEX networks_index2471(name),
    INDEX networks_index2472(director_id, director_name)
)
ENGINE=InnoDB;

и я получаю эту ошибку:

[Err] 1215 - Cannot add foreign key constraint

Я использую MySQL 5.6.12. Как я могу переписать свой запрос, чтобы исправить это?

Answer 1

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

 CREATE TABLE network_classes (
    id TINYINT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
    category VARCHAR(80) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE KEY `category_UNIQUE` (`category`),
    KEY `key_1` (`id`,`category`)
)
ENGINE=InnoDB;


CREATE TABLE networks (
    id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(80) NOT NULL,
    director_id TINYINT(3) UNSIGNED NULL,
    director_name VARCHAR(100) NULL,
    description VARCHAR(1000) NULL,
    last_modified TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    user_id SMALLINT UNSIGNED NULL,
    PRIMARY KEY(id),
    KEY `networks_fk1` (`category`),
    CONSTRAINT `networks_fk1` FOREIGN KEY (`category`) REFERENCES `network_classes` (`category`) ON DELETE NO ACTION,
    INDEX networks_index2471(name),
    INDEX networks_index2472(director_id, director_name)
)
ENGINE=InnoDB;

После этого вы сможете добавить нужный внешний ключ

Answer 2

типы столбцов в таблице и ссылочной таблице не совпадают для ограничения

Почему 2 столбца varchar одинакового размера не совпадают по типу? И, конечно, ответ очевиден. Оказывается, что в новой таблице столбец был UTF-8 вместо ASCII, как в ссылочной таблице. Поменял на ascii и сделал.

Answer 3

Цель FOREIGN KEY ограничение должно быть проиндексировано. Обычно это PRIMARY KEY так что это не проблема, но в вашем случае это не так (хотя это часть составного ключа, этого недостаточно)

Создайте индекс на своем network_classes,category поле:

CREATE INDEX category_idx ON network_classes(category);

Затем воссоздайте свой networks Таблица.

Answer 4

Для меня отсутствовала кодировка. Просто привел пример для справки.

      CREATE TABLE `client` (

int NOT NULL, varchar(255) NOT NULL,emailvarchar(255) НАБОР СИМВОЛОВ utf8 COLLATE utf8_general_ci ПО УМОЛЧАНИЮ NULL,created_atдата и время НЕ NULL,created_byvarchar(50) НЕ NULL ПО УМОЛЧАНИЮ 'admin',updated_atдата и время НЕ NULL,updated_byvarchar(50) НЕ NULL ПО УМОЛЧАНИЮ 'admin', ПЕРВИЧНЫЙ КЛЮЧ (id), УНИКАЛЬНЫЙ КЛЮЧname_UNIQUE(), КЛЮЧname_idx(name)) ENGINE=InnoDB НАБОР ШИМОВЫ ПО УМОЛЧАНИЮ=latin1

      create table `usecase` (`id` int NOT NULL AUTO_INCREMENT, `client` varchar(255) NOT NULL, `name` varchar(255), `description` varchar(1000), `rule_file` varchar(255), `parsed_rule_file` varchar(255), `archived` BOOLEAN DEFAULT 0, `state` ENUM('INITIATED','PARSED','UPLOADED', 'COMPLETE','FAILED'), `digest` varchar(255), `created_by` varchar(128) DEFAULT NULL, `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updated_by` varchar(128) DEFAULT NULL, `updated_at` datetime(3) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_usecase_id` (`client`,`name`), FOREIGN KEY (`client`) REFERENCES client(`name`))ENGINE=InnoDB DEFAULT CHARSET=latin1;

Я пропустил строку DEFAULT CHARSET=latin1

Answer 5

В

CONSTRAINT `networks_fk1` FOREIGN KEY (`category`)
REFERENCES `network_classess` (`category`) ON DELETE NO ACTION,

вы использовали network_classess вместо network_classes (как в вашем скрипте создания таблицы), поэтому таблица не существует.

РЕДАКТИРОВАТЬ

Имя вашего ограничения совпадает с ключом (networks_fk1) изменения.

Я лучше читаю ваш DDL.

Ваша таблица network_classes имеет идентификатор первичного ключа, поэтому правильно указать в качестве внешнего ключа поле для связи с вашим полем id, поле категории не должно появляться в вашей сети таблиц, но я думаю, что вы должны поместить fk_network_class (как int) в привязку к id (из сетевых_классов)