Как добавить foreign key mysql в существующую таблицу
Внешние ключи позволяют установить связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы.
Общий синтаксис установки внешнего ключа на уровне таблицы:
[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 );
Добавление внешнего ключа в существующую таблицу SQL
На днях столкнулся с проблемой добавления в таблицу поля, которое было бы внешним ключом для другой таблицы. Речь идет именно о таблице, в которой уже есть заполненные поля. Перечитав несколько источников и перепробовав несколько вариантов, определил для себя работающий (не знаю правда оптимальный он или нет), который и перенесу сюда чтобы не забыть, а возможно он пригодится ещё кому-то ) Итак, запрос:
ALTER TABLE articles ADD subcategoryId SMALLINT(4) NOT NULL DEFAULT 4; ALTER TABLE articles ADD FOREIGN KEY (`subcategoryId`) REFERENCES subcategories(`id`);
В первом запросе для таблицы articles мы создаем поле subcategoryId, которое будет выступать внешним ключом. Тут очень важны два момента. Во-первых — тип данных создаваемого столбца должен в точности совпадать с тем, на который он будет ссылаться как внешний ключ (вплоть до количества символов и установленного значения NOT NULL). И во-вторых — нужно установить в качестве дефолтного значения одно из существующих значений того столбца, на который мы будем ссылаться, чтобы в уже существующие записи было введено корректное значение нового внешнего столбца.
Второй запрос это собственно назначение нашего новосозданного столбца внешним ключом.
Ключевые слова:
- обучение программированию с нуля бесплатно
- блог о программировании
- Воронеж
- блог студента программиста
- Voronve’s blog
- Log in to post comments
- 15296 reads
Как создать внешний ключ в MySQL
Foreign key (внешний ключ) – это мощный инструмент в базах данных, который позволяет установить связь между двумя таблицами. Этот инструмент особенно полезен при проектировании баз данных, поскольку позволяет создавать сложные отношения между различными таблицами.
В этом пошаговом руководстве мы рассмотрим, как создать foreign key в MySQL. Мы детально изучим синтаксис команды ALTER TABLE, которая позволяет добавить foreign key в уже существующую таблицу.
Прежде чем приступить к созданию foreign key, необходимо иметь две таблицы, которые нужно связать между собой. В одной из таблиц должно быть столбец, который будет являться primary key (основным ключом). В другой таблице должен быть столбец, который будет являться foreign key (внешним ключом).
Для создания foreign key необходимо выполнить следующие шаги:
- Создать основную таблицу с основным ключом;
- Создать вторую таблицу с внешним ключом;
- Добавить foreign key с помощью команды ALTER TABLE;
- Протестировать связь между таблицами.
Создание foreign key позволяет установить связь между таблицами и обеспечить согласованность данных в них. Это очень важный инструмент при проектировании баз данных, поэтому его использование стоит изучить более подробно.
Что такое foreign key и зачем он нужен?
Foreign key определяется на одной или нескольких колонках в таблице-потомке и ссылается на колонку в таблице-родителе. Когда вы создаете foreign key, вы указываете, какие значения из колонки-родителя могут быть использованы в колонке-потомке. Это означает, что значения в колонке-потомке должны быть совместимы с значениями в колонке-родителе.
Foreign key имеет несколько важных преимуществ:
- Поддержка ссылочной целостности: foreign key гарантирует, что каждое значение в колонке-потомке будет существовать в соответствующей колонке-родителе. Это позволяет предотвращать ошибки, связанные с некорректными ссылками на данные.
- Улучшение производительности: foreign key может использоваться для оптимизации запросов, связывающих связанные таблицы. Он позволяет использовать операции объединения (join) для получения связанных данных.
- Создание связей между таблицами: foreign key позволяет создавать связи между данными в разных таблицах. Например, он может использоваться для связывания таблицы пользователей с таблицей заказов, чтобы установить, какому пользователю принадлежит каждый заказ.
Использование foreign key является важным аспектом проектирования баз данных. Он помогает обеспечить целостность данных и сделать структуру базы данных более понятной и удобной для использования.
Шаг 1: Создание таблиц в базе данных
Ниже приведен пример создания двух таблиц с использованием SQL-запросов:
CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL );
CREATE TABLE orders ( id INT PRIMARY KEY AUTO_INCREMENT, user_id INT, product_name VARCHAR(50) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) );
В примере выше таблица «users» содержит поле «id» в качестве первичного ключа, а также поля «name» и «email». Таблица «orders» также содержит поле «id» в качестве первичного ключа, а также поле «user_id», которое является внешним ключом, ссылается на поле «id» из таблицы «users». При создании таблицы «orders» мы используем ключевое слово «FOREIGN KEY», чтобы определить внешний ключ и связь с таблицей «users».
Создание таблиц — важный шаг при использовании foreign key в MySQL, так как они определяют структуру базы данных и связи между таблицами.
Как создать первичные и внешние ключи MySQL
В этой инструкции рассказываем про первичный и внешний ключи SQL, зачем они нужны и как их создать различными способами.
Эта инструкция — часть курса «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)); Создание первичного ключа при создании таблицы и с помощью ALTER TABLE
В созданной выше таблице ни одно из полей не является первичным ключом. Увидеть это мы можем, выполнив команду:
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. Также мы рассмотрели несколько сценариев использования внешнего ключа для управления таблицами.
Как установить и использовать MySQL Workbench