Создание первичных ключей. Ограничение PRIMARY KEY Primary key синтаксис

В этой статье мы попробуем рассмотреть все, что касается ключей в SQL: для чего нужны, создание, ограничения ключей. В общем: будет скучно 😉

План на сегодня такой:

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

Если говорить простыми словами, то ключи в sql созданы для того, чтобы указать дополнительную функциональность столбца. Будь то уникальность или то, что столбец ссылается на другую таблицу (внешний ключ).

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

Столбец, который в базе данных должен быть уникальным помечают первичным ключом. Первичный ключ или primary key означает, что в таблице значение колонки primary key не может повторяться. Таким образом данный ключ позволяет однозначно идентифицировать запись в таблице не боясь при этом, что значение столбца повториться. Сразу пример: допустим у Вас есть таблица пользователей. В данной таблице есть поля: ФИО, год рождения, телефон. Как идентифицировать пользователя? Таким параметрам как ФИО и телефон доверять нельзя. Ведь у нас может быть несколько пользователей не только с одинаковой фамилией, но и с именем. Телефон может меняться со временем и пользователь с номером телефона может оказаться не тем кто у нас в базе данных.

Вот для этого и придумали первичный ключ. Один раз присвоили уникальный идентификатор и все. В mySql на примере которой мы выполняем все примеры из поле AUTO_INCREMENT нельзя задать если не указать, что это первичный ключ.

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

Внешний ключ (foreign key )

Есть еще внешний ключ (foreign key ). Его еще называют ссылочным. Он нужен для связывания таблиц между собой.

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

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

Создание внешнего ключа

create table shoes(shoes_id int auto_increment primary key, title text, size int, price float, count int, type varchar(30), supplier int, foreign key (supplier) references supplier (supplier_id));

Как видно на примере выше, синтаксис по созданию внешнего ключа довольно прост. Нужно в таблицу добавить поле, а после объявить это поле как внешний ключ и указать, куда он будет ссылаться. В данном случае поле supplier будет ссылаться на поле supplier_id в таблице supplier.

Составной ключ (composite key)

Что касается составного ключа — это несколько первичных ключей в таблице. Таким образом, создав composite key , уникальность записи будет проверяться по полям, которые объединенные в этот ключ.

Бывают ситуации, когда при вставке в таблицу нужно проверять запись на уникальность сразу по нескольким полям. Вот для этого и придуман составной ключ. Для примера я создам простую таблицу с composite key , чтобы показать синтаксис:

Create table test(field_1 int, field_2 text, field_3 bigint, primary key (field_1, field_3));

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

Это все, что касается ключей в SQL. Это небольшое пособие — подготовка к статье где мы подробно рассмотрим как объединять таблицы, чтобы они составляли единую базу данных.

ПРИМЕНЯЕТСЯ К: SQL Server (начиная с 2016)База данных SQL AzureХранилище данных SQL AzureParallel Data Warehouse

Определить первичный ключ в SQL Server 2016 можно с помощью среды SQL Server Management Studio или Transact-SQL. Создание первичного ключа автоматически приводит к созданию соответствующего уникального кластеризованного или некластеризованного индекса.

В этом разделе

    Перед началом работы выполните следующие действия.

    Ограничения

    Безопасность

    Создание первичного ключа с помощью:

    Среда SQL Server Management Studio

Ограничения

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

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

Безопасность

Разрешения

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

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

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

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

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

    Щелкните правой кнопкой мыши средство выбора строк столбца и выберите команду Задать первичный ключ .

Ключевой столбец-источник идентифицируется символом первичного ключа в соответствующем селекторе строк.

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

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

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

    В обозревателе объектов

    Создать запрос .

    Выполнить . В этом примере создается первичный ключ в столбце TransactionID .

    USE AdventureWorks2012; GO ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID); GO

Создание первичного ключа в новой таблице

    В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.

    На стандартной панели выберите пункт Создать запрос .

    Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить . В этом примере создается таблица и определяется первичный ключ для столбца TransactionID .

    USE AdventureWorks2012; GO CREATE TABLE Production.TransactionHistoryArchive1 (TransactionID int NOT NULL , CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID)); GO

    Дополнительные сведения см. в разделах , и .

