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

лМБУУЙЖЙЛБГЙС

рТПУФЩЕ Й УПУФБЧОЩЕ ЛМАЮЙ

рЕТЧЙЮОЩК ЛМАЮ НПЦЕФ УПУФПСФШ ЙЪ ЕДЙОУФЧЕООПЗП РПМС ФБВМЙГЩ, ЪОБЮЕОЙС ЛПФПТПЗП ХОЙЛБМШОЩ ДМС ЛБЦДПК ЪБРЙУЙ. фБЛ, ОБРТЙНЕТ, ОБ РТЕДРТЙСФЙЙ ОЕ НПЦЕФ ВЩФШ ДЧХИ ТБВПФОЙЛПЧ У ПДЙОБЛПЧЩНЙ ФБВЕМШОЩНЙ ОПНЕТБНЙ, РПЬФПНХ Ч ФБВМЙГЕ, УПДЕТЦБЭЕК ЪБРЙУЙ П ТБВПФОЙЛБИ, ФБВЕМШОЩК ОПНЕТ НПЦЕФ ВЩФШ РЕТЧЙЮОЩН ЛМАЮПН. фБЛПК РЕТЧЙЮОЩК ЛМАЮ ОБЪЩЧБАФ РТПУФЩН ЛМАЮПН.

еУМЙ ФБВМЙГБ ОЕ ЙНЕЕФ ЕДЙОУФЧЕООПЗП ХОЙЛБМШОПЗП РПМС, РЕТЧЙЮОЩК ЛМАЮ НПЦЕФ ВЩФШ УПУФБЧМЕО ЙЪ ОЕУЛПМШЛЙИ РПМЕК, УПЧПЛХРОПУФШ ЪОБЮЕОЙК ЛПФПТЩИ ЗБТБОФЙТХЕФ ХОЙЛБМШОПУФШ. фБЛ, ЙНС, ЖБНЙМЙС, ПФЮЕУФЧП, ОПНЕТ РБУРПТФБ, УЕТЙС РБУРПТФБ ОЕ НПЗХФ ВЩФШ РЕТЧЙЮОЩНЙ ЛМАЮБНЙ РП ПФДЕМШОПУФЙ, ФБЛ ЛБЛ НПЗХФ ПЛБЪБФШУС ПДЙОБЛПЧЩНЙ Х ДЧХИ Й ВПМЕЕ МАДЕК. оП ОЕ ВЩЧБЕФ ДЧХИ МЙЮОЩИ ДПЛХНЕОФПЧ ПДОПЗП ФЙРБ У ПДЙОБЛПЧЩНЙ УЕТЙЕК Й ОПНЕТПН. рПЬФПНХ Ч ФБВМЙГЕ, УПДЕТЦБЭЕК ЪБРЙУЙ П МАДСИ, РЕТЧЙЮОЩН ЛМАЮПН НПЦЕФ ВЩФШ ОБВПТ РПМЕК, УПУФПСЭЙК ЙЪ ФЙРБ МЙЮОПЗП ДПЛХНЕОФБ, ЕЗП УЕТЙЙ Й ОПНЕТБ. фБЛПК РЕТЧЙЮОЩК ЛМАЮ ОБЪЩЧБАФ УПУФБЧОЩН ЛМАЮПН (БОЗМ. compound key, composite key, concatenated key).

еУФЕУФЧЕООЩЕ Й УХТТПЗБФОЩЕ ЛМАЮЙ

рЕТЧЙЮОЩК ЛМАЮ НПЦЕФ УПУФПСФШ ЙЪ ЙОЖПТНБГЙПООЩИ РПМЕК ФБВМЙГЩ (ФП ЕУФШ РПМЕК, УПДЕТЦБЭЙИ РПМЕЪОХА ЙОЖПТНБГЙА ПВ ПРЙУЩЧБЕНЩИ ПВЯЕЛФБИ). фБЛПК РЕТЧЙЮОЩК ЛМАЮ ОБЪЩЧБАФ ЕУФЕУФЧЕООЩН ЛМАЮПН. фЕПТЕФЙЮЕУЛЙ, ЕУФЕУФЧЕООЩК ЛМАЮ ЧУЕЗДБ НПЦОП УЖПТНЙТПЧБФШ, Ч ЬФПН УМХЮБЕ НЩ РПМХЮЙН Ф. О. ЙОФЕММЕЛФХБМШОЩК ЛМАЮ. оБ РТБЛФЙЛЕ, ПДОБЛП, ЙУРПМШЪПЧБОЙЕ ЕУФЕУФЧЕООЩИ ЛМАЮЕК ОБФБМЛЙЧБЕФУС ОБ ПРТЕДЕМЈООЩЕ УМПЦОПУФЙ:

