Как изменить foreign key mysql

Введение 

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

Что такое первичный и внешний ключи и зачем они нужны

Начнем рассмотрение данного вопроса с двух самых главных элементов: первичного и внешнего ключей.

Первичный ключ или primary key

Первичный ключ — особенное поле в SQL-таблице, которое позволяет однозначно идентифицировать каждую запись в ней. Как правило, эти поля используются для хранения уникальных идентификаторов объектов, которые перечислены в таблице, например, это может быть ID клиента или товара.
Первичный ключ имеет несколько свойств:

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

Внешний ключ или foreign key

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

Давайте рассмотрим все это на примере простой базы данных. Для начала нам понадобится создать базу данных. Заходим в MySQL и создаем базу данных, в которой будут храниться записи о книгах в библиотеке:

    create database slcbookshelf;

Так как дальше мы будем работать с этой базой, вводим команду:

    use slcbookshelf;

И создаем таблицу, в которой будут храниться записи о книгах в библиотеке:

    CREATE TABLE books (
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
book_category VARCHAR(255));

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

    DESC books;

Вывод команды будет выглядеть следующим образом:

    mysql> DESC books;
+---------------+--------------+------+-----+---------+-------+
| Field     	| Type     	| Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| book_id   	| int      	| NO   | 	| NULL	|   	|
| book_name 	| varchar(255) | NO   | 	| NULL	|   	|
| book_category | varchar(255) | YES  | 	| NULL	|   	|
+---------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Первичный ключ при создании таблицы

Вы можете также создать его при создании таблицы, добавив в команду для создания таблицы следующую запись:

    PRIMARY KEY (book_id)

В таком случае поле book_id после создания таблицы будет являться первичным ключом для таблицы books.

Создание первичного ключа при помощи ALTER TABLE

Если таблица уже создана, а первичный ключ в ней не указан, вы можете создать ключевое поле, с помощью команды ALTER TABLE. Команда ALTER TABLE помогает изменять уже существующие столбцы, удалять их или добавлять новые. Чтобы определить первичный ключ в поле book_id, выполните команду:

    ALTER TABLE books
ADD PRIMARY KEY (book_id);

Проверяем:
mysql> DESC books;
+---------------+--------------+------+-----+---------+-------+
| Field     	| Type     	| Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| book_id   	| int      	| NO   | PRI | NULL	|   	|
| book_name 	| varchar(255) | NO   | 	| NULL	|   	|
| book_category | varchar(255) | YES  | 	| NULL	|   	|
+---------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Установка внешнего ключа MySQL при создании таблицы и с помощью ALTER TABLE

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

Внешний ключ при создании таблицы

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

    FOREIGN KEY (author_id) REFERENCES authors(author_id)

В итоге запрос, которым создается такая таблица будет выглядеть следующим образом:

    CREATE TABLE books (
book_id INT NOT NULL,
book_name VARCHAR(255) NOT NULL,
book_category VARCHAR(255),
FOREIGN KEY (author_id) REFERENCES authors(author_id));

Создание внешнего ключа при помощи ALTER TABLE

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

    ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(author_id);

Сценарии использования внешнего ключа

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

Каскадное удаление или CASCADE

Каскадное удаление позволит вам одновременно удалить строки из главной таблицы, а вместе с ними удалить все связанные строки в других таблицах. Задается каскадное удаление таким запросом:

    CREATE TABLE Orders(
Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE);

Аналогично работает метод ON UPDATE CASCADE. При попытке изменить значение, записанное в поле первичного ключа, изменение будет применено к внешнему ключу, связанному с данным полем. Этот метод используется крайне редко, так как первичные ключи практически не являются изменяемыми полями.

RESTRICT

Опция RESTRICT позволяет отклонять все запросы на изменение или удаление строк в главной таблице при условии, что в связанной таблице также имеются строки. Задается данное ограничение следующим запросом:

    CREATE TABLE Orders(
Id INT PRIMARY KEY AUTO_INCREMENT,
Customer_Id INT,
CreatedAt Date,
FOREIGN KEY (Customer_Id) REFERENCES Customers (Id) ON DELETE RESTRICT);