Представляю Вашему вниманию вольный перевод статьи SQL for Beginners Part 2

Для каждого веб-разработчика важно уметь взаимодействовать с базами данных. Во второй части мы продолжаем изучение языка SQL и применяем свои навыки к СУБД MySQL . Мы познакомимся с индексами, типами данных и более сложными запросами.

Что вам нужно

Обратитесь, пожалуйста, к разделу "Что вам нужно" первой части, которая находится .

Если Вы хотите выполнять приведенные примеры на своем сервере, сделайте следующее:

  1. Откройте консоль MySQL и авторизуйтесь.
  2. Создайте базу "my_first_db" с помощью запроса CREATE , если она не была создана ранее.
  3. Смените базу, используя оператор USE .

Индексы

Индексы (или ключи) обычно используются для повышения скорости выполнения операторов, которые выбирают данные (такие как SELECT ) из таблиц.

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

Основные доводы в пользу индексации столбцов базы данных:

  • Почти каждая таблица имеет первичный ключ (PRIMARY KEY ), обычно это столбец "id".
  • Если в столбце предполагается хранить уникальные значения, он должен иметь уникальный индекс (UNIQUE ).
  • Если нужен частый поиск по столбцу (его использование в предложении WHERE ), он должен иметь обычный индекс (INDEX ).
  • Если столбец используется для взаимосвязи с другой таблицей, он должен быть по возможности внешним ключом (FOREIGN KEY ) или обычным индексом.

Первичный ключ (PRIMARY KEY)

Почти у всех таблиц есть первичный ключ, обычно это целое число с опцией автоинкремента (AUTO_INCREMET ).

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

UNION: Совмещение данных

Используя запрос UNION , можно объединять результаты нескольких напросов SELECT.

В этом примере объединяются штаты, название которых начинается с буквы "N", со штатами с большим населением:

(SELECT * FROM states WHERE name LIKE "n%") UNION (SELECT * FROM states WHERE population > 10000000);

Обратите внимание, что штат New York принадлежит крупным и начинается с буквы "N". Однако в списке он встречается один раз, т.к. дубликаты удаляются автоматически.

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

Например, у нас есть таблицы employees (сотрудники), managers (менеджеры) и customers (клиенты). В каждой таблице есть поле с адресом электронной почты. Если мы хотим получить все E-mail адреса в одном запросе, то можем поступить следующим образом:

(SELECT email FROM employees) UNION (SELECT email FROM managers) UNION (SELECT email FROM customers WHERE subscribed = 1);

Выполнив этот запрос, мы получим почтовые адреса всех сотрудников и менеджеров, и только тех клиентов, которые подписаны на рассылку.

INSERT Продолжение

Мы уже говорили о запросе INSERT в предыдущей статье. После того как мы рассмотрели индексы, мы можем поговорить о дополнительных возможностях запросов INSERT .

INSERT ... ON DUPLICATE KEY UPDATE

Это наиболее часто используемое условие. Сначала запрос пытается выполнить INSERT , и если запрос терпит неудачу в следствии дублирования первичного (PRIMARY KEY ) или уникального (UNIQUE KEY ) ключа, то выполняется запрос UPDATE .

Давайте сначала создадим тестовую таблицу.

Это таблица для хранения продуктов. Поле "stock" хранит количество продуктов доступных на складе.

Теперь попробуем вставить уже существующее значение в таблицу и посмотрим что произойдет.

Мы получили ошибку.

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

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

Обратите внимание, т.к. вставляется совершенно новая строка, поле автоинкремента увеличивается на единицу.

Это способ предотвращения появления ошибки о дублировании, прежде всего для того, чтобы не останавливать выполнение приложения. Может понадобится вставить новую строку без вывода каких-либо ошибок, если даже произошло дублирование.

Нет никаких ошибок и обновленных строк.

Типы данных