• оЙЪЛБС ЬЖЖЕЛФЙЧОПУФШ — еУФЕУФЧЕООЩК ЛМАЮ НПЦЕФ ВЩФШ ЧЕМЙЛ РП ТБЪНЕТХ (ПУПВЕООП ЛПЗДБ ПО УПУФБЧОПК), Й ЕЗП ЙУРПМШЪПЧБОЙЕ ПЛБЦЕФУС ФЕИОЙЮЕУЛЙ ОЕЬЖЖЕЛФЙЧОЩН (ЧЕДШ ЧП ЧУЕИ ФБВМЙГБИ, УЧСЪБООЩИ У ДБООПК, РПОБДПВЙФУС УПЪДБФШ РПМЕ ФПЗП ЦЕ ТБЪНЕТБ, ЮФПВЩ ИТБОЙФШ УУЩМЛЙ).
• оЕПВИПДЙНПУФШ ЛБУЛБДОЩИ ЙЪНЕОЕОЙК — рТЙ ЙЪНЕОЕОЙЙ ЪОБЮЕОЙС РПМС, ЧИПДСЭЕЗП Ч ЕУФЕУФЧЕООЩК ЛМАЮ, ПЛБЪЩЧБЕФУС ОЕПВИПДЙНЩН ЙЪНЕОЙФШ ЪОБЮЕОЙЕ РПМС ОЕ ФПМШЛП Ч ДБООПК ФБВМЙГЕ, ОП Й ЧП ЧУЕИ ФБВМЙГБИ, УЧСЪБООЩИ У ДБООПК, Ч РТПФЙЧОПН УМХЮБЕ ЧУЕ УУЩМЛЙ ОБ ДБООХА ЪБРЙУШ ПЛБЦХФУС ОЕЛПТТЕЛФОЩНЙ. ч УМПЦОЩИ ВБЪБИ ДБООЩИ ФБЛЙИ УЧСЪБООЩИ ФБВМЙГ НПЦЕФ ВЩФШ ПЮЕОШ НОПЗП, Й ЧУЕЗДБ ПУФБЈФУС ПРБУОПУФШ ХРХУФЙФШ ЙЪ ЧЙДХ ЛБЛХА-ФП ЙЪ ОЙИ. рТЙ ДПВБЧМЕОЙЙ ОПЧЩИ УЧСЪБООЩИ ФБВМЙГ РТЙИПДЙФУС ДПВБЧМСФШ УПЗМБУХАЭЙЕ ЙЪНЕОЕОЙС ЧП ЧУЕ НЕУФБ РТПЗТБНН, ЗДЕ РТБЧЙФУС ЙУИПДОБС ФБВМЙГБ.
• оЕУППФЧЕФУФЧЙЕ ТЕБМШОПУФЙ — хОЙЛБМШОПУФШ ЕУФЕУФЧЕООПЗП РЕТЧЙЮОПЗП ЛМАЮБ Ч ТЕБМШОЩИ вд ОЕ ЧУЕЗДБ УПВМАДБЕФУС. дПРХУФЙН, ОБРТЙНЕТ, ЮФП РЕТЧЙЮОЩК ЛМАЮ Ч ФБВМЙГЕ — ДБООЩЕ МЙЮОПЗП ДПЛХНЕОФБ. ч ФБЛХА ФБВМЙГХ ПЛБЦЕФУС ОЕЧПЪНПЦОЩН ЧОЕУФЙ ЮЕМПЧЕЛБ, П ДПЛХНЕОФБИ ЛПФПТПЗП ОЕФ ЙОЖПТНБГЙЙ Ч НПНЕОФ ДПВБЧМЕОЙС ЪБРЙУЙ, Б ОБ РТБЛФЙЛЕ ФБЛБС ОЕПВИПДЙНПУФШ НПЦЕФ ЧПЪОЙЛОХФШ.

