Код ошибки MySQL 1215: “Не удается добавить ограничение внешнего ключа”

ProgramBox

Код ошибки MySQL 1215: “Не удается добавить ограничение внешнего ключа”

Post author:admin
Запись опубликована:23 марта, 2023
Post category:Вопросы по программированию

#mysql #sql #phpmyadmin

Вопрос:

Я продолжаю получать эту ошибку при попытке создать таблицу с помощью SQL.

У меня есть эти две таблицы:

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

Кто-нибудь может увидеть, что не так с моим кодом?

Спасибо!

1. ключи должны быть идентичны в обеих таблицах. Являются ли обе таблицы одного и того же типа — InnoDB? Также убедитесь, что оба INT столбца имеют одинаковую длину

2. Вы ссылаетесь на ту же таблицу. ‘ВНЕШНИЙ КЛЮЧ (Super_id) ССЫЛАЕТСЯ НА СОТРУДНИКА (E_id)’.. это должна быть другая (внешняя) таблица

3. У вас есть внешний ключ в employee, который ссылается на department, и внешний ключ в department, который ссылается на employee — это не сработает, поскольку таблица, на которую ссылается ссылка, должна существовать до того, как вы создадите таблицу ссылок. Если вы действительно хотите это сделать, создайте таблицы без FKS и используйте операторы ALTER для добавления FKS

Ответ №1:

Определения внешнего ключа должны точно соответствовать столбцам первичного ключа, на которые они ссылаются. В этом случае вы определили, Department.M_id чтобы a был целочисленным столбцом с нулевым значением, в то время как EMPLOYEE.E_id целое число не обнуляемо. Попробуйте сделать M_id not nullable:

 CREATE TABLE Department (
    D_name VARCHAR(100) NOT NULL,
    D_id INT NOT NULL,
    M_id INT NOT NULL DEFAULT 0000,
    ...
    FOREIGN KEY (M_id) REFERENCES EMPLOYEE(E_id)
    ON DELETE SET DEFAULT   ON UPDATE CASCADE
)

Ответ №2:

Ваш код содержит несколько ошибок:

varchar() слишком большая длина.
У вас есть прямая ссылка на ограничение внешнего ключа.
SET DEFAULT на самом деле не работает.

Вы хотите что-то вроде этого:

 CREATE TABLE employees (
  employee_id int not null primary key,
  Job_type VARCHAR(100),
  Ssn INT NOT NULL,
  Salary DECIMAL NOT NULL,
  Address VARCHAR(500) NOT NULL,
  First_name VARCHAR(50) NOT NULL,
  M_initial CHAR(1),
  Last_name VARCHAR(50) NOT NULL,
  E_end_date DATE,
  E_start_date DATE NOT NULL,
  department_id INT NOT NULL,
  Super_id INT,
  FOREIGN KEY (Super_id) REFERENCES employees(employee_id) ON DELETE SET NULL ON UPDATE CASCADE,
  UNIQUE (Ssn)
);

CREATE TABLE departments (
  department_id int primary key,
  D_name VARCHAR(100) NOT NULL,
  D_id INT NOT NULL,
  M_id INT DEFAULT 0000,
  Manager_start_date DATE NOT NULL,
  Manager_end_date DATE,
  Report VARCHAR(8000),
  Num_of_employees INT NOT NULL,
  FOREIGN KEY (M_id) REFERENCES employees(employee_id) ON DELETE SET NULL ON UPDATE CASCADE,
  UNIQUE (D_name)
);

ALTER TABLE employees ADD CONSTRAINT FOREIGN KEY (department_id) REFERENCES departments(department_id)
  ON DELETE CASCADE ON UPDATE CASCADE;

Я также изменил несколько других вещей:

Имена таблиц являются множественными.
Первичные ключи представляют собой форму единственного числа, за которой следует «_id».
Внешние ключи и первичные ключи имеют одинаковое имя.
Первичный ключ — это первый столбец в таблице.

Вот строка db<>, показывающая, что это работает.

Ответ №3:

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

Однако — Вы не можете ссылаться на таблицу, которая еще не существует ( REFERENCES Department(D_id) ). Вы должны либо удалить ограничения ВНЕШНЕГО КЛЮЧА из инструкций CREATE, а затем добавить их в инструкции ALTER TABLE.

Пример:

 CREATE TABLE EMPLOYEE (...);
CREATE TABLE Department (...);

ALTER TABLE EMPLOYEE 
  ADD FOREIGN KEY (D_id)
  REFERENCES Department(D_id)
  ON DELETE CASCADE
  ON UPDATE CASCADE
;

ДЕМОНСТРАЦИЯ

Или временно отключить проверки внешнего ключа:

 SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE EMPLOYEE (...);

CREATE TABLE Department (...);

SET FOREIGN_KEY_CHECKS = 1;

ДЕМОНСТРАЦИЯ

Вы также не можете использовать ON DELETE SET DEFAULT . InnoDB его не поддерживает. Вам нужно изменить его на ON DELETE SET NULL . Если вы хотите такого поведения, вам нужно будет реализовать его либо в коде вашего приложения, либо в триггере.

Я бы также использовал TEXT в качестве типа данных вместо VARCHAR(30000) .