Уникальный ключ sql что это

Первичный ключ, внешний ключ и уникальный ключ при использовании выделенного пула SQL в Azure Synapse Analytics

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

Ограничения таблиц

Выделенный пул SQL поддерживает следующие ограничениях на уровне таблиц:

Синтаксис см. в описании команд ALTER TABLE и CREATE TABLE.

Ограничение FOREIGN KEY в выделенном пуле SQL не поддерживается.

Remarks

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

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

Примеры

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

Создайте таблицу в выделенном пуле SQL с ограничением UNIQUE:

Дальнейшие действия

После создания таблиц для выделенного пула SQL переходите к загрузке данных в таблицу. Дополнительные сведения см. в статье Загрузка данных в выделенный пул SQL.

Источник

jtest.ru

HTML, CSS, JavaScript, JQuery, PHP, MySQL

SQL для начинающих. Часть 2

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

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

Что вам нужно

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

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

Индексы

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

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

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

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

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

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

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

Давайте рассмотрим простой запрос, который создает таблицу для хранения списка штатов США:

Можно записать его так:

Уникальный ключ (UNIQUE)

Поскольку мы предполагаем, что имя штата уникально, нам надо немного изменить предыдущий запрос:

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

Теперь индекс называется «state_name», а не «name».

Индекс (INDEX)

Допустим мы хотим добавить столбец для хранения года, в котором каждый штат вошел в состав США.

Я добавил столбец «join_year» и проиндексировал его. Этот тип индекса не накладывает ограничение на уникальность.

Вы можете использовать KEY вместо INDEX.

Подробнее о производительности

Добавление индекса снижает производительность запросов INSERT и UPDATE. Потому что каждый раз при вставке новых данных в таблицу, данные индекса тоже обновляются, что требует выполнения дополнительной работы. Увеличение производительности для запросов SELECT обычно перевешивают эти недостатки. Однако не надо добавлять индексы к каждому столбцу таблицы, не подумав о том какие запросы будут выполняться.

Пример таблицы

Перед тем как идти дальше, я хотел бы создать таблицу с некоторыми данными.

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

GROUP BY: Группировка данных

Предложение GROUP BY группирует результирующий набор данных. Пример:

Итак, что произошло? В таблице у нас 50 строк, а запрос вернул только 34. Так произошло, потому что результат сгруппирован по столбцу «join_year». Другими словами, мы видим только уникальные позиции столбца join_year. Некоторые штаты имеют одно и тоже значение join_year, поэтому мы получили менее 50 строк.

Например, присутствует только одно значение с 1787 годом, хотя в эту группу входят 3 штата:

Вот эти три штата, но только название штата Delaware показано после выполнения запроса с группировкой. На самом деле мы могли бы увидеть название любого из трех штатов. Тогда в чем же смысл использования предложении GROUP BY?

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

COUNT(*): Подсчет строк

Это, пожалуй, наиболее часто используемая функция в запросах совместно с предложением GROUP BY. Она возвращает количество строк в каждой группе.

Например, мы можем использовать ее для подсчета количества штатов для каждого join_year:

Группировка всего

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

Количество строк во всей таблице:

Количество строк удовлетворяющие условию WHERE:

MIN(), MAX() и AVG()

Эти функции возвращают минимальное, максимальное и среднее значения:

GROUP_CONCAT()

Эта функция объединяет все значения внутри группы в одну строку с разделителем.

В первом запросе с GROUP BY только один штат для каждого года. Используя данную функцию, мы можем видеть все имена в каждой группе:

Вы можете использовать эту функцию для сложения численных значений.

IF() & CASE: Управление потоком выполнения

Как и в других языках программирования, в SQL есть конструкции для управления потоком выполнения.

Вот более практический пример использования функции SUM():

Результат выполнения запроса:

CASE (Выбор)

CASE работает подобно условию switch-case в других языках программирования.

Предположим, что мы хотим разделить штаты на три возможные категории.

Как Вы видите, мы можем применить GROUP BY для значений, возвращаемых после условия CASE. Вот что произошло:

HAVING: Условие для скрытых полей

Условие HAVING применяется для «скрытых» полей, таких как результат, возвращаемый агрегирующей функцией. Обычно оно используется совместно с GROUP BY.

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