чУМЕДУФЧЙЕ ЬФЙИ Й ДТХЗЙИ УППВТБЦЕОЙК Ч РТБЛФЙЛЕ РТПЕЛФЙТПЧБОЙС вд ЮБЭЕ ЙУРПМШЪХАФ Ф. О. УЙОФЕФЙЮЕУЛЙЕ (УХТТПЗБФОЩЕ) ЛМАЮЙ — ЙУЛХУУФЧЕООП УПЪДБООЩЕ ФЕИОЙЮЕУЛЙЕ ЛМАЮЕЧЩЕ РПМС, ОЕ ОЕУХЭЙЕ ЙОЖПТНБГЙЙ ПВ ПВЯЕЛФБИ.

нБФЕТЙБМ ЙЪ ЧЙЛЙРЕДЙЙ — УЧПВПДОПК ЬОГЙЛМПРЕДЙЙ
тБЪДЕМ: фЕПТЕФЙЮЕУЛЙЕ ПУОПЧЩ ВБЪ ДБООЩИ • ухвд

Связанные задачи

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

Задача	Раздел
Описывает, как создать первичный ключ.	Создание первичных ключей
Описывает, как удалить первичный ключ.	Удаление первичных ключей
Описывает, как изменить первичный ключ.	Изменение первичных ключей
Описывается создание связей внешнего ключа	Создание связей по внешнему ключу
Описывает, как изменить связи внешнего ключа.	Изменение связей по внешнему ключу
Описывает, как удалить связи внешнего ключа.	Удаление связей по внешнему ключу
Описывает, как просматривать свойства внешнего ключа.	Просмотр свойств внешнего ключа
Описывает, как отключить ограничения внешнего ключа для репликации.	Отключение ограничений внешнего ключа для репликации
Описывает, как отключить ограничения внешнего ключа на время выполнения инструкций INSERT и UPDATE.	Отключение ограничений внешнего ключа для инструкций INSERT и UPDATE

Вторичный ключ

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

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

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

Отношение один к одному

Пока что мы рассмотрели классическую связь, когда одной строке основной таблицы данных соответствует одна строка из связанной таблицы. Такая связь называется один ко многим. Но существуют и другие связи, и сейчас мы рассмотрим еще одну – один к одному, когда одна запись основной таблице связана с одной записью другой. Чтобы это реализовать, достаточно связать первичные ключи обеих таблиц. Так как первичные ключи не могут повторяться, то в обеих таблицах связанными могут быть только одна строка.

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

 
CREATE TABLE Names
(
 idName uniqueidentifier DEFAULT NEWID(), 
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (idName)
)

CREATE TABLE Phones
(
 idPhone uniqueidentifier DEFAULT NEWID(),
 vcPhone varchar(10), 
 CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
 CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
   REFERENCES Names (idName)
)

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

1.2.5. Первичный ключ

Мы уже достаточно много говорили про ключевые поля, но ни разу их не использовали. Самое интересное, что все работало. Это преимущество, а может недостаток базы данных Microsoft SQL Server и MS Access. В таблицах Paradox такой трюк не пройдет и без наличия ключевого поля таблица будет доступна только для чтения.

В какой-то степени ключи являются ограничениями, и их можно было рассматривать вместе с оператором CHECK, потому что объявление происходит схожим образом и даже используется оператор CONSTRAINT. Давайте посмотрим на этот процесс на примере. Для этого создадим таблицу из двух полей «guid» и «vcName». При этом поле «guid» устанавливается как первичный ключ:

CREATE TABLE Globally_Unique_Data
(
 guid uniqueidentifier DEFAULT NEWID(),
 vcName varchar(50),
 CONSTRAINT PK_guid PRIMARY KEY (Guid)
)

Самое вкусное здесь это строка CONSTRAINT. Как мы знаем, после этого ключевого слова идет название ограничения, и объявления ключа не является исключением. Для именования первичного ключа, я рекомендую использовать именование типа PK_имя, где имя – это имя поля, которое должно стать главным ключом. Сокращение PK происходит от Primary Key (первичный ключ).

После этого, вместо ключевого слова CHECK, которое мы использовали в ограничениях, стоит оператор PRIMARY KEY, Именно это указывает на то, что нам необходима не проверка, а первичный ключ. В скобках указывается одно, или несколько полей, которые будут составлять ключ.

