Первичный ключ таблицы базы данных
Примечание:
Во всех статьях текущей категории уроков по SQL используются примеры и задачи, основанные на учебной базе данных.
Приступая к изучению данного материала, рекомендуется ознакомиться с описанием учебной БД.
Каждая строка в таблицах реляционных баз данных должна отвечать требованию уникальности. Некоторые поля могут повторяться от записи к записи, но сочетание всех полей строки встречается только единожды. Если не соблюдать это правило, то можно ошибочно отнести свойства одного объекта к другому.
Допустим, существует таблица с перечнем студентов:
На примере видно, что 2 и 4 строки содержать ФИО студентов, являющихся полными тезками, которые учатся в одной группе. Такая ситуация маловероятна, но возможна. Если один из этих студентов не сдаст экзамены и его отчислят, то по ошибке можно отчислить другого, который не имеет проблем с успеваемостью.
Чтобы исключить подобные ошибки, потребуется добавить дополнительное свойство, которое потенциально может служить идентификатором: паспортные данные, номер личного дела и т.п.
На приведенном в качестве примера изображении, столбец «№ дела» однозначно определяет запись и называется первичным ключом. Он является простым, так как состоит из одного столбца.
В учебной базе данных имеется таблица «Сотрудники_Линии», в которой для каждого оператора определены подключенные телефонные линии.
Ни один из столбцов не может являться простым первичным ключом, потому что может повторяться (сотруднику подключается несколько линии, и одна линия подключаются разным сотрудникам). В таком случае первичным ключом служит пара столбцов – «Сотрудник» и «Линия». Телефонную линию нельзя подключить несколько раз одному и тому же оператору, что соответствует уникальности записей.
Первичный ключ, состоящий из нескольких полей, называется составным ключом.
Первичный ключ и внешний ключ таблиц реляционных баз данных
Вступление
В прошлой статье (устройство реляционной БД) мы разбирали, как устроена реляционная (табличная) база данных и выяснили, что основными элементами реляционной базы данных являются: таблицы, столбцы и строки, а в математических понятиях: отношения, атрибуты и кортежи. Также часто, строки называют записями, столбцы называют колонками, а пересечение записи и колонки называют ячейкой.
Важно вспомнить, что содержание строки и названия столбцов должны быть уникальны в пределах одной базы данных.
Типы данных в базах
Важно понимать, что можно создавать базы для любых типов данных: текстов, дат, времени, событий, цифр. В зависимости от типа информации реляционные базы данных делят на типы. Каждый тип данных (атрибут) имеет свое обозначение:
Это основные типы данных, которых на самом деле гораздо больше. Причем, каждый язык программирования имеет свой набор системных атрибутов (типов данных).
Что такое первичный ключ и внешний ключ таблиц реляционных баз данных
Первичный ключ
Выше мы вспоминали: каждая строка (запись) БД должна быть уникальна. Именно первичный ключ в виде наборов определенных значений, максимально идентифицируют каждую запись. Можно определить по-другому. Первичный ключ: набор определенных признаков, уникальных для каждой записи. Обозначается первичный ключ, как primary key.
Primary key (PK) очень важен для каждой таблицы. Поясню почему.
На логической связи между таблицами, стоит остановиться подробнее.
Ключ внешний
Foreign key, кратко FK. Обеспечивает однозначную логическую связь, между таблицами одной БД.
Например, есть две таблицы А и В. В таблице А (обувь), есть первичный ключ: размер, в таблице В (цвет) должна быть колонка с названием размер. В этой таблице «размер» это и будет внешний ключ для логической связи таблиц В и А.
Более сложный пример.
Две таблицы данных: Люди и Номера телефонов.
Таблица: Люди
| primary key | Имя |
| 1 | Зайцев |
| 2 | Белкин |
| 3 | Волков |
Таблица: Номера телефонов
| primary key | телефон | foreign key |
| 1 | 12345 | 1 |
| 2 | 54321 | 1 |
| 3 | 678910 | 2 |
| 4 | 109876 | 3 |
| 5 | 13579 | 3 |
В таблице Номера телефонов PK уникален. FK этой таблицы является PK таблицы Люди. Связь между номерами телефонов и людьми обеспечивает FK таблицы телефонов. То есть:
В завершении добавлю, что любая СУБД, управляющая базой данных, имеет технические возможности составить первичный ключ.
Как узнать первичный ключ таблицы
InterBase – это реляционная СУБД. Помимо всего прочего, это означает, что все данные в InterBase хранятся в виде таблиц. Таблица, как ее понимают с точки зрения SQL, очень похожа на обычную таблицу, которую можно нарисовать от руки на листе бумаги или создать в программе вроде Microsoft Excel. У таблиц InterBase имеются столбцы и строки, в которых размещаются данные. Таблица обязательно имеет имя, уникальное в пределах одной базы данных. Таблицы являются основным хранилищем информации в базе данных, и поэтому необходимо ответственно отнестись к созданию таблиц. Создание таблиц осуществляется командой CREATE TABLE.
Команда CREATE TABLE имеет следующий синтаксис:
Каждый столбец в таблице должен иметь имя, уникальное в пределах данной таблицы, а также либо тип данных, определяемый предложением datatype, либо выражение ( для вычисления значения столбца (для вычисляемых столбцов), либо домен, определяемый domain.
Опция [DEFAULT < literal | NULL | USER>] позволяет задать значение столбца по умолчанию. Это очень удобная возможность для автоматического заполнения данных. Существует 3 возможности задавать значения по умолчанию:
Для некоторых полей совершенно необходимо, чтобы поле имело какое-то непустое значение. Например, у студента обязательно есть номер курса.
Бывают случаи, когда необходимо изменить уже существующую таблицу. Конечно, мы можем пересоздать таблицу целиком. Для этого следует выполнить команду удаления таблицы и вновь создать ее.
Синтаксис команды DROP TABLE (удаление таблицы):
Но такой способ изменения таблиц имеет значительные недостатки. При удалении таблицы с помощью команды DROP все данные, которые существовали в таблице, уничтожаются, и, чтобы их не потерять, приходится копировать их во временные таблицы. Это весьма хлопотно. Поэтому для легкого изменения структуры таблиц существует команда ALTER TABLE, которая позволяет добавлять новые поля, удалять существующие, а также добавлять/удалять ограничения ссылочной целостности.
Синтаксис команды ALTER TABLE (изменение таблицы):
| DROP col | DROP CONSTRAINT constraint
| TYPE new_col_datatype | POSITION new_col_position>>
Первичные ключи в таблицах
Наличие ключей – важнейший элемент нормализации.
Первичный ключ – это одно или более полей в таблице, однозначно идентифицирующих записи в пределах этой таблицы. Иными словами, в таблице не должно быть двух различных строк, имеющих однинаковое значение первичного ключа. Кроме того, столбец, включённый в состав первичного ключа, не может иметь пустого (NULL) значения.
Абсолютное большинство таблиц в реляционной базе данных обязательно имеют первичный ключ (часто пишут РК – сокращенно от Primary key). Общей рекомендацией при создании таблиц является создавать первичный ключ. Создать первичный ключ можно как при создании таблицы, так и позже.
Можно выбрать в качестве первичного ключа столбец или группу столбцов таблицы. Здесь следует исходить из того, что размер первичного ключа не должен быть слишком большим. Во-первых, для первичного ключа в базе данных автоматически создаётся индекс, размер которого зависит от размера первичного ключа и количества строк в таблице, и, во-вторых, часто на первичный ключ данной таблицы ссылаются внешние ключи других, подчинённых, таблиц (о внешних ключах речь пойдёт позже). При большом размере первичного ключа размер внешней памяти, выделяемый для базы данных, может быть очень большим. Это также приведёт к увеличению времени реакции системы.
Генераторы – удобная основа для первичных ключей
Несколько слов о реализации первичного ключа: т.к. он предназначен для обеспечения уникальности, то никакие две записи в одной таблице не могут иметь одинаковых значений этого ключа. То есть, чтобы удовлетворить этому условию, при занесении новой записи в таблицу InterBase должен просмотреть все записи в таблице и выяснить, нет ли уже таких значений в таблице. Для быстрого поиска в InterBase существует механизм индексов – специальных объектов InterBase, которые позволяют очень быстро найти запись в таблице. Поэтому при создании и удалении первичного ключа создается индекс на то поле (или поля), которое входит в первичный ключ.
На практике самым распространенным видом ключа является счетчик – целочисленное поле, которое содержит увеличивающиеся значения. Некоторые СУБД, такие, как Paradox и MS SQL, имеют специальные тип – счетчик (auto increment). При добавлении в таблицу новой записи значение поля с этим типом автоматически увеличивается на величину приращения, обычно на единицы. В InterBase нет поля типа счетчик, однако есть возможность реализовать подобное поведение. Для создания поля, которое бы заполнялось автоматически при добавлении записи в таблицу, используется совокупность средств: первым из них является генератор.
Генератор – это именованный счетчик. Внутри базы данных мы можем создать счетчик, дать ему уникальное имя в пределах этой базы и управлять значениями этого счетчика. Это и будет генератор.
Синтаксис команды создания генератора:
Чтобы получать и изменять значения генераторов, существует встроенная в InterBase функция GEN_ID. Эта функция принимает в качестве параметров имя генератора и величину приращения, которую нужно применить к данному генератору, полученному в результате прибавления к нему приращения.
Формирование первичного ключа на стороне сервера – вообще исключает всякую заботу на стороне клиента о том, каково будет значение первичного ключа. В этом случае при вставке записи срабатывает триггера – специальный объект базы данных, который может осуществлять какие-либо действия при вставке\удалении\изменении записей в таблицах. И в этом триггере происходит вызов функции GEN_ID, получение нужного значения генератора и вставка его в таблицу.
Sysadminium
База знаний системного администратора
Первичный и внешний ключ SQL
Из статьи вы узнаете, что такое первичный и внешний ключ в SQL. Зачем они нужны и как их использовать. Я покажу на практике как их использовать в PostgreSQL.
Теория
Первичный ключ это одно или несколько полей в таблице. Он необходим для уникальной идентификации любой строки. Первичный ключ накладывает некоторые ограничения:
К первичному ключу предъявляют следующее требование:
Первичный ключ может быть:
Я сам не имею большого опыта работы с SQL, но в книгах пишут что лучше использовать естественный первичный ключ. Почему именно так, я пока ответить не смогу.
Связь между таблицами
Первостепенная задача первичного ключа – это уникальная идентификация каждой строки. Но первичный ключ может решить ещё одну задачу. В базе данных есть возможность связывания нескольких таблиц. Для такой связи используют первичный и внешний ключ sql. В одной из таблиц создают внешний ключ, который ссылается на поля другой таблицы. Но внешний ключ не может ссылаться на любые поля другой таблицы, а может ссылаться только на определённые:
Например, у вас есть таблица “Ученики” (pupils) и выглядит она следующим образом:
| ФИО full_name | Возраст age | Класс class |
| Иванов Иван Иванович | 15 | 9А |
| Сумкин Фёдор Андреевич | 15 | 9А |
| Петров Алексей Николаевич | 14 | 8Б |
| Булгаков Александр Геннадьевич | 14 | 8Б |
Таблица pupils
И есть таблица “Успеваемость” (evaluations):
| Предмет item | ФИО full_name | Оценка evaluation |
| Русский язык | Иванов Иван Иванович | 4 |
| Русский язык | Петров Алексей Николаевич | 5 |
| Математика | Булгаков Александр Геннадьевич | 3 |
| Литература | Сумкин Фёдор Андреевич | 5 |
Таблица evaluations
В обоих таблицах есть одинаковое поле: ФИО. При этом в таблице “Успеваемость” не может содержаться ФИО, которого нет в таблице “ Ученики“. Ведь нельзя поставить ученику оценку, которого не существует.
Первичным ключом в нашем случае может выступать поле “ФИО” в таблице “ Ученики“. А внешним ключом будет “ФИО” в таблице “Успеваемость“. При этом, если мы удаляем запись о каком-то ученике из таблицы “Ученики“, то все его оценки тоже должны удалиться из таблицы “Успеваемость“.
Ещё стоит заметить что первичный ключ в PostgreSQL автоматически создает индекс. Индекс ускоряет доступ к строкам таблицы и накладывает ограничение на уникальность. То есть двух Ивановых Иванов Ивановичей у нас не может существовать. Чтобы это обойти можно использовать:
Теперь давайте попробуем создать эти две таблички и попробуем с ними поработать.
Практика
Создадим базу данных school и подключимся к ней. Затем создадим таблицу pupils. Про создание таблиц я уже писал тут, а про типы данных тут. Затем посмотрим на табличку с помощью команды \d:
Как вы могли заметить, первичный ключ создаётся с помощью конструкции PRIMARY KEY (имя_поля) в момент создания таблицы.
Вывод команды \d нам показал, что у нас в таблице есть первичный ключ. А также первичный ключ сделал два ограничения:
Индекс в свою очередь наложил ещё одно ограничение – записи в поле full_name должны быть уникальны.
Следующим шагом создадим таблицу evaluations:
В этом случае из вывода команды \d вы увидите, что создался внешний ключ (Foreign-key), который относится к полю full_name и ссылается на таблицу pupils.
Внешний ключ создается с помощью конструкции FOREIGN KEY (имя_поля) REFERENCES таблица_на_которую_ссылаются.
Создавая внешний ключ мы дополнительно указали опцию ON DELETE CASCADE. Это означает, что при удалении строки с определённым учеником в таблице pupils, все строки связанные с этим учеником удалятся и в таблице evaluations автоматически.
Заполнение таблиц и работа с ними
Заполним таблицу “pupils“:
Заполним таблицу “evaluations“:
А теперь попробуем поставить оценку не существующему ученику:
Как видите, мы получили ошибку. Вставлять (insert) или изменять (update) в таблице evaluations, в поле full_name можно только те значения, которые есть в этом же поле в таблице pupils.
Теперь удалим какого-нибудь ученика из таблицы pupils:
И посмотрим на строки в таблице evaluations:
Как видно, строка с full_name равная ‘Иванов Иван Иванович’ тоже удалилась. Если бы у Иванова было бы больше оценок, они всё равно бы все удалились. За это, если помните отвечает опция ON DELETE CASCADE.
Попробуем теперь создать ученика с точно таким-же ФИО, как у одного из существующих:
Ничего не вышло, так как такая запись уже существует в поле full_name, а это поле у нас имеет индекс. Значит значения в нём должны быть уникальные.
Составной первичный ключ
Есть большая вероятность, что в одной школе будут учиться два ученика с одинаковым ФИО. Но меньше вероятности что эти два ученика будут учиться в одном классе. Поэтому в качестве первичного ключа мы можем взять два поля, например full_name и class.
Давайте удалим наши таблички и создадим их заново, но теперь создадим их используя составной первичный ключ:
Как вы могли заметить, разница не большая. Мы должны в PRIMARY KEY указать два поля вместо одного. И в FOREIGN KEY точно также указать два поля вместо одного. Ну и не забудьте в таблице evaluations при создании добавить поле class, так как его там в предыдущем варианте не было.
Теперь посмотрим на структуры этих таблиц:
Первичный ключ в таблице pupils уже состоит из двух полей, поэтому внешний ключ ссылается на эти два поля.
Теперь мы можем учеников с одинаковым ФИО вбить в нашу базу данных, но при условии что они будут учиться в разных классах:
И также по второй таблице:
Удаление таблиц
Кстати, удалить таблицу, на которую ссылается другая таблица вы не сможете:
Поэтому удалим наши таблицы в следующем порядке:
Либо мы могли удалить каскадно таблицу pupils вместе с внешним ключом у таблицы evaluations:
Как видно из примера, после каскадного удаления у нас вместе с таблицей pupils удался внешний ключ в таблице evaluations.
Создание связи в уже существующих таблицах
Выше я постоянно создавал первичный и внешний ключи при создании таблицы. Но их можно создавать и для существующих таблиц.
Вначале удалим оставшуюся таблицу:
И сделаем таблицы без ключей:
Теперь создадим первичный ключ в таблице pupils:
И создадим внешний ключ в таблице evaluations:
Посмотрим что у нас получилось:
В этой статье я рассказал про первичный и внешний ключ sql. А также продемонстрировал, как можно создать связанные между собой таблицы и как создать связь между уже существующими таблицами. Вы узнали, какие ограничения накладывает первичный ключ и какие задачи он решает. И вдобавок, какие требования предъявляются к нему. Вместе с тем я показал вам как работать с составным первичным ключом.
Дополнительно про первичный и внешний ключ sql можете почитать тут.
Создание первичных ключей
Определить первичный ключ в SQL Server можно с помощью среды SQL Server Management Studio или Transact-SQL. Создание первичного ключа автоматически приводит к созданию соответствующего уникального кластеризованного индекса (или некластеризованного при наличии такого указания).
Перед началом
Ограничения
В таблице возможно наличие только одного ограничения по первичному ключу.
Все столбцы с ограничением PRIMARY KEY должны иметь признак NOT NULL. Если допустимость значения NULL не указана, то для всех столбцов c ограничением PRIMARY KEY устанавливается признак NOT NULL.
безопасность
Permissions
Создание новой таблицы с первичным ключом требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.
Создание первичного ключа в существующей таблице требует разрешения ALTER на таблицу.
Использование среды SQL Server Management Studio
Создание первичного ключа
Чтобы переопределить первичный ключ, необходимо удалить все связи с существующим первичным ключом и только после этого создавать новый первичный ключ. Появится сообщение, предупреждающее об автоматическом удалении в ходе процесса всех существующих связей.
Ключевой столбец-источник идентифицируется символом первичного ключа в соответствующем селекторе строк.
Если первичный ключ состоит более чем из одного столбца, то в одном столбце могут встречаться дублирующиеся значения, но все сочетания значений изо всех столбцов первичного ключа должны быть уникальными.
При определении составного ключа порядок столбцов в первичном ключе совпадает с порядком столбцов, показанным в таблице. Однако после создания первичного ключа порядок столбцов можно изменить. Дополнительные сведения см. в разделе Изменение первичных ключей.
Использование Transact-SQL
Создание первичного ключа в существующей таблице
В следующем примере создается первичный ключ для столбца TransactionID в базе данных AdventureWorks.
Создание первичного ключа в новой таблице
В следующем примере создается таблица и определяется первичный ключ для столбца TransactionID в базе данных AdventureWorks.
Создание первичного ключа с кластеризованным индексом в новой таблице
В следующем примере создается таблица и определяется первичный ключ для столбца CustomerID и кластеризованного индекса для TransactionID в базе данных AdventureWorks.
