PRIMARY KEY в SQL
PRIMARY KEY — первичный ключ, ограничение, позволяющее однозначно идентифицировать каждую запись в таблице SQL. PRIMARY KEY OracleПервичный Ключ (PRIMARY KEY) может ограничивать таблицы или их столбцы. Это ограничение работает так же как и ограничение UNIQUE. Но следует учитывать различие между первичными ключами и уникальностью столбцов в способе их использования с внешними ключами. Первичные ключи не могут позволять значений NULL. Это означает что, подобно полям в ограничении UNIQUE, любое поле, используемое в ограничении PRIMARY KEY, должно уже быть обьявлено NOT NULL. PRIMARY KEY Oracle. Пример №1. Пример создания таблицы SQL с ограничением PRIMARY KEY:
CREATE TABLE Student ( Kod_stud integer NOT NULL PRIMARY KEY, Fam char(30) NOT NULL UNIQUE, Adres char(50), Ball decimal);
Лучше всего помещать ограничение PRIMARY KEY в поле (или в поля), которое будет образовывать уникальный идентификатор строки, и сохранить ограничение UNIQUE для полей которые должны быть уникальными логически (такие как номера телефона или поле sname), а не для идентификации строк. Ограничение PRIMARY KEY может также быть применено для многочисленных полей, составляющих уникальную комбинацию значений: PRIMARY KEY Oracle. Пример №2.
CREATE TABLE Student ( Fam char (30) NOT NULL, Im char (30) NOT NULL Adres char (50), PRIMARY KEY (Fam, Im));
PRIMARY KEY MySQL
PRIMARY KEY SQL / MySQL. Пример №3.
CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id));
PRIMARY KEY SQL / MySQL. Пример №4.
CREATE TABLE `ad_packages` ( `id` int(111) NOT NULL auto_increment, `title` varchar(132) NOT NULL default », `price` float NOT NULL default ‘0’, `type` varchar(22) NOT NULL default », `c_type` enum(‘cash’,’points’,’rur’) NOT NULL default ‘cash’, PRIMARY KEY (`id`) );
PRIMARY KEY SQL / MySQL. Пример №5.
CREATE TABLE `gamestat` ( `id` int(11) NOT NULL auto_increment, `game` varchar(10) NOT NULL default ‘tuz’, `stavok` int(11) NOT NULL default ‘0’, `usd` float NOT NULL default ‘0’, `rur` float NOT NULL default ‘0’, `point` float NOT NULL default ‘0’, `bank_usd` decimal(12,2) NOT NULL default ‘0.00’, `bank_rur` decimal(12,2) NOT NULL default ‘0.00’, `bank_point` decimal(12,2) NOT NULL default ‘0.00’, PRIMARY KEY (`id`) );
Как создать первичные и внешние ключи 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
Зачем нужен PRIMARY KEY и FOREIGN KEY (ключи)?
Разумеется, я читал основы SQL, но они не объясняют зачем нужны эти ключи в работе БД. Первичный ключ ID содержит уникальное значение по которому можно однозначно идентифицировать запись. Тут первичный ключ ID. Внешний ключ dept_name. Зачем мы пишем перед полем PRIMARY KEY т.е. указываем что это поле первичный ключ? Почему нельзя сделать это поле просто автоинкрементируемым? И будет уникальное поле. Аналогично — зачем мы пишем перед полем dept_name FOREIGN KEY? То есть мы говорим, что поле dept_name указывает на поле dept_name в другой таблице department. Что это дает? Я могу не указывать FOREIGN KEY (dept_name) REFERENCES department(dept_name) при создании таблицы. Просто запомнить, что например JOIN по этим полям. Что делают эти команды? Зачем они нужны?
Отслеживать
задан 15 дек 2022 в 21:57
195 2 2 серебряных знака 10 10 бронзовых знаков
О какой СУБД речь?
15 дек 2022 в 22:02
Автоинкрементируемое поле можно изменить UPDATE-запросом, и есть риск, что оно перестанет быть уникальным. PRIMARY KEY не позволит изменить его на уникальное значение
15 дек 2022 в 22:04
FOREIGN KEY запретит удаление строки́ из department, если в instructor ещё есть стро́ки, которые ссылаются на строку, которую пытались удалить
15 дек 2022 в 22:05
@andreymal Или удалять все записи по ключу из таблицы instructor. В то же время запретят вставку записей в instructor если в department нет соответствующего ключа
15 дек 2022 в 22:09
Почему нельзя сделать это поле просто автоинкрементируемым? И будет уникальное поле. Можно. Только в этом случае ничто не мешает сунуть в это поле NULL. в сотню записей.
16 дек 2022 в 7:17
5 ответов 5
Сортировка: Сброс на вариант по умолчанию
Либо плохо читали, либо читали что-то не то. По пунктам.
PRIMARY KEY. Как выше уже сказали, identity-поле вовсе не гарантирует уникальность значения. Пример ниже — для MS SQL. Создаем таблицу, и добавляем в неё 1 строку:
use tempdb go create table dbo.pk_test ( id int identity not null, name varchar(1) ) go insert into dbo.pk_test(name) values('A'); select id, name from dbo.pk_test; go id name ----------- ---- 1 A (1 rows affected) и вставляем ещё одну с таким же id:
begin tran; set xact_abort on; set identity_insert dbo.pk_test on; insert into dbo.pk_test(id, name) values(1, 'B'); set identity_insert dbo.pk_test off; select id, name from dbo.pk_test; go id name ----------- ---- 1 A 1 B (2 rows affected) – никаких ошибок. Откатываем вставку, вешаем на поле id PRIMARY KEY:
rollback go alter table dbo.pk_test add constraint pk_pk_test primary key(id); go select id, name from dbo.pk_test; go id name ----------- ---- 1 A (1 rows affected) и снова пытаемся вставить дубль id:
begin tran; set xact_abort on; set identity_insert dbo.pk_test on; insert into dbo.pk_test(id, name) values(1, 'B'); go Violation of PRIMARY KEY constraint 'pk_pk_test'. Cannot insert duplicate key in object 'dbo.pk_test'. – получаем ошибку. А в некоторых БД identity-поля отсутствуют вообще — например, в оракле до версии 12c. Вместо них используются генераторы последовательностей (sequence), и для вставки неуникального значения в поле не нужно никаких ухищрений типа set identity_insert. И ещё нюанс PRIMARY/UNIQUE constraints: по сути, это логические ограничения, ограничения бизнес-модели. На физическом уровне эти ограничения всегда реализуются уникальными индексами по соответствующим полям.
FOREIGN KEY: создаем и заполняем тестовые таблицы:
use tempdb go create table dbo.fk_source ( id int not null primary key ) go create table dbo.fk_target ( fk_id int not null, constraint fk_target_source foreign key(fk_id) references dbo.fk_source(id) on update cascade on delete no action ) go insert into dbo.fk_source(id) values(1); insert into dbo.fk_target(fk_id) values(1); go select id from dbo.fk_source; go id ----------- 1 (1 rows affected) select fk_id from dbo.fk_target; go fk_id ----------- 1 (1 rows affected) теперь в таблице, на которую ссылается FK, меняем значение поля с FK:
update dbo.fk_source set where rows affected) select fk_id from dbo.fk_target go fk_id ----------- 2 (1 rows affected) – из-за включенной опции каскадного обновления в связанной таблице значение поля обновилось автоматически. Пытаемся удалить запись из таблицы-источника:
delete dbo.fk_source where 547, Level 16, State 1, Server ., Line 1 The DELETE statement conflicted with the REFERENCE constraint "fk_target_source". The conflict occurred in database "tempdb", table "dbo.fk_target", column 'fk_id'. The statement has been terminated. – FK не позволяет этого сделать. Пытаемся в таблицу-приёмник вставить не существующее в таблице-источнике значение:
insert into dbo.fk_target(fk_id) values(3); go Msg 547, Level 16, State 1, Server ., Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "fk_target_source". The conflict occurred in database "tempdb", table "dbo.fk_source", column 'id'. The statement has been terminated. – FK не позволяет этого сделать. Пытаемся очистить всю таблицу-источник, и вообще удалить её:
truncate table dbo.fk_source; go Msg 4712, Level 16, State 1, Server ., Line 1 Cannot truncate table 'dbo.fk_source' because it is being referenced by a FOREIGN KEY constraint. drop table dbo.fk_source; go Msg 3726, Level 16, State 1, Server ., Line 1 Could not drop object 'dbo.fk_source' because it is referenced by a FOREIGN KEY constraint. – FK не позволяет этого сделать. А теперь удаляем FK:
alter table dbo.fk_target drop constraint fk_target_source go – и становится можно всё:
insert into dbo.fk_target(fk_id) values(3); go (1 rows affected) delete dbo.fk_source where rows affected) truncate table dbo.fk_source; go drop table dbo.fk_source; go Отслеживать
ответ дан 16 дек 2022 в 4:26
user532595 user532595
Помимо вышеуказанных причин приведу ещё одну: PRIMARY KEY и FOREIGN KEY зачастую индексируются. Это приводит к тому, что обращение по ним будет происходить быстрее.
Пример на MySql:
Создадим таблицу и заполним её большим числом данных:
CREATE TABLE `table_test_1` ( `field_1` INT NOT NULL AUTO_INCREMENT, `field_2` INT NOT NULL DEFAULT 0, `field_3` VARCHAR(255) NOT NULL, PRIMARY KEY (`field_1`) ); INSERT INTO `table_test_1` (`field_3`) VALUES ('a1'),('a2'),('a3'); INSERT INTO `table_test_1` (`field_3`) VALUES ('b1'),('b2'),('b3'); INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; INSERT INTO `table_test_1` (`field_3`) SELECT `field_3` FROM `table_test_1`; UPDATE `table_test_1` SET `field_2` = `field_1`; Теперь у нас есть таблица с первичным ключом field_1 и аналогичным ему значением field_2 . Теперь сравним выборки по первичному ключу:
SELECT * FROM `table_test_1` WHERE `field_1` = 555555; +---------+---------+---------+ | field_1 | field_2 | field_3 | +---------+---------+---------+ | 555555 | 555555 | b2 | +---------+---------+---------+ 1 row in set (0.00 sec) SELECT * FROM `table_test_1` WHERE `field_2` = 555555; +---------+---------+---------+ | field_1 | field_2 | field_3 | +---------+---------+---------+ | 555555 | 555555 | b2 | +---------+---------+---------+ 1 row in set (0.36 sec) Теперь создадим вторую таблицу:
CREATE TABLE `table_test_2` ( `field_1` INT NOT NULL AUTO_INCREMENT, `field_2` INT NOT NULL DEFAULT 0, `field_3` INT NULL DEFAULT NULL, PRIMARY KEY (`field_1`), CONSTRAINT `table_test_2_fk0` FOREIGN KEY (`field_3`) REFERENCES `table_test_1` (`field_1`) ON UPDATE RESTRICT ON DELETE RESTRICT ); INSERT INTO `table_test_2` (`field_1`, `field_2`, `field_3`) SELECT `field_1`, `field_1` FROM `table_test_1`; Здесь у нас есть внешний ключ field_3 и обычное поле field_2 , значения которых содержит значения field_1 (и соответственно field_2 ) из первой таблицы.
Попробуем INNER JOIN запрос на основе внешнего ключа:
SELECT * FROM `table_test_2` INNER JOIN `table_test_1` ON `table_test_2`.`field_3` = `table_test_1`.`field_1` LIMIT 1 OFFSET 55555; +---------+---------+---------+---------+---------+---------+ | field_1 | field_2 | field_3 | field_1 | field_2 | field_3 | +---------+---------+---------+---------+---------+---------+ | 71925 | 71925 | 71925 | 71925 | 71925 | a2 | +---------+---------+---------+---------+---------+---------+ 1 row in set (0.28 sec) А вот запрос на основе обычных значений:
SELECT * FROM `table_test_2` INNER JOIN `table_test_1` ON `table_test_2`.`field_2` = `table_test_1`.`field_2` LIMIT 1 OFFSET 55555; +---------+---------+---------+---------+---------+---------+ | field_1 | field_2 | field_3 | field_1 | field_2 | field_3 | +---------+---------+---------+---------+---------+---------+ | 71925 | 71925 | 71925 | 71925 | 71925 | a2 | +---------+---------+---------+---------+---------+---------+ 1 row in set (0.51 sec) Возможно эти примеры не самые показательные, но разница заметна уже на них, а для сложных структур данных и для более сложных запросов разница между индексируемыми значениями и неиндексируемыми становится критически важной.
SQL PRIMARY KEY
Ограничение PRIMARY KEY однозначно идентифицирует каждую запись в таблице.
Первичные ключи должны содержать уникальные значения и не могут содержать нулевые значения.
Таблица может иметь только один первичный ключ, а в таблице этот первичный ключ может состоять из одного или нескольких столбцов (полей).
PRIMARY KEY в CREATE TABLE
Следующий SQL создает первичный ключ о «ID» в столбик, когда таблица «Persons» создается:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
Чтобы разрешить именование ограничения первичного ключа и определить ограничение первичного ключа для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Примечание: В приведенном выше примере существует только один первичный ключ (PK_Person). Однако значение первичного ключа состоит из двух столбцов (ID + LastName).
PRIMARY KEY в ALTER TABLE
Чтобы создать ограничение первичного ключа для столбца «ID», когда таблица уже создана, используйте следующий SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
Чтобы разрешить именование ограничения первичного ключа и определить ограничение первичного ключа для нескольких столбцов, используйте следующий синтаксис SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Примечание: Если вы используете инструкцию ALTER TABLE для добавления первичного ключа, то столбец(ы) первичного ключа должен(ы) быть уже объявлено, что они не содержат нулевых значений (при первом создании таблицы).
Ограничение PRIMARY KEY с DROP
Чтобы удалить ограничение первичного ключа, используйте следующий SQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
Мы только что запустили
SchoolsW3 видео
курс сегодня!
Сообщить об ошибке
Если вы хотите сообщить об ошибке или внести предложение, не стесняйтесь отправлять на электронное письмо:
Ваше предложение:
Спасибо Вам за то, что помогаете!
Ваше сообщение было отправлено в SchoolsW3.
Schoolsw3 оптимизирован для бесплатного обучения, проверки и подготовки знаний. Примеры в редакторе упрощают и улучшают чтение и базовое понимание. Учебники, ссылки, примеры постоянно пересматриваются, чтобы избежать ошибок, но не возможно гарантировать полную правильность всего содержания. Некоторые страницы сайта могут быть не переведены на РУССКИЙ язык, можно отправить страницу как ошибку, так же можете самостоятельно заняться переводом. Используя данный сайт, вы соглашаетесь прочитать и принять Условия к использованию, Cookies и политика конфиденциальности.