Помните, что в ключевом поле не может быть одинакового значения у двух строк, в этом ограничение первичного ключа идентично ограничению уникальности. Это значит, что если сделать поле для хранения фамилии первичным ключом, то в такую таблицу нельзя будет записать двух Ивановых с разными именами. Это нарушает ограничение первичного ключа. Именно поэтому ключи являются ограничениями и объявляются также как и ограничение CHECK. Но это не верно только для первичных ключей и вторичных с уникальностью.

В данном примере, в качестве первичного ключа выступает поле типа uniqueidentifier (GUID). Значение по умолчанию для этого поля – результат выполнения серверной процедуры NEWID.

Внимание

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

Для простоты примеров, в качестве ключа желательно использовать численный тип и если позволяет база данных, то будет лучше, если он будет типа «autoincrement» (автоматически увеличивающееся/уменьшающееся число). В MS SQL Server таким полем является IDENTITY, а в MS Access это поле типа «счетчик».

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

CREATE TABLE Товары
(
  id int IDENTITY(1, 1),
  товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY (id)
)

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

Первичный ключ может состоять из более, чем одной колонки. Следующий пример создает таблицу, в которой поля «id» и «Товар» образуют первичный ключ, а значит, будет создан индекс уникальности на оба поля:

CREATE TABLE Товары1
(
  id int IDENTITY(1, 1),
  Товар varchar(50),
  Цена money,
  Количество numeric(10, 2),
  CONSTRAINT PK_id PRIMARY KEY 
         (id, )
)

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

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

SQL PRIMARY KEY в CREATE TABLE

Следующий SQL создает первичный ключ в столбце «ID» при создании таблицы «Persons»:

MySQL:

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

Чтобы разрешить именование ограничения PRIMARY KEY и определить ограничение PRIMARY KEY для нескольких столбцов, используйте следующий синтаксис 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).
Однако значение первичного ключа состоит из TWO COLUMNS (ID + LastName).

Создать первичный ключ (оператор CREATE TABLE)

Первичный ключ может быть создан при выполнении оператора CREATE TABLE в SQL.

Синтаксис

Синтаксис для создания первичного ключа с помощью оператора CREATE TABLE в SQL.

CREATE TABLE table_name
(
column1 datatype ,
column2 datatype ,
…

CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, … pk_col_n)
);

Или

CREATE TABLE table_name
(
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype ,
…
);

table_name

Имя таблицы, которую вы хотите создать

column1, column2

Столбцы, которые вы хотите создать в таблице

constraint_name

Название первичного ключа

pk_col1, pk_col2, … pk_col_n

Столбцы, составляющие первичный ключ

Пример

Давайте посмотрим, как создать первичный ключ с помощью оператора CREATE TABLE в SQL. Мы начнем с очень простого, где наш первичный ключ состоит всего из одного столбца.
Например:

PgSQL