Заключение

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

In this article, we will discuss the overview of foreign keys and will discuss how to add a foreign key using ALTER in MySQL step by step. Let’s discuss it one by one.

Foreign key : 
If an attribute is a primary key in one table but was not used as a primary key in another table then the attribute which is not a primary key in the other table is called a foreign key. If the changes made or any data is manipulated in any of the tables the changes get reflected in both the tables with the help of foreign key constraint.

Steps to add a foreign key using ALTER in MySQL :
Here let us see how to add an attribute of student which is the primary key in the student table as a foreign key in another table exam as follows.

Step-1: Creating a database university :
Here, you will see how to create a database in MySQL as follows.

CREATE DATABASE university;

Output :

Step-2: Using the database university :
Here, you will see how you can use the existing database which you have already created as follows.

USE university;

Output :

Step-3: Creating a table student :
Here, you will see how to create a table in MySQL as follows.

 CREATE TABLE student
 (
     student_id INT PRIMARY KEY,
     student_name varchar,
     student_branch varchar
 );

Output :

Step-4: Viewing the description of the table :
Here, you will see how to verify the table as follows.

DESCRIBE student;

Output :
Here, as you can see in the description the key column of student_id is PRI which means it is the primary key in that table student.

Step-5: Creating another table exam :
In this step, you will see one more table for reference.

CREATE TABLE exam
 (
    exam_id INT PRIMARY KEY,
    exam_name varchar(20)
 );

Output :

Step-6: Viewing the description of the table :
In this step, you can verify the table you have created.

DESCRIBE exam;

Output :

Step-7: Adding another column student_id into the exam table :
Here, you will see how to add another column student_id into the exam table as follows.

 ALTER TABLE exam
 ADD COLUMN student_id INT;

Output :

Step-8: Making a foreign key :
Here, you will see how to make the student_id attribute foreign key in the exam table which is the primary key in the student table as follows.

Syntax –

ALTER TABLE table_name
ADD FOREIGN KEY (column_name)
REFERENCE table_name(Referencing column_name in table_name);

Query –

ALTER TABLE exam
ADD FOREIGN KEY(student_id)
REFERENCES student(student_id);

Output :

Step-9: Verifying the exam table : 
Here, you will see the description of the exam table as follows. 

DESCRIBE exam;

Output :
Now as you can see in the description of the table exam one more column student_id is added and in the Key column of description, the student_id has MUL which means it is a foreign key.

In this article, we will discuss the overview of foreign keys and will discuss how to add a foreign key using ALTER in MySQL step by step. Let’s discuss it one by one.

Foreign key : 
If an attribute is a primary key in one table but was not used as a primary key in another table then the attribute which is not a primary key in the other table is called a foreign key. If the changes made or any data is manipulated in any of the tables the changes get reflected in both the tables with the help of foreign key constraint.

Steps to add a foreign key using ALTER in MySQL :
Here let us see how to add an attribute of student which is the primary key in the student table as a foreign key in another table exam as follows.

Step-1: Creating a database university :
Here, you will see how to create a database in MySQL as follows.

CREATE DATABASE university;

Output :

Step-2: Using the database university :
Here, you will see how you can use the existing database which you have already created as follows.

USE university;

Output :

Step-3: Creating a table student :
Here, you will see how to create a table in MySQL as follows.

 CREATE TABLE student
 (
     student_id INT PRIMARY KEY,
     student_name varchar,
     student_branch varchar
 );

Output :

Step-4: Viewing the description of the table :
Here, you will see how to verify the table as follows.

DESCRIBE student;

Output :
Here, as you can see in the description the key column of student_id is PRI which means it is the primary key in that table student.

Step-5: Creating another table exam :
In this step, you will see one more table for reference.

CREATE TABLE exam
 (
    exam_id INT PRIMARY KEY,
    exam_name varchar(20)
 );