Каждый столбец в таблице должен быть определенного типа. Мы уже использовали типы INT , VARCHAR и DATE , но не останавливались на них подробно. Также мы рассмотрим еще несколько типов данных.

Начнем с числовых типов данных. Я разделяю из на две группы: Целые и дробные.

Целые

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

MySQL поддерживает 5 типов целых чисел разных размеров и диапазонов:

Дробные числовые типы данных

Эти типы могут хранить дробные числа: FLOAT, DOUBLE и DECIMAL.

FLOAT занимает 4 байта, DOUBLE занимает 8 байт и аналогичен предыдущему. DOUBLE более точный.

DECIMAL(M,N) имеет изменяемую точность. M максимальное число цифр, N - число цифр после десятичной точки.

Например, DECIMAL(13,4) имеет 9 знаков до запятой и 4 после.

Строковые типы данных

По названию можно догадаться, что в них можно хранить строки.

CHAR(N) может хранить N символов и имеет фиксированную величину. Например, CHAR(50) должен всегда содержать 50 символов в каждой строке во всем столбце. Максимально возможное значениен 255 символов

VARCHAR(N) работает также, но диапазон может меняться. N - обозначает максимальное значение. Если хранимая строка короче N символов, то она будет занимать меньше места на жестком диске. Максимально возможное значениен 65535 символов.

Разновидности типа TEXT больше подходят для длинных строк. TEXT имеет ограничение в 65535 символов, MEDIUMTEXT в 16.7 миллионов, и LONGTEXT в 4.3 миллиарда символов. MySQL обычно хранит их в отдельных хранилищах на сервере, для того что бы главное хранилище было по возможности меньше и быстрее.

.

Заключение

Спазибо за чтение статьи. SQL - это важный язык и инструмент в арсенале веб-разработчика.

P rimary Key (Первичный ключ) является полем в таблице, которое однозначно идентифицирует каждую строку/запись в таблице базы данных. Первичные ключи должны содержать уникальные значения. Первичный ключ столбец не может иметь значения .

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

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

Примечание – Вы могли бы использовать эти понятия при создании таблиц базы данных.

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

Вот синтаксис для определения атрибута ID в качестве первичного ключа в таблице Customers.

CREATE TABLE CUSTOMERS(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));

Для того, чтобы создать ограничение первичного ключа на столбце «ID», когда таблица CUSTOMERS уже существует, используйте следующий синтаксис SQL:

ALTER TABLE CUSTOMERS ADD PRIMARY KEY (ID);

Примечание

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

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

CREATE TABLE CUSTOMERS(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID, NAME));

Чтобы создать ограничение первичного ключа на колонки «ID» и «NAME», когда таблица CUSTOMERS уже существует, используйте следующий синтаксис SQL.

ALTER TABLE CUSTOMERS ADD CONSTRAINT PK_CUSTID PRIMARY KEY (ID, NAME);

Удаление первичного ключа

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

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

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

  • Столбцы какого типа и размера будут составлять каждую из таблиц, какие требуется выбрать имена для столбцов таблиц?
  • Какие столбцы могут содержать значение NULL ?
  • Будут ли использованы ограничения целостности , значения по умолчанию и правила для столбцов?
  • Необходимо ли индексирование столбцов, какие типы индексов будут применены для конкретных столбцов?
  • Какие столбцы будут входить в первичные и внешние ключи .

Для создания таблиц в среде MS SQL Server используется команда:

<определение_таблицы> ::= CREATE TABLE [ имя_базы_данных.[владелец]. | владелец. ]имя_таблицы (<элемент_таблицы>[,...n])

<элемент_таблицы> ::= {<определение_столбца>} | <имя_столбца> AS <выражение> | <ограничение_таблицы>

Обычно владельцем таблицы (dbo) является тот, кто ее создал.