CREATE TABLE suppliers
( supplier_id int NOT NULL,
supplier_name char(50) NOT NULL,
contact_name char(50),
CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

1 2 3 4 5 6

CREATETABLEsuppliers (supplier_idintNOT NULL, supplier_namechar(50)NOT NULL, contact_namechar(50), CONSTRAINTsuppliers_pkPRIMARYKEY(supplier_id) );

В этом примере мы создали первичный ключ для таблицы suppliers, который называется sources_pk. Он состоит только из одного столбца — столбца supplier_id.
Мы могли бы использовать альтернативный синтаксис и создать этот же первичный ключ следующим образом.

PgSQL

CREATE TABLE suppliers
( supplier_id int CONSTRAINT suppliers_pk PRIMARY KEY,
supplier_name char(50) NOT NULL,
contact_name char(50)
);

1 2 3 4 5

CREATETABLEsuppliers (supplier_idintCONSTRAINTsuppliers_pkPRIMARYKEY, supplier_namechar(50)NOT NULL, contact_namechar(50) );

Оба эти синтаксиса действительны при создании первичного ключа только с одним полем.
Если вы создаете первичный ключ, который состоит из 2-х или более столбцов, вы ограничены использованием только первого синтаксиса, в котором первичный ключ определен в конце оператора CREATE TABLE.
Например:

PgSQL

CREATE TABLE contacts
( last_name VARCHAR(30) NOT NULL,
first_name VARCHAR(25) NOT NULL,
birthday DATE,
CONSTRAINT contacts_pk PRIMARY KEY (last_name, first_name)
);

1 2 3 4 5 6

CREATETABLEcontacts (last_nameVARCHAR(30)NOT NULL, first_nameVARCHAR(25)NOT NULL, birthdayDATE, CONSTRAINTcontacts_pkPRIMARYKEY(last_name,first_name) );

Этот пример создает первичный ключ с именем contacts_pk, который состоит из комбинации столбцов last_name и first_name. Поэтому каждая комбинация last_name и first_name должна быть уникальной в таблице contacts.

Связи между отношениями

Итак, первичный ключ в базе данных – это один или несколько столбцов таблицы, позволяющий однозначно идентифицировать строку этого отношения. Для чего же он нужен?

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

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

Типы движков баз данных MySQL

Каждый из примеров создания таблицы в этой статье до этого момента включал в себя определение ENGINE= . MySQL поставляется с несколькими различными движками баз данных, каждый из которых имеет свои преимущества. Используя директиву ENGINE =, можно выбрать, какой движок использовать для каждой таблицы. В настоящее время доступны следующие движки баз данных MySQL:

• InnoDB — был представлен вMySQL версии 4.0 и классифицирован как безопасная среда для транзакций.Ее механизм гарантирует, что все транзакции будут завершены на 100%. При этом частично завершенные транзакции (например, в результате отказа сервера или сбоя питания) не будут записаны. Недостатком InnoDB является отсутствие поддержки полнотекстового поиска.
• MyISAM — высокопроизводительный движок с поддержкой полнотекстового поиска. Эта производительность и функциональность обеспечивается за счет отсутствия безопасности транзакций.
• MEMORY— с точки зрения функционала эквивалентен MyISAM, за исключением того, что все данные хранятся в оперативной памяти, а не на жестком диске. Это обеспечивает высокую скорость обработки. Временный характер данных, сохраняемых в оперативной памяти, делает движок MEMORY более подходящим для временного хранения таблиц.

Движки различных типов могут сочетаться в одной базе данных. Например, некоторые таблицы могут использовать движок InnoDB, а другие — MyISAM. Если во время создания таблицы движок не указывается, то по умолчанию MySQL будет использовать MyISAM.

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

CREATE TABLE tmp_orders
{
tmp_number   int    NOT_NULL,
tmp_quantity    int    NOT_NULL,
tmp_desc     char(20) NOT_NULL,
      PRIMARY KEY (tmp_number)
) ENGINE=MEMORY;

Что такое первичный ключ

Столбец первичного ключа в таблице помогает идентифицировать каждую строку или запись в таблице. Содержит уникальные значения. Столбец первичного ключа не может иметь значения Null. Таблица может иметь один первичный ключ. В таблице Student, student_id является первичным ключом. В таблице Patient_Details, Patient_id является первичным ключом. Для первичного ключа необязательно иметь одно поле. Это также может быть комбинация нескольких полей. Когда первичный ключ состоит из нескольких полей, он называется составным ключом. Например, первичный ключ таблицы Student может быть комбинацией student_id и name.

Ограничения первичного ключа

Обычно в таблице есть столбец или сочетание столбцов, содержащих значения, уникально определяющие каждую строку таблицы. Этот столбец, или столбцы, называются первичным ключом (PK) таблицы и обеспечивает целостность сущности таблицы. Ограничения первичного ключа часто определяются в столбце идентификаторов, поскольку гарантируют уникальность данных.

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

Как показано на следующем рисунке, столбцы ProductID и VendorID в таблице Purchasing.ProductVendor формируют составное ограничение первичного ключа для данной таблицы. При этом гарантируется, что каждая строка в таблице ProductVendor имеет уникальное сочетание значений ProductID и VendorID. Это предотвращает вставку повторяющихся строк.

• В таблице возможно наличие только одного ограничения по первичному ключу.

• Первичный ключ не может включать больше 16 столбцов, а общая длина ключа не может превышать 900 байт.

• Индекс, формируемый ограничением первичного ключа, не может повлечь за собой выход количества индексов в таблице за пределы в 999 некластеризованных индексов и 1 кластеризованный.

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

• Все столбцы с ограничением первичного ключа должны быть определены как не допускающие значения NULL. Если допустимость значения NULL не указана, то все столбцы c ограничением первичного ключа устанавливаются как не допускающие значения NULL.

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

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

Primary key может быть простым и составным. Если уникальность записи определяется значением только в одном поле, как описано выше, мы имеем дело с простым ключом. Составной ключ – это первичный ключ базы данных, состоящий из двух и более полей. Рассмотрим следующее отношение клиентов банка.

Ф. И. О.	Дата рождения	Серия паспорта	Номер паспорта
Иванов П.А.	12.05.1996	75	0553009
Сергеев В.Т.	14.07.1958	71	4100654
Краснов Л.В.	22.01.2001	73	1265165

Паспорта людей могут содержать одни и те же серии либо номера, но паспортов с одним и тем же сочетанием серии и номера не существует. Таким образом, поля «Серия паспорта» и «Номер паспорта» станут составным ключом указанного отношения, однозначно идентифицируя человека.

Преимущества использования связей в Access

Ключи обеспечивают раздельное хранение данных в связанных таблицах. Это несёт следующие плюсы:
1. Согласованность. Раз каждый элемент данных заносится в нашу таблицу лишь один раз, снижается вероятность появления несогласованных и неоднозначных данных. То же имя клиента станет храниться лишь в таблице клиентов, а не в разных записях в таблице заказов.
2. Эффективность. Хранить данные в одном месте — значит сэкономить место на жёстком диске. К тому же, данные из небольших таблиц мы сможем извлекать быстрее, чем из больших. Кроме того, если мы не храним данные по разным темам в различных таблицах, у нас часто появляются пустые значения, что неэффективно в плане использования имеющихся ресурсов и, как следствие, приводит к понижению производительности.
3. Простота. Структура базы данных (БД) становится более понятной, если данные по разным темам находятся в различных таблицах.

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

.

SQL 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 для добавления первичного ключа,
то столбец(ы) первичного ключа уже должен быть объявлен не содержащим нулевых значений (при первом создании таблицы).

Другое использование термина

Следующие термины не являются ключами в смысле реляционных баз данных:

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

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

• Говорящий ключ (также называемый естественным ключом ): ключ-кандидат, который естественным образом присутствует в кортеже. Такой ключ также имеет значение в реальном мире, например B. «Номер шасси» для автомобилей, зарегистрированных в полиции. В случае говорящих ключей следует отметить, что ключевой домен может сломаться, если полевой домен не выбран тщательно. Например, из-за роста числа регистраций новых транспортных средств пятизначные номера транспортных средств могут в какой-то момент оказаться слишком маленькими, что потребует соответствующей реорганизации ключевых обозначений. Если будет сделана попытка закодировать назначения говорящей группы в ключе, очень вероятны разрывы клавиш, потому что диапазоны номеров не используются последовательно. Кроме того, такая практика также нарушает правило нормализации, поэтому групповое назначение должно выполняться через поле атрибута или даже через таблицу назначений N: M. Естественные ключи часто полезны, например, для сортировки записей данных в хронологии их создания или требуются клиенту. Например, номера счетов соответствуют спецификациям плана счетов . При разработке ключевой области необходимо в достаточной мере учитывать такие критерии, как запланированная мощность, удобочитаемость и обработка пользователем.
• Замещающий ключ ( суррогатный ключ ): искусственно созданный атрибут, который раньше не появлялся в кортеже, который определяет кортежи отношения, которое часто используется в качестве первичного ключа. Тривиальный пример: порядковый номер документа. Суррогатные ключи используются, например, в технологии OLAP , где очень широкие составные ключи преобразуются в более компактный искусственный суррогатный ключ. В гетерогенных прикладных системах суррогатный ключ для конкретной сущности назначается ведущей системой, определенной для этой цели . Если записи данных передаются во вторые системы через интерфейс, то в дополнение к ключевому атрибуту, который используется, например, в таблице перемещений, во вторую систему должен быть передан связанный объект с основными данными.

Создать первичный ключ (оператор ALTER TABLE)

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

Синтаксис

Синтаксис для создания первичного ключа с помощью оператора ALTER TABLE в SQL.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, … column_n);

