Внешние ключи FOREIGN KEY. SQL – Primary Key (Первичный ключ) Как создать таблицу в sql primary key
Ограничение PRIMARY KEY
PRIMARY KEY является ограничением целостности на уровне столбца - набор поддерживаемых правил, - которое формально отмечает столбец или группу столбцов как уникальный идентификатор для каждой строки в таблице.
Если вы пришли в Firebird из СУБД, которые поддерживают концепцию "первичного индекса" для определения ключа (обычно основанные на файлах системы, такие как Paradox, Access и MySQL), то Firebird и мир стандартов SQL вам понятны. Первичный ключ является не индексом, а ограничением. Одним из правил для такого ограничения является то, что ограничение должно иметь определенный уникальный индекс из одного или более связанных с ним непустых элементов.
Простое создание такого индекса не создает первичный ключ. При этом создание ограничения первичного ключа приводит к созданию требуемого индекса, состоящего из столбцов, перечисленных в объявлении этого ограничения.
ВНИМАНИЕ! Не надо импортировать существующий "первичный индекс" из наследуемой системы, основанной на файлах, или создавать такой индекс в ожидании объявления ограничения первичного ключа. Firebird не может накладывать ограничение первичного ключа поверх существующего индекса - по крайней мере в существующих версиях, включая 1.5, - а оптимизатор запросов не будет правильно работать при дублировании индексов.
Таблица может иметь только один первичный ключ. Когда вы определяете ограничение, Firebird автоматически создает требуемый индекс, используя множество именованных правил. Имена индексов первичных ключей обсуждаются далее.
ВНИМАНИЕ! Если вы конвертируете базу данных в Firebird из любого другого источника за исключением InterBase или Oracle, то вы должны обратить особое внимание на схему в отношении имен и ограничений первичного ключа.
Хотя само ограничение PRIMARY KEY не является ссылочным ограничением, оно обычно является обязательной частью любого ссылочного ограничения, будучи потенциальным объектом предложения REFERENCES ограничения FOREIGN KEY. Подробности см. в главе 17.
Выбор первичного ключа
Выявление столбцов в качестве кандидатов на первичный ключ выходит за рамки данного издания. Много прекрасных книг было написано о нормализации, процессе сокращения избыточности и повторяющихся групп в наборах данных, а также о правильной идентификации элемента, который уникальным образом представляет одну строку в таблице. Если вы новичок в реляционных базах данных, то затраты на изучение хорошей книги по моделированию данных не будут слишком большими.
Кандидат в первичные ключи, который может быть одним столбцом или группой столбцов, имеет два обязательных требования.
* Атрибут NOT NULL должен быть объявлен для всех столбцов в группе из одного или более столбцов. Целостность ключа может быть осуществлена только сравнением значений, a NULL не является значением.
* Столбец или группа столбцов должны быть уникальными - т. е. не может в таблице появиться более одной строки с теми же значениями. Например, номер водительских прав или социального обеспечения могут рассматриваться как кандидаты, потому что они генерируются системами, которые не допускают дубликатов номеров.
К этим теоретическим "установкам" должна быть добавлена третья.
* Общий размер кандидата в ключи должен быть 252 байта или меньше. Дело здесь не просто в подсчете символов. Этот лимит должен быть уменьшен - в некоторых случаях радикально - если присутствует несколько столбцов, недвоичная последовательность сортировки или многобайтовый набор символов.
Как реальные данные могут привести вас к неудаче
Используя таблицу EMPLOYEE базы данных employee.fdb из каталога /examples корневого каталога Firebird (employee.gdb в версии 1,0.x), давайте посмотрим, как реальные данные могут привести к ошибочности ваших теоретических предположений об уникальности. Вот объявление, которое показывает имеющие смысл данные, хранимые в этой таблице:
CREATE TABLE EMPLOYEE (
FIRST_NAME VARCHAR(15) NOT NULL,
/* предположение: служащий должен иметь имя */
LAST_NAME VARCHAR(20) NOT NULL,
/* предположение: служащий должен иметь фамилию */
PHONE_EXT VARCHAR(4),
HIRE_DATE DATE DEFAULT CURRENT_DATE NOT NULL,
DEPT_NO CHAR(3) NOT NULL,
JOB_CODE VARCHAR (5) NOT NULL,
JOB_GRADE SMALLINT NOT NULL,
JOB_COUNTRY VARCHAR(15) NOT NULL,
SALARY NUMERIC (15, 2) DEFAULT 0 NOT NULL,
FULL_NAME COMPUTED BY FIRST_NAME || || LAST_NAME) ;
Фактически эта структура не имеет кандидата в ключи. Невозможно идентифицировать одну строку служащего, используя (FIRST_NAME, LAST_NAME) в качестве ключа, поскольку комбинация двух элементов с вероятностью от средней до высокой может дублироваться в организации. Мы не сможем сохранить записи двух служащих с именем John Smith.
Для получения ключей необходимо что-то изобрести. Это "что-то" - механизм, известный как суррогатный ключ.
Суррогатные ключи
Мы уже рассматривали суррогатный ключ во вводной теме о ключах в главе 14. Суррогатный первичный ключ - значение, гарантирующее уникальность и не имеющее смыслового содержания, которое используется в качестве заменителя ключа в структуре таблицы, которая не может предоставить кандидата на ключ в собственной структуре. По этой причине в таблицу EMPLOYEE добавляется EMP_NO (объявляется через домен) для выполнения роли суррогатного ключа:
CREATE DOMAIN EMPNO SMALLINT ;
ALTER TABLE EMPLOYEE
ADD EMP_NO EMPNO NOT NULL,
ADD CONSTRAINT PK_EMPLOYEE
PRIMARY KEY(EMP_NO) ;
Эта база данных также содержит генератор с именем EMP_NO_GEN и триггер Before insert (перед добавлением) с именем SET_EMP_NO для таблицы EMPLOYEE для получения значения данного ключа в момент добавления новой строки. В разд. "Реализация автоинкрементных ключей" главы 31 эта техника описывается в деталях. Это рекомендованный способ реализации суррогатных ключей в Firebird.
Возможно, вам захочется рассмотреть преимущества использования суррогатного первичного ключа не только в случае, когда таблица не может предложить кандидата, но также и в случаях, где ваш кандидат в ключи является составным.
Составные первичные ключи
В процессе анализа данных иногда в структуре данных можно отыскать единственный уникальный столбец. Теория советует найти два или более столбцов, сгруппированных вместе в качестве ключа, которые будут гарантировать уникальность строки. Когда множество столбцов объединяются для формирования ключа, такой ключ называется составным ключом (composite key) или иногда сложным ключом.
Если вы имеете опыт работы с такими СУБД, как Paradox, где использовали составные ключи для реализации иерархических отношений, вам, вероятно, будет тяжело расстаться с мыслью, что вам придется жить без них. Пока еще на практике составные ключи должны рассматриваться очень ограниченно в таких СУБД, как Firebird, где не выполняется проход по физическим индексным структурам на диске для реализации отношений.
В Firebird нет необходимости в составных индексах и, более того, составные индексы создают некоторые проблемы как для разработки, так и для производительности в случае больших таблиц.
* Составные ключи обычно являются составленными из неатомарных элементов ключа- т.е. выбранные столбцы имеют смысловое значение (они являются "значимыми данными") и, несомненно, уязвимы для внешних изменений и ошибок ручного ввода.
* Внешние ключи из других таблиц, которые ссылаются на эту таблицу, будут дублировать каждый элемент составного ключа. Ссылочная целостность подвергается риску при использовании неатомарных ключей. Комбинация неатомарных элементов увеличивает риск.
* Ключи - внешние, так же как и первичные - имеют постоянные индексы. Составные индексы имеют более строгие ограничения по размеру, чем индексы из одного столбца.
* Составные индексы имеют тенденцию к большим размерам. Большие индексы используют больше страниц базы данных, что приводит к тому, что индексные операции (сортировка, соединение и сравнение) выполняются медленнее, чем необходимо.
Атомарность столбцов первичного ключа
Рекомендуется на практике не включать в первичные и внешние ключи любые столбцы, имеющие смысл как данные. Это нарушает один из основных принципов проектирования реляционных баз данных- атомарность. Принцип атомарности требует, чтобы каждый элемент данных полностью существовал сам по себе с единым внутренним правилом управления его существованием.
Чтобы первичный ключ был атомарным, нужно быть вне человеческих решений. Если люди составляют его или классифицируют его, он не является атомарным. Если он является субъектом любого правила за исключением требований NOT NULL и уникальности, он не является атомарным. В приведенном ранее примере даже водительские права или номер социального обеспечения не соответствуют требованиям атомарности для первичного ключа, потому что они являются субъектами внешних систем.
Синтаксис объявления первичного ключа
Можно использовать несколько вариантов синтаксиса для назначения ограничения PRIMARY KEY столбцу или группе столбцов. Все столбцы, являющиеся элементами первичного ключа, должны быть предварительно определены с атрибутом NOT NULL. Так как нельзя добавить ограничение NOT NULL в столбец после его создания, необходимо позаботиться об этом ограничении до использования других ограничений.
Ограничение PRIMARY KEY может применяться в любой из следующих фаз создания метаданных:
* в определении столбца в операторах CREATE TABLE или ALTER TABLE как часть определения столбца;
* в определении таблицы в операторах CREATE TABLE или ALTER TABLE как отдельно определенное ограничение таблицы.
Определение первичного ключа как часть определения столбца
В следующей последовательности создается и подтверждается (commit) домен, не допускающий пустое значение, затем определяется столбец первичного ключа, основанный на этом домене, и одновременно применяется ограничение PRIMARY KEY к этому столбцу:
CREATE DOMAIN D_IDENTITY AS BIGINT NOT NULL;
CREATE TABLE PERSON (
PERSON_ID D_IDENTITY PRIMARY KEY,
Firebird создает ограничение таблицы с именем INTEG_M и индекс с именем RDB$PRIMARYnn. (пл в каждом случае - число, полученное от генератора. Эти два числа не связаны друг с другом.) Вы не можете повлиять на то, какими будут эти имена, и не можете поменять их.
Результат будет похожим, если вы используете тот же подход при добавлении столбца, используя оператор ALTER TABLE и создавая первичный ключ в одном предложении:
ALTER TABLE BOOK
ADD BOOK_ID D_IDENTITY PRIMARY KEY;
Определение первичного ключа как именованного ограничения
Другой способ определения первичного ключа в определении таблицы - добавить объявление ограничения в конце определений столбцов. Объявления ограничений помещаются последними, потому что они зависят от существования столбцов, к которым они обращаются. Этот метод дает вам возможность именования ограничений. Следующее объявление именует ограничение первичного ключа как PK_ATABLE:
CREATE TABLE ATABLE (
ID BIGINT NOT NULL,
ANOTHER_COLUMN VARCHAR (20),
CONSTRAINT PK_ATABLE PRIMARY KEY(ID));
Теперь вместо использования сгенерированного системой имени RDB$PRIMARYnnn Firebird использует PK_ATABLE В качестве имени этого ограничения. В Firebird 1.5 и выше он также применяет определенное пользователем имя ограничения для поддерживающего уникального индекса. В этом примере индекс получит имя PK_ATABLE, когда в других версиях его имя будет RDB$PRIMARYnnn.
Firebird 1.5 также позволяет использовать определенные пользователем имена для ограничения и поддерживающего его индекса.
Использование пользовательского индекса
До Firebird 1.5 не было возможности использовать убывающий индекс для поддержки первичного ключа. Начиная с версии 1.5, можно поддерживать первичный ключ убывающим индексом. Чтобы это сделать, в Firebird 1.5 добавляется расширение синтаксиса в форме предложения USING, позволяющего создавать индекс ASC (по возрастанию) или DESC (по убыванию) и присваивать ему имя, отличное от имени ограничения.
AS с и DESC определяют направление поиска. Подробнее эта концепция обсуждается в главе 18.
Следующий оператор создаст ограничение первичного ключа с именем PK ATEST и поддерживающий его убывающий индекс с именем IDX_PK_ATEST:
CREATE TABLE ATEST (
ID BIGINT NOT NULL,
DATA VARCHAR(10));
ALTER TABLE ATEST
ADD CONSTRAINT PK_ATEST PRIMARY KEY(ID)
USING DESC INDEX IDX_PK_ATEST;
Альтернативный синтаксис также будет работать:
CREATE TABLE ATEST (
ID BIGINT NOT NULL,
DATA VARCHAR(10),
CONSTRAINT PK_ATEST PRIMARY KEY(ID)
USING DESC INDEX IDX PK ATEST;
ВНИМАНИЕ! Если вы создаете индекс DESCENDING для ограничения первичного или уникального ключа, вы должны указать USING DESC INDEX для всех ссылающихся на него внешних ключей.
Добавление первичного ключа к существующей таблице
Добавление в таблицу ограничений может быть отложенным. Это общая практика разработчиков определять все свои таблицы без ограничений таблицы, а затем добавлять их, используя отдельный скрипт. Основная причина такой практики: большие скрипты часто дают сбой, потому что авторы забывают про некоторые зависимости. Просто будет меньше головной боли, если создавать базу данных в последовательности, которая уменьшает время и раздражение при исправлении ошибок зависимостей и нового запуска скриптов.
Обычно в первом скрипте мы объявляем таблицы и подтверждаем их создание:
CREATE TABLE ATABLE (
ID BIGINT NOT NULL,
ANOTHER_COLUMN VARCHAR (20),
< другие столбцы >) ;
CREATE TABLE ANOTHERTABLE (
ALTER TABLE ATABLE
ADD CONSTRAINT PK_ATABLE
PRIMARY KEY(ID);
ALTER TABLE ANOTHERTABLE...
В следующей главе при рассмотрении определений FOREIGN KEY станут очевидными преимущества создания базы данных в последовательности надежных зависимостей.
Из книги Базы данных: конспект лекций автора Автор неизвестен3. Ограничение целостности по состоянию Ограничение целостности реляционного объекта данных по состоянию – это так называемый инвариант данных.При этом целостность следует уверенно отличать от безопасности, которая, в свою очередь, подразумевает защиту данных от
Из книги Язык программирования С# 2005 и платформа.NET 2.0. автора Троелсен ЭндрюОграничение использования атрибута По умолчанию пользовательские атрибуты могут применяться к любой части программного кода (к методам, классам, свойствам и т.д.). Поэтому, если только это имеет смысл, можно использовать VehicleDescription для определения (среди прочего) методов,
Из книги Linux-сервер своими руками автора11.2.2. Ограничение доступа
Я считаю необходимым подробно рассмотреть блочную директиву Limit. Эта директива определяет вид и параметры доступа к тому или иному каталогу. Рассмотрим листинг 11.9.Листинг 11.9. Пример использования директивы Limit Ограничение доступа клиентам
Для ограничения доступа клиентов к некоторой компоненте h, будет использована возможность включения в объявление класса двух или более разделов feature. Объявление будет выглядеть следующим образомclass S2 featuref ...g ...feature {A, B}h ......endКомпоненты f и g 5.1. Ограничение перебора
В процессе достижения цели пролог-система осуществляет автоматический перебор вариантов, делая возврат при неуспехе какого-либо из них. Такой перебор - полезный программный механизм, поскольку он освобождает пользователя от необходимости Ограничение
Второй элемент формулы ОДП – дедлайн (от англ. deadline), или ограничение. Это может быть ограничение по времени (например, скидка 50 % только два дня) или по количеству (например, ценный подарок для первых 50 покупателей). Причем короткие по времени дедлайны работают Ссылочное ограничение
Ссылочное ограничение реализовано как FOREIGN KEY. Ограничение внешнего ключа существует только в контексте другой таблицы и уникального ключа этой таблицы, заданного явно или неявно в предложении REFERENCES при объявлении ограничения.Таблицы, связанные в Ограничение NOT NULL
Firebird не поддерживает атрибут указания допустимости пустого значения, как это делают некоторые нестандартные СУБД. В соответствии со стандартами все столбцы в Firebird могут содержать пустое значение, если не будет явно указано ограничение NOT NULL. 4.11.5. Ограничение сети
В крупных сетях описывать каждый компьютер очень сложно. Для облегчения этой задачи можно использовать групповые записи. Например, вам надо разрешить выход в Интернет только для сети 192.168.1.x (с маской 255.255.255.0). Это значит, что первые 24 бита (первые три 9.5.8. Ограничение канала
При организации доступа в Интернет очень часто требуется отдельным пользователям обеспечить большую скорость подключения. Как это сделать, когда по умолчанию все равноправны и могут работать на максимально доступной на данный момент скорости? Ограничение CHECK
Одним из наиболее полезных ограничений в базе данных является ограничение проверки. Идея его очень проста - проверять вставляемое в таблицу значение на какое-либо условие и, в зависимости от выполнения условия, вставлять или не вставлять данные.Синтаксис П2.4. Ограничение доступа
П2.4.1. Запрет доступа к сайту (или к списку сайтов)
Предположим, что вам нужно запретить доступ к определенному сайту (или к списку сайтов). Для этого зайдите в раздел Биллинг | Клиенты | Фильтры | До группы (рис. П2.10). Выполните команду меню Действие | П2.4.2. Ограничение скорости
Для ограничения скорости работы того или иного пользователя выделите его в списке пользователей, нажмите правую кнопку мыши и выберите команду Свойства. Перейдите на вкладку Ограничения (рис. П2.14), снимите флажок По умолчанию в области ) мы разбирали, как устроена реляционная (табличная) база данных и выяснили, что основными элементами реляционной базы данных являются: таблицы, столбцы и строки, а в математических понятиях: отношения, атрибуты и кортежи. Также часто, строки называют записями, столбцы называют колонками, а пересечение записи и колонки называют ячейкой. Важно
вспомнить, что содержание строки и названия столбцов должны быть уникальны в пределах одной базы данных. Важно
понимать, что можно создавать базы для любых типов данных: текстов, дат, времени, событий, цифр. В зависимости от типа информации реляционные базы данных делят на типы. Каждый тип данных (атрибут) имеет свое обозначение: Это основные типы данных, которых на самом деле гораздо больше. Причем, каждый язык программирования имеет свой набор системных атрибутов (типов данных). Выше мы вспоминали: каждая строка (запись) БД должна быть уникальна. Именно первичный ключ в виде наборов определенных значений, максимально идентифицируют каждую запись. Можно определить по-другому. Первичный ключ: набор определенных признаков, уникальных для каждой записи. Обозначается первичный ключ, как primary key. Primary key (PK)
очень важен для каждой таблицы. Поясню почему. На логической связи между таблицами, стоит остановиться подробнее. Foreign key, кратко FK.
Обеспечивает однозначную логическую связь, между таблицами одной БД. Например, есть две таблицы А и В. В таблице А (обувь), есть первичный ключ: размер, в таблице В (цвет) должна быть колонка с названием размер. В этой таблице «размер» это и будет внешний ключ для логической связи таблиц В и А. Более сложный пример.
Две таблицы данных: Люди и Номера телефонов. Таблица: Люди
Таблица: Номера телефонов
В таблице Номера телефонов PK уникален. FK этой таблицы является PK таблицы Люди. Связь между номерами телефонов и людьми обеспечивает FK таблицы телефонов. То есть: В завершении добавлю, что любая , управляющая базой данных, имеет технические возможности составить первичный ключ.
Вот так вот незаметно мы подошли к очень важной теме – первичных и внешних ключей. Если первые используются почти всеми, то вторые почему-то игнорируются. А зря. Внешние ключи – это не проблема, это реальная помощь в целостности данных. Мы уже достаточно много говорили про ключевые поля, но ни разу их не использовали. Самое интересное, что все работало. Это преимущество, а может недостаток базы данных 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, а на поля, которые должны быть уникальными установлено ограничение уникальности. Что выбрать? Зависит от конкретной задачи и от того, с чем вам удобнее работать. Внешний ключ также является ограничением CONSTRAINT и отображает связь между двумя таблицами. Допустим, что у вас есть две таблицы: У одного человека может быть несколько телефонов, поэтому мы разделили хранение данных в разные таблицы. На рисунке 1.4 визуально показана связь между двумя таблицами. Если вы уже работали со связанными таблицами, то этого для вас будет достаточно. Если вы слышите о связях впервые, то попробуем посмотреть на проблему поближе. Для примера возьмем таблицу из трех человек. В таблице 1.3 показано содержимое таблицы "Names". Здесь всего три строки и у каждой свой уникальный главный ключ. Для уникальности, когда будем создавать таблицу, сделаем ключ автоматически увеличиваемым полем. Таблица 1.3 Содержимое таблицы Names
Таблица 1.4. Содержимое таблицы Phones
В таблице 1.4 находится пять номеров телефонов. В поле главный ключ также уникальный главный ключ, которой также можно сделать автоматически увеличиваемым. Вторичный ключ – это связь с главным ключом таблицы Names. Как работает эта связь? У Петрова в таблице Names в качестве главного ключа стоит число 1. В таблице Phones во вторичном ключе ищем число 1 и получаем номера телефонов Петрова. То же самое и с остальными записями. Визуально связь можно увидеть на рисунке 1.5. Такое хранение данных очень удобно. Если бы не было возможности создавать связанные таблицы, то в таблице Names пришлось бы забивать все номера телефонов в одно поле. Это неудобно с точки зрения использования, поддержки и поиска данных. Можно создать в таблице несколько полей Names, но возникает вопрос – сколько. У одного человека может быть только 1 телефон, а у меня, например, их 3, не считая рабочих. Большое количество полей приводит к избыточности данных. Можно для каждого телефона в таблице Names заводить отдельную строку с фамилией, но это легко только для такого простого примера, когда нужно вводить только фамилию и легко можно внести несколько записей для Петрова с несколькими номерами телефонов. А если полей будет 10 или 20?
Итак, создание двух таблиц связанных внешним ключом можно увидеть в листинге 1.6. Листинг 1.6. Создание таблиц связанных внешним ключом
CREATE TABLE Names
(idName int IDENTITY(1,1),
vcName varchar(50),
CONSTRAINT PK_guid PRIMARY KEY (idName),)
CREATE TABLE Phones
(idPhone int IDENTITY(1,1),
idName int,
vcPhone varchar(10),
CONSTRAINT PK_idPhone PRIMARY KEY (idPhone),
CONSTRAINT FK_idName FOREIGN KEY (idName)
REFERENCES Names (idName))
Внимательно изучите содержимое листинга. Он достаточно интересен, потому что использует некоторые операторы, которые мы уже рассмотрели и дополнительный пример не помешает. Для обеих таблиц создается ключевое поле, которое стоит первым, имеет тип int и автоматически увеличивается, начиная с 1 с приращением в единицу. Ключевое поле делается главным ключом с помощью ограничение CONSTRAINT. В описании таблицы Phones последняя строка содержит новое для нас объявление, а именно – объявление внешнего ключа с помощью оператора FOREIGN KEY. Как видите, это тоже ограничение и чуть позже вы увидите почему. В скобках указывается поле таблицы, которое должно быть связано с другой таблицей. После этого идет ключевое слово REFERENCES (ссылка), имя таблицы, с которой должна быть связь (Names) и в скобках имя поля ("idName"). Таким образом, мы навели связь, которая отображена на рисунке 1.4. Внимание!
Внешний ключ может ссылаться только на первичный ключ другой таблицы или на ограничение уникальности. Это значит, что после ключевого слова REFERENCES должно быть имя таблицы и в скобках можно указывать только первичный ключ или поле с ограничением UNIQUE. Другие поля указывать нельзя. Теперь, если можно наполнять таблицы данными. Следующие три команды добавляют три фамилии, которые мы видели в таблице 1.3: INSERT INTO Names(vcName)
VALUES("Петров")
INSERT INTO Names(vcName)
VALUES("Иванов")
INSERT INTO Names(vcName)
VALUES("Сидоров")
Если вы уже работали с SQL то сможете добавить записи и для таблицы телефонов. Я опущу эти команды, а вы можете увидеть их в файле foreign_keys.sql директории Chapter1 на компакт диске. Наша задача сейчас увидеть, в чем заключаются ограничительные действия внешнего ключа, давайте разберемся. Мы указали явную связь между двумя полями в разных таблицах. Если попытаться добавить в таблицу телефонов запись с идентификатором в поле "idName", не существующим в одноименном поле (имя можно было сделать и другим) таблице с фамилиями, то произойдет ошибка. Это нарушит связь между двумя таблицами, а ограничение внешнего ключа не позволит существовать записям без связи. Ограничение действует и при изменении или удалении записей. Например, если попытаться удалить строку с фамилией Петров, то произойдет ошибка ограничения внешнего ключа. Нельзя удалять записи, для которых существуют внешне связанные строки. Для начала, нужно удалить все телефоны для данной записи и только после этого будет возможно удаление самой строки с фамилией Петров. Во время создания внешнего ключа, можно указать ON DELETE CASCADE или ON UPDATE CASCADE. В этом случае, если удалить запись Петрова из таблице Names или изменить идентификатор, то все записи в таблице Phones, связанные со строкой Петрова будут автоматически обновлены. Никогда. Нет, нужно написать большими буквами: НИКОГДА не делайте этого. Все должно удаляться или изменяться вручную. Если пользователь случайно удалит запись из таблицы Names, то удаляться и соответствующие телефоны. Смысл тогда создавать внешний ключ, если половина его ограничительных возможностей исчезает! Все необходимо делать только вручную, а идентификаторы изменять не рекомендуется вообще никогда. Удаление самих таблиц также должно начинаться с подчиненной таблицы, то есть с Phones, и только потом можно удалить главную таблицу Names. Напоследок покажу, как красиво получить соответствие имен и телефонов из двух таблиц: SELECT vcName, vcPhone
FROM Names, Phones
WHERE Names.idName=Phones.idName
Более подробно о подобных запросах мы поговорим в главе 2. Сейчас же я привел пример только для того, чтобы вы увидели мощь связанных таблиц. Таблица может содержать до 253 внешних ключей, что вполне достаточно даже для построения самых сложных баз данных. Лично мне приходилось работать с базами данных, где количество внешних ключей не превышало 7 на одну таблицу. Если больше, то скорей всего база данных спроектирована неверно, хотя бывают и исключения. Сама таблица также может иметь максимум 253 внешних ключей. Внешние ключи в таблице встречаются реже, в основном не более 3. Чаще всего в таблице может быть много ссылок на другие таблицы. Внешний ключ может ссылаться на ту же таблицу, в которой он создается. Например, у вас есть таблица должностей в организации, как показано в таблице 1.5. Таблица состоит из трех полей: первичный ключ, внешний ключ и наименование должности. В любой организации может быть множество должностей, но вполне логичным будет в одной таблице отобразить их названия и структуру подчинения. Для этого внешний ключ нужно связать с первичным ключом таблицы должностей. Таблица 1.5. Таблица с внутренней связью
В результате мы получаем, что у генерального директора внешний ключ нулевой, т.е. эта должность стоит во главе всех остальных. У коммерческого директора и директора по общим вопросам внешний ключ указывает на строку генерального директора. Это значит, что эти две должности подчиняются непосредственно генеральному директору. И так далее. Посмотрим, как можно создать все это в виде SQL запроса: CREATE TABLE Positions
(idPosition int IDENTITY(1,1),
idParentPosition int,
vcName varchar(30),
CONSTRAINT PK_idPosition PRIMARY KEY (idPosition),
CONSTRAINT FK_idParentPosition FOREIGN KEY (idParentPosition)
REFERENCES Positions (idPosition))
Как видите, внешний ключ просто ссылается на ту же таблицу, которую мы создаем. На компакт диске, в директории Chapter1 можно увидеть в файле foreign_keys_to_self.sql пример создания этой таблицы, наполнения его данными и отображения должностей с учетом их подчинения. В следующей главе мы рассмотрим возможность работы с такими таблицами более подробно. Пока что мы рассмотрели классическую связь, когда одной строке основной таблицы данных соответствует одна строка из связанной таблицы. Такая связь называется один ко многим. Но существуют и другие связи, и сейчас мы рассмотрим еще одну – один к одному, когда одна запись основной таблице связана с одной записью другой. Чтобы это реализовать, достаточно связать первичные ключи обеих таблиц. Так как первичные ключи не могут повторяться, то в обеих таблицах связанными могут быть только одна строка. Следующий пример создает две таблицы, у которых создана связь между первичными ключами: 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))
Внешний ключ нужен только у одной из таблиц. Так как связь идет один к одному, то не имеет значения, в какой таблице создать его. Самая сложная связь – многие ко многим, когда много записей из одной таблицы соответствует многим записям из другой таблицы. Чтобы такое реализовать, двух таблиц мало, необходимо три таблицы. Для начала нужно понять, когда может использоваться связь многие ко многим? Допустим, что у вас есть две таблицы: список жителей дома и список номеров телефона. В одной квартире может быть более одного номера, а значит, одной фамилии может принадлежать два телефона. Получается, связь один ко многим. С другой стороны, в одной квартире может быть две семьи (коммунальная квартира или просто квартиросъемщик, который пользуется телефоном владельца), а значит, связь между телефоном и жителем тоже один ко многим. И самый сложный вариант – в коммунальной квартире находиться два телефона. В этом случае обоими номерами пользуются несколько жителей квартире. Вот и получается, что "много" семей может пользоваться "многими" телефонами (связь многие ко многим). Как реализовать связь многие ко многим? На первый взгляд, в реляционной модели это невозможно. Лет 10 назад я долго искал разные варианты и в результате просто создавал одну таблицу, которая была переполнена избыточностью данных. Но однажды, мне досталась одна задача, благодаря которой уже из условия на поверхность вышло отличное решение – нужно создать две таблицы жителей квартир и телефонов и реализовать в них только первичный ключ. Внешние ключи в этой таблице не нужны. А вот связь между таблицами должна быть через третью, связующую таблицу. На первый взгляд это сложно и не понятно, но один раз разобравшись с этим методом, вы увидите всю мощь этого решения. В таблицах 1.6 и 1.7 показаны примеры таблиц фамилий и телефонов соответственно. А в таблице 1.8 показана связующая таблица. Таблица 1.6. Таблица фамилий
Таблица 1.7. Таблица телефонов
Таблица 1.8. Таблица телефонов
Давайте теперь посмотрим, какая будет логика поиска данных при связи многие ко многим. Допустим, что нам нужно найти все телефоны, которые принадлежат Иванову. У Иванова первичный ключ равен 1. Находим в связующей таблице все записи, у которых поле "Связь с именем" равно 1. Это будут записи 1 и 2. В этих записях в поле "Связь с телефоном" находятся идентификаторы 1 и 2 соответственно, а значит, Иванову принадлежат номера из таблицы телефонов, которые расположены в строках 1 и 2. Теперь решим обратную задачу – определим, кто имеет доступ к номеру телефона 567575677. Этот номер в таблице телефонов имеет ключ 3. Ищем все записи в связующей таблице, где в поле "Связь с телефоном" равно 3. Это записи с номерами 4 и 5, которые в поле "Связь с именем" содержат значения 2 и 3 соответственно. Если теперь посмотреть на таблицу фамилий, то вы увидите под номерами 2 и 3 Петрова и Сидорова. Значит, именно эти два жителя пользуются телефоном с номером 567575677. Просмотрите все три таблицы и убедитесь, что вы поняли, какие номера телефонов принадлежат каким жителям и наоборот. Если вы увидите эту связь, то поймете, что она проста, как три копейки и сможете быстро реализовать ее в своих проектах. 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))
CREATE TABLE LinkTable
(idLinkTable uniqueidentifier DEFAULT NEWID(),
idName uniqueidentifier,
idPhone uniqueidentifier,
CONSTRAINT PK_idLinkTable PRIMARY KEY (idLinkTable),
CONSTRAINT FK_idPhone FOREIGN KEY (idPhone)
REFERENCES Phones (idPhone),
CONSTRAINT FK_idName FOREIGN KEY (idName)
REFERENCES Names (idName))
У связующей таблицы два внешних ключа, которые связываются с таблицами имен и телефонов и один первичный ключ, который обеспечивает уникальность записей. В качестве первичного ключа я выбрал GUID поле, потому что для решения именно этой задачи он более удобен. Дело в том, что нам нужно вставлять записи в две таблицы и в обоих случаях нужно указывать один и тот же ключ. Значение GUID можно сгенерировать, а потом можно использовать при вставке данных в обе таблицы. Вы можете использовать в качестве ключа и автоматически увеличиваемое поле, но в этом случае проблему решить немного сложнее, точнее сказать, решать проблему неудобно. Например, добавляя номер телефона, необходимо сначала вставить соответствующую строку в таблицу, потом найти ее, определить ключ, который был назначен строке, и после этого уже наводить связь. На данном этапе мы ограничиваемся только созданием таблиц, а в разделе 2.8 мы вернемся к этой теме и научимся и научимся работать со связанными таблицами. Работа со связью один к одному и один ко многим отличается не сильно, потому что в этой схеме участвует только две таблицы. Связь многие ко многим немного сложнее из-за связующей таблицы, поэтому мы ее рассмотрим отдельно в разделе 2.27. Последнее обновление: 27.04.2019 Внешние ключи позволяют установить связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и
указывает на один из столбцов из главной таблицы. Как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы. Общий синтаксис установки внешнего ключа на уровне таблицы: FOREIGN KEY (столбец1, столбец2, ... столбецN)
REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, ... столбец_главной_таблицыN)
Для создания ограничения внешнего ключа после FOREIGN KEY
указывается столбец таблицы, который будет представляет
внешний ключ. А после ключевого слова REFERENCES
указывается имя
связанной таблицы, а затем в скобках имя связанного столбца, на который будет указывать внешний ключ. После выражения REFERENCES
идут выражения ON DELETE
и ON UPDATE
, которые задают действие при удалении и обновлении строки из
главной таблицы соответственно. Например, определим две таблицы и свяжем их посредством внешнего ключа: CREATE TABLE Customers
(Id INT PRIMARY KEY AUTO_INCREMENT,
Age INT,
FirstName VARCHAR(20) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Phone VARCHAR(20) NOT NULL UNIQUE);
CREATE TABLE Orders
(Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
В данном случае определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и
представляет заказ, сделанный клиентом. Таблица Orders через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец
CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers. С помощью оператора CONSTRAINT
можно задать имя для ограничения внешнего ключа: CREATE TABLE Orders
(Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
CONSTRAINT orders_custonmers_fk
FOREIGN KEY (CustomerId) REFERENCES Customers (Id));
С помощью выражений ON DELETE
и ON UPDATE
можно установить действия, которые выполняются
соответственно при удалении и изменении связанной строки из главной таблицы. В качестве действия могут использоваться следующие опции: CASCADE
: автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице. SET NULL
: при удалении или обновлении связанной строки из главной таблицы устанавливает для столбца внешнего ключа
значение NULL
. (В этом случае столбец внешнего ключа должен поддерживать установку NULL) RESTRICT
: отклоняет удаление или изменение строк в главной таблице при наличии связанных строк в зависимой таблице. NO ACTION
: то же самое, что и RESTRICT . SET DEFAULT
: при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по
умолчанию, которое задается с помощью атрибуты DEFAULT. Несмотря на то, что данная опция в принципе доступна, однако движок InnoDB не поддерживает данное выражение. Каскадное удаление позволяет при удалении строки из главной таблицы автоматически удалить все связанные строки из зависимой таблицы.
Для этого применяется опция CASCADE
: CREATE TABLE Orders
(Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE CASCADE);
Подобным образом работает и выражение ON UPDATE CASCADE
. При изменении значения первичного ключа автоматически изменится
значение связанного с ним внешнего ключа. Однако поскольку первичные ключи изменяются очень редко, да и с принципе не рекомендуется использовать в
качестве первичных ключей столбцы с изменяемыми значениями, то на практике выражение ON UPDATE используется редко. При установки для внешнего ключа опции SET NULL
необходимо, чтобы столбец внешнего ключа допускал значение NULL: CREATE TABLE Orders
(Id INT PRIMARY KEY AUTO_INCREMENT,
CustomerId INT,
CreatedAt Date,
FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL);
Представляю Вашему вниманию вольный перевод статьи SQL for Beginners Part 2 Для каждого веб-разработчика важно уметь взаимодействовать с базами данных. Во второй части мы продолжаем изучение языка SQL
и применяем свои навыки к СУБД MySQL
. Мы познакомимся с индексами, типами данных и более сложными запросами. Обратитесь, пожалуйста, к разделу "Что вам нужно" первой части, которая находится . Если Вы хотите выполнять приведенные примеры на своем сервере, сделайте следующее: Индексы (или ключи) обычно используются для повышения скорости выполнения операторов, которые выбирают данные (такие как SELECT
) из таблиц. Они являются важной частью хорошей архитектуры баз данных, сложно их отнести к "оптимизации". Обычно индексы добавляются изначально, но могут быть добавлены позднее с помощью запроса ALTER TABLE
. Основные доводы в пользу индексации столбцов базы данных: Почти у всех таблиц есть первичный ключ, обычно это целое число с опцией автоинкремента (AUTO_INCREMET
). Очень часто подзапросы ведут к значительному снижению производительности, поэтому используйте их с осторожностью. Используя запрос 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
, и если запрос терпит неудачу в следствии дублирования первичного (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 - это важный язык и инструмент в арсенале веб-разработчика.Типы данных в базах
Что такое первичный ключ и внешний ключ таблиц реляционных баз данных
Первичный ключ
Ключ внешний
первичный ключ и внешний ключ
1.2.5. Первичный ключ
1.2.6. Внешний ключ
Отношение один к одному
Многие ко многим
ON DELETE и ON UPDATE
Каскадное удаление
Установка NULL
Что вам нужно
Индексы
Первичный ключ (PRIMARY KEY)
UNION: Совмещение данных
INSERT Продолжение
INSERT ... ON DUPLICATE KEY UPDATE
Типы данных
Целые
Дробные числовые типы данных
Строковые типы данных
Заключение