<Выражение> задает значение для вычисляемого столбца . Вычисляемые столбцы - это виртуальные столбцы, т. е. физически в таблице они не хранятся и вычисляются с использованием значений столбцов той же таблицы. В выражении для вычисляемого столбца могут присутствовать имена обычных столбцов, константы и функции, связанные одним или несколькими операторами. Подзапросы в таком выражении участвовать не могут. Вычисляемые столбцы могут быть включены в раздел SELECT при указании списка столбцов, которые должны быть возвращены в результате выполнения запроса. Вычисляемые столбцы не могут входить во внешний ключ , для них не используются значения по умолчанию. Кроме того, вычисляемые столбцы не могут участвовать в операциях INSERT и DELETE .

<определение_столбца> ::= { имя_столбца <тип_данных>} [ [ DEFAULT <выражение> ] | [ IDENTITY (начало, шаг) ]]] [<ограничение_столбца>][...n]]

В определении столбца обратим внимание на параметр IDENTITY , который указывает, что соответствующий столбец будет столбцом-счетчиком . Для таблицы может быть определен только один столбец с таким свойством. Можно дополнительно указать начальное значение и шаг приращения. Если эти значения не указываются, то по умолчанию они оба равны 1. Если с ключевым словом IDENTITY указано NOT FOR REPLICATION , то сервер не будет выполнять автоматического генерирования значений для этого столбца, а разрешит вставку в столбец произвольных значений.

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

<ограничение_столбца>::= [ CONSTRAINT имя_ограничения ] { [ NULL | NOT NULL ] | [ {PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] [ WITH FILLFACTOR=фактор_заполнения ] [ ON {имя_группы_файлов | DEFAULT } ] ] ] | [ [ FOREIGN KEY ] REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] [ NOT FOR REPLICATION ]] | CHECK [ NOT FOR REPLICATION](<лог_выражение>) } <ограничение_таблицы>::= { [ {PRIMARY KEY | UNIQUE } [ CLUSTERED | NONCLUSTERED ] {(имя_столбца [,...n])} ] |FOREIGN KEY[(имя_столбца [,...n])] REFERENCES имя_род_таблицы [(имя_столбца_род_таблицы [,...n])] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] | NOT FOR REPLICATION ] | CHECK [ NOT FOR REPLICATION ] (лог_выражение) }

Рассмотрим отдельные параметры представленных конструкций, связанные с ограничениями целостности данных . Ограничения целостности имеют приоритет над триггерами, правилами и значениями по умолчанию. К ограничениям целостности относятся ограничение первичного ключа PRIMARY KEY , ограничение внешнего ключа FOREIGN KEY , ограничение уникальности UNIQUE , ограничение значения NULL , ограничение на проверку CHECK .

Ограничение первичного ключа (PRIMARY KEY)

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

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

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

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

Ограничение внешнего ключа (FOREIGN KEY)

Ограничение внешнего ключа - это основной механизм для поддержания ссылочной целостности между таблицами реляционной базы данных. Столбец дочерней таблицы, определенный в качестве внешнего ключа в параметре FOREIGN KEY , применяется для ссылки на столбец родительской таблицы, являющийся в ней первичным ключом . Имя родительской таблицы и столбцы ее первичного ключа указываются в предложении REFERENCES . Данные в столбцах, определенных в качестве внешнего ключа , могут принимать только такие же значения, какие находятся в связанных с ним столбцах первичного ключа родительской таблицы. Совпадение имен столбцов для связи дочерней и родительской таблиц необязательно. Первичный ключ может быть определен для столбца с одним именем, в то время как столбец, на который наложено ограничение FOREIGN KEY , может иметь совершенно другое имя. Единственным требованием остается соответствие столбцов по типу и размеру данных.

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

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

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

Ограничение ссылочной целостности задает требование, согласно которому для каждой записи в дочерней таблице должна иметься запись в родительской таблице. При этом изменение значения столбца связи в записи родительской таблицы при наличии дочерней записи блокируется, равно как и удаление родительской записи (запрет каскадного изменения и удаления), что гарантируется параметрами ON DELETE NO ACTION и ON UPDATE NO ACTION , принятыми по умолчанию. Для разрешения каскадного воздействия следует использовать параметры ON DELETE CASCADE и ON UPDATE CASCADE .

СХОЖИЕ СТАТЬИ