Что такое primary key sql

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the «ID» column when the «Persons» table is created:

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
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

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)
);

Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the «ID» column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been declared to not contain NULL values (when the table was first created).

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Persons
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

Зачем нужен 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) – это поле, которое являетс ядентификатором для каждой отдельной записи в таблице базы данных (далее – БД). Первичный клдюч обязательно должен быть NOT NULL.

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

Если таблица имеет первичный ключ, прикреплённый к любому полю, то мы не можем иметь две записи с одинкаовым значением данного поля. Другими словами, в каждой таблице первичный ключ – это уникальное значение.

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

 CREATE TABLE developers( ID INT NOT NULL, NAME VARCHAR (100) NOT NULL, SPECIALTY VARCHAR(100) NOT NULL, SALARY INT NOT NULL, PRIMARY KEY (ID) ); 

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

 ALTER TABLE developers ADD PRIMARY KEY (ID); 

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

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

 CREATE TABLE developers( ID INT NOT NULL, NAME VARCHAR (100) NOT NULL, SPECIALTY VARCHAR(100) NOT NULL, SALARY INT NOT NULL, PRIMARY KEY (ID, NAME) ); 

Для создания констрейнта PRIMARY KEY для полей ID и NAMES в уже существующей таблице для RDBMS MySQL, мы должны использовать такую команду:

 ALTER TABLE developers ADD CONSTRAINT PK_DEVELOPER_ID PRIMARY KEY (ID, NAME); 

Для удаление констрейнта PRIMARY KEY в той же RDBMS испольуется следующая команда:

 ALTER TABLE developers DROP PRIMARY KEY ; 

На этом мы заканчиваем изучение констрейнта PRIMARY KEY.

Полезности

Туториалы
Системный дизайн
Собеседования
Студенты
Задачи

Немного о себе

Приветствую! Меня зовут Евгений. На этом сайте я пишу о разработке программного обеспечения. Связаться со мной вы можете по email: proselytear@yahoo.com Имеет смысл, предварительно ознакомиться вот с этим FAQ разделом.

Недавние публикации

• Механизмы CAS и FAA глазами Java разработчика
• ExecutorService в Java и примеры его применения.
• Особенности работы PreparedStatement в JDBC
• Основы кэширования в Hibernate
• Феномены чтения глазами разработчика

Copyright © 2023 PROSELYTE.

Omega WordPress Theme by ThemeHall

Первичные ключи

Первичный ключ (primary key) представляет собой один из примеров уникальных индексов и применяется для уникальной идентификации записей таблицы. Никакие из двух записей таблицы не могут иметь одинаковых значений первичного ключа. Первичный ключ обычно сокращенно обозначают как PK (primary key).

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

К примеру, в базе данных Forum таблицы themes и posts связаны между собой следующим образом:

Первичным ключом таблицы themes является id_theme, а таблицы posts — id_post. Обратите внимание, что поле id_theme присутствует и в таблице posts. Каждое значение этого поля в таблице posts является внешним ключом (в данном случае это внешний ключ для первичного ключа таблицы themes). Внешний ключ сокращенно обозначают как FK (foreign key).Как видно из рис.1, внешний ключ ссылается на первичный ключ таблицы themes, устанавливая однозначную логическую связь между записями таблиц themes и posts. Иначе говоря, если внешний ключ для записи (сообщения) с PK=1 в таблице posts имеет значение внешнего ключа равное 1, то это значит, что это сообщение относится к теме с PK=1 таблицы themes.

Если Вам нужна частная профессиональная консультация от авторов многих книг Кузнецова М.В. и Симдянова И.В., добро пожаловать в наш Консультационный Центр SoftTime.