Output :

Step-6: Viewing the description of the table :
In this step, you can verify the table you have created.

DESCRIBE exam;

Output :

Step-7: Adding another column student_id into the exam table :
Here, you will see how to add another column student_id into the exam table as follows.

 ALTER TABLE exam
 ADD COLUMN student_id INT;

Output :

Step-8: Making a foreign key :
Here, you will see how to make the student_id attribute foreign key in the exam table which is the primary key in the student table as follows.

Syntax –

ALTER TABLE table_name
ADD FOREIGN KEY (column_name)
REFERENCE table_name(Referencing column_name in table_name);

Query –

ALTER TABLE exam
ADD FOREIGN KEY(student_id)
REFERENCES student(student_id);

Output :

Step-9: Verifying the exam table : 
Here, you will see the description of the exam table as follows. 

DESCRIBE exam;

Output :
Now as you can see in the description of the table exam one more column student_id is added and in the Key column of description, the student_id has MUL which means it is a foreign key.

Once unpublished, all posts by mcgurkadam will become hidden and only accessible to themselves.

If mcgurkadam is not suspended, they can still re-publish their posts from their dashboard.

Note:

Once unpublished, this post will become invisible to the public and only accessible to Adam McGurk.

They can still re-publish the post if they are not suspended.

Thanks for keeping DEV Community 👩‍💻👨‍💻 safe. Here is what you can do to flag mcgurkadam:

Make all posts by mcgurkadam less visible

mcgurkadam consistently posts content that violates DEV Community 👩‍💻👨‍💻’s
code of conduct because it is harassing, offensive or spammy.

Последнее обновление: 27.04.2019

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

Общий синтаксис установки внешнего ключа на уровне таблицы:

[CONSTRAINT имя_ограничения]
FOREIGN KEY (столбец1, столбец2, ... столбецN) 
REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, ... столбец_главной_таблицыN)
[ON DELETE действие]
[ON UPDATE действие]

Для создания ограничения внешнего ключа после FOREIGN KEY указывается столбец таблицы, который будет представляет
внешний ключ. А после ключевого слова REFERENCES указывается имя
связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES
идут выражения ON DELETE и ON UPDATE, которые задают действие при удалении и обновлении строки из
главной таблицы соответственно.

Например, определим две таблицы и свяжем их посредством внешнего ключа:

CREATE TABLE Customers
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Age INT, 
    FirstName VARCHAR(20) NOT NULL,
    LastName VARCHAR(20) NOT NULL,
    Phone VARCHAR(20) NOT NULL UNIQUE
);

CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date,
    FOREIGN KEY (CustomerId)  REFERENCES Customers (Id)
);

В данном случае определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и
представляет заказ, сделанный клиентом. Таблица Orders через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец
CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.

С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа:

CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date,
    CONSTRAINT orders_custonmers_fk 
    FOREIGN KEY (CustomerId)  REFERENCES Customers (Id)
);

ON DELETE и ON UPDATE

С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняются
соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции:

• CASCADE: автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.

• SET NULL: при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа
  значение NULL. (В этом случае столбец внешнего ключа должен поддерживать установку NULL)

• RESTRICT: отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице.

• NO ACTION: то же самое, что и RESTRICT.

• SET DEFAULT: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по
  умолчанию, которое задается с помощью атрибуты DEFAULT. Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение.

Каскадное удаление

Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы.
Для этого применяется опция CASCADE:

CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date,
    FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE
);

Подобным образом работает и выражение ON UPDATE CASCADE. При изменении значения первичного ключа автоматически изменится
значение связанного с ним внешнего ключа. Однако поскольку первичные ключи изменяются очень редко, да и с принципе не рекомендуется использовать в
качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко.

Установка NULL

При установки для внешнего ключа опции SET NULL необходимо, чтобы столбец внешнего ключа допускал значение NULL:

CREATE TABLE Orders
(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    CustomerId INT,
    CreatedAt Date,
    FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL
);