table_name

Имя таблицы для изменения. Это таблица, к которой вы хотите добавить первичный ключ

constraint_name

Название первичного ключа

column1, column2, … column_n

Столбцы, составляющие первичный ключ

Пример

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

PgSQL

ALTER TABLE suppliers
ADD CONSTRAINT suppliers_pk
PRIMARY KEY (supplier_id);

1 2 3

ALTERTABLEsuppliers ADDCONSTRAINTsuppliers_pk PRIMARYKEY(supplier_id);

В этом примере мы создали первичный ключ для существующей таблицы suppliers, который называется sources_pk. Он состоит из столбца supplier_id.
Мы также могли бы создать первичный ключ с более чем одним полем, как показано ниже.

PgSQL

ALTER TABLE suppliers
ADD CONSTRAINT suppliers_pk
PRIMARY KEY (supplier_id, supplier_name);

1 2 3

ALTERTABLEsuppliers ADDCONSTRAINTsuppliers_pk PRIMARYKEY(supplier_id,supplier_name);

Этот пример создал бы первичный ключ с именем supplier_pk, который состоит из комбинации столбцов supplier_id и supplier_name.

Что такое внешний ключ

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

Рисунок 1: Первичный и Внешний ключ

Например, предположим, что есть база данных продаж. Имеются таблицы клиентов и продуктов. Таблица customer содержит столбцы customer_id, name, address и contact_no. Первичный ключ таблицы клиента — customer_id. Товар имеет столбцы product_id, name, quality. Первичный ключ таблицы продукта — product_id. Размещение product_id в таблице клиентов создаст связь между двумя таблицами. Product_id в таблице product является первичным ключом, но это внешний ключ в customer_table. Аналогично, можно соединять таблицы в базе данных, используя внешний ключ.