Теперь, скажем, что нас интересуют только строки с количеством больше 1. Мы не можем использовать условие WHERE для этого:

На помощь приходит HAVING:

Помните, что такая возможность доступна не во всех СУБД.

Подзапросы

Можно использовать результат одного запроса в другом.

В этом примере мы получим штат с наибольшим населением:

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

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

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

В этом примере мы получим штат, который был присоединен к Союзу последним:

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

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

Подробнее о подзапросах

Подзапросы могуть быть очень сложными, поэтому я не буду слишком углубляться в них в этой статье. Если Вы хотите почитать о них более подробно, обратитесь к руководству по MySQL.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

REPLACE INTO

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

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

INSERT IGNORE

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

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

Типы данных

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

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

Целые

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

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

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

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

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

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

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

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

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

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

Тип DATE (Дата)

Тип DATE хранит даты и показывает их в формате «YYYY-MM-DD», но не хранит информацию о времени. Имеет диапазон от 1001-01-01 до 9999-12-31.

Тип DATETIME содержит дату и время и имеет формат «YYYY-MM-DD HH:MM:SS». Имеет диапазон от «1000-01-01 00:00:00» до «9999-12-31 23:59:59». Занимает 8 байт.

TIMESTAMP работает как DATETIME с некоторыми отличаями. Он занимает только 4 байта и имеет диапазон «1970-01-01 00:00:01» UTC до «2038-01-19 03:14:07» UTC. Например, он не очень подходит для хранения дат рождения.

Тип TIME хранит только время, а YEAR только год.

Другое

Другие типы данных, поддерживаемые MySQL. Посмотреть их список можно здесь. Так же обратите внимание на размеры хранимых данных каждого типа.

Заключение

Источник

Интернет и базы данных. Часть 04. Ключи и ссылочная целостность

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

Уникальный ключ

Внешний ключ

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

Ссылочная целостность

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

Более интересные моменты возникают, когда мы удаляем или изменяем строки родительской таблицы. Как при этом не допустить появления \»болтающихся в воздухе\» строк дочерней таблицы? Для этого существуют правила ссылочной целостности ON UPDATE и ON DELETE, которые, по стандарту SQL 92, могут содержать следующие инструкции:

Ключи и ссылочная целостность в MySQL и Oracle

Oracle поддерживает первичные, уникальные, внешние ключи в полном объеме. Oracle поддерживает следующие правила ссылочной целостности:

Более сложные правила ссылочной целостности в Oracle можно реализовать через механизм триггеров.

MySQL версии 4.1 (последняя на момент написания статьи стабильная версия) позволяет в командах CREATE / ALTER TABLE задавать фразы REFERENCES / FOREIGN KEY, но в работе никак их не учитывает и реально внешние ключи не создает. Соответственно правила ссылочной целостности, реализуемые через внешние ключи, в MySQL не поддерживаются. И все заботы по обеспечению целостности и непротиворечивости информации в базе MySQL ложатся на плечи разработчиков клиентских приложений.

Источник

SQL ключи во всех подробностях

В Интернете полно догматических заповедей о том, как нужно выбирать и использовать ключи в реляционных базах данных. Иногда споры даже переходят в холивары: использовать естественные или искусственные ключи? Автоинкрементные целые или UUID?

Прочитав шестьдесят четыре статьи, пролистав разделы пяти книг и задав кучу вопросов в IRC и StackOverflow, я (автор оригинальной статьи Joe «begriffs» Nelson), как мне кажется, собрал куски паззла воедино и теперь смогу примирить противников. Многие споры относительно ключей возникают, на самом деле, из-за неправильного понимания чужой точки зрения.

Содержание

Что же такое «ключи»?

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

Для примера рассмотрим таблицу для подсчёта карт в карточной игре:

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

Мы можем и должны задать базе данных это ограничение, добавив следующее:

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

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

Ограничения уникальности

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

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

Вот два примера таблиц с несколькими ключами.

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

Любопытный случай первичных ключей

То, что в предыдущем разделе мы назвали просто «ключами», обычно называется «потенциальными ключами» (candidate keys). Термин «candidate» подразумевает, что все такие ключи конкурируют за почётную роль «первичного ключа» (primary key), а оставшиеся назначаются «альтернативными ключами» (alternate keys).

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

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

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