SQL Справочник

SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник

Заместитель ключа

→ Основная статья : суррогатный ключ

Возможно, что все ключевые кандидаты отношения состоят из нескольких атрибутов или что все ключевые кандидаты лишь неадекватно отражают фактические отношения. Один из таких случаев — б) наших примеров. Если вы хотите идентифицировать человека в таблице «Клиент», вам всегда нужно вводить его имя и день рождения одновременно. Поэтому часто желательно ввести дополнительный атрибут, который служит первичным ключом: он называется альтернативным ключом (англ. Surrogate key ). Например, b), будет подходящим бизнес-идентификационный номер, такой как «номер клиента» или порядковый номер.

Ключевой кандидат

Ключ — кандидат (англ. Ключ кандидат ) является минимальный набор атрибутов , который однозначно идентифицирует кортеж (записей) отношение. Формальное определение: это отношение на множестве атрибутов , то: является точным , то кандидат ключ R , если: .
Р.(А.){\ Displaystyle R (A)}А.знак равно{А.1,…,А.п}{\ Displaystyle A: = \ {A_ {1}, …, A_ {n} \}}α⊆А.{\ displaystyle \ alpha \ substeq A}α→˙А.{\ displaystyle \ alpha {\ dot {\ rightarrow}} A}

Здесь используется термин полная функциональная зависимость, представленная  как. Здесь A полностью функционально зависим, что означает:
α→˙А.{\ displaystyle \ alpha {\ dot {\ rightarrow}} A}α{\ displaystyle \ alpha}

1. Если два кортежа имеют одинаковые значения в ключевых атрибутах (), они также имеют одинаковые значения во всех других атрибутах (A). А также:α{\ displaystyle \ alpha}
2. Если вы удалите атрибут , свойство 1 больше не применяется.α{\ displaystyle \ alpha}

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

а

{ISBN}, {автор, название книги}

б

{Имя, день рождения}

c

{Начальник, подчиненный}

Из списка суперключей были выбраны минимальные. Иногда используется термин « кандидат ключ» , который является дословным переводом английского технического термина « кандидат ключ» .

Управление ключами SQL Server и базы данных

Управление ключами шифрования заключается в создании новых ключей базы данных, создании резервной копии ключей сервера и базы данных и знании порядка восстановления, удаления и смены ключей.

Чтобы управлять симметричными ключами, можно использовать средства, входящие в SQL Server , для выполнения следующих действий:

• Резервное копирование копии ключа сервера и базы данных, чтобы использовать их при восстановлении установки сервера или в ходе запланированного переноса.

• Восстановление ранее сохраненного ключа в базе данных. Это позволяет новому экземпляру сервера обращаться к существующим данным, которые первоначально шифровались не им.

• Удаление зашифрованных данных из базы данных в маловероятной ситуации, когда не удается обратиться к зашифрованным данным.

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

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

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии