Создание первичных ключей
Вы можете определить первичный ключ в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Создание первичного ключа автоматически приводит к созданию соответствующего уникального кластеризованного индекса (или некластеризованного при наличии такого указания).
Перед началом
Ограничения
- В таблице возможно наличие только одного ограничения по первичному ключу.
- Все столбцы с ограничением PRIMARY KEY должны иметь признак NOT NULL. Если допустимость значения NULL не указана, то для всех столбцов c ограничением PRIMARY KEY устанавливается признак NOT NULL.
Безопасность
Разрешения
Создание новой таблицы с первичным ключом требует разрешения CREATE TABLE в базе данных и разрешения ALTER на схему, в которой создается таблица.
Создание первичного ключа в существующей таблице требует разрешения ALTER на таблицу.
Использование SQL Server Management Studio
Создание первичного ключа
- В обозреватель объектов щелкните правой кнопкой мыши таблицу, к которой нужно добавить уникальное ограничение, и выберите Пункт Конструктор.
- В Designer таблицы выберите селектор строк для столбца базы данных, который необходимо определить в качестве первичного ключа. Если вы хотите выбрать несколько столбцов, удерживайте нажатой клавишу CTRL, выбирая селекторы строк для других столбцов.
- Щелкните правой кнопкой мыши средство выбора строк столбца и выберите команду Задать первичный ключ.
Чтобы переопределить первичный ключ, необходимо удалить все связи с существующим первичным ключом и только после этого создавать новый первичный ключ. Появится сообщение, предупреждающее об автоматическом удалении в ходе процесса всех существующих связей.
Ключевой столбец-источник идентифицируется символом первичного ключа в соответствующем селекторе строк.
Если первичный ключ состоит более чем из одного столбца, то в одном столбце могут встречаться дублирующиеся значения, но все сочетания значений изо всех столбцов первичного ключа должны быть уникальными.
При определении составного ключа порядок столбцов в первичном ключе совпадает с порядком столбцов, показанным в таблице. Однако после создания первичного ключа порядок столбцов можно изменить. Дополнительные сведения см. в разделе Изменение первичных ключей.
Использование Transact-SQL
Создание первичного ключа в существующей таблице
В следующем примере создается первичный ключ для столбца TransactionID в базе данных AdventureWorks.
ALTER TABLE Production.TransactionHistoryArchive ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID PRIMARY KEY CLUSTERED (TransactionID); Создание первичного ключа в новой таблице
В следующем примере создается таблица и определяется первичный ключ для столбца TransactionID в базе данных AdventureWorks.
CREATE TABLE Production.TransactionHistoryArchive1 ( TransactionID int IDENTITY (1,1) NOT NULL , CONSTRAINT PK_TransactionHistoryArchive1_TransactionID PRIMARY KEY CLUSTERED (TransactionID) ) ; Создание первичного ключа с кластеризованным индексом в новой таблице
В следующем примере создается таблица и определяется первичный ключ для столбца CustomerID и кластеризованного индекса для TransactionID в базе данных AdventureWorks.
-- Create table to add the clustered index CREATE TABLE Production.TransactionHistoryArchive1 ( CustomerID uniqueidentifier DEFAULT NEWSEQUENTIALID() , TransactionID int IDENTITY (1,1) NOT NULL , CONSTRAINT PK_TransactionHistoryArchive1_CustomerID PRIMARY KEY NONCLUSTERED (CustomerID) ) ; -- Now add the clustered index CREATE CLUSTERED INDEX CIX_TransactionID ON Production.TransactionHistoryArchive1 (TransactionID); Дальнейшие действия
- ALTER TABLE
- CREATE TABLE
- table_constraint
Поиск первичных ключей в SQL Server
Проблема
Одним из аспектов разработки, который должны быть у всех таблиц, является первичный ключ. Первичный ключ — это основной путь входа в ваш набор данных, поэтому при доступе к вашим данным вы гарантированно затронете только одну строку данных. Наличие первичных ключей является не только хорошей особенностью проектирования, но и играет важную роль в репликации и обновлении данных, особенно когда могут быть дублированные строки данных. Так как же определить, какие таблицы имеют первичные ключи, а какие таблицы нет?
Решение
Как упоминалось выше, первичные ключи гарантируют уникальную строку данных в вашей таблице. Некоторые из аспектов проектирования первичного ключа следующие:
- может быть один или несколько столбцов
- значения столбца не могут быть нулевыми
- столбец или комбинация столбцов должны быть уникальными
- в таблице может быть только один первичный ключ
Были и другие советы, которые фокусируются на всех индексах, существующих в базе данных, но здесь мы по-другому взглянем на таблицы с первичными ключами и таблицы без первичных ключей. Для SQL 2005 и более поздних версий это довольно легко сделать с помощью представлений каталога sys.key_constraints, но в SQL 2000 получение этой информации немного загадочно.
Найдите все таблицы SQL Server в базе данных с первичным ключом
Вот сценарии, которые вы можете использовать, чтобы найти все таблицы в базе данных, которые имеют первичный ключ.
SQL Server 2005 / 2008 / 2008 R2 / 2012 / 2014 / 2016 / 2017
c . name as SchemaName ,
b . name as TableName ,
a . name as PKname
FROM sys . key_constraints a
INNER JOIN sys . tables b ON a . parent_object_id = b . OBJECT_ID
INNER JOIN sys . schemas c ON a . schema_id = c . schema_id
WHERE a . type = ‘PK’
SQL Server 2000
SELECT c . name , a . name , b . name
FROM sysobjects a
INNER JOIN sysindexes b ON a . id = b . id
INNER JOIN sysusers c ON a . uid = c . uid
WHERE ( b . status & 2048 )<> 0
Найдите все таблицы SQL Server в базе данных без первичного ключа
Вот сценарии, которые вы можете использовать, чтобы найти все таблицы в базе данных, которые не имеют первичного ключа.
SQL Server 2005 / 2008 / 2008 R2 / 2012 / 2014 / 2016 / 2017
c . name as SchemaName ,
b . name as TableName
FROM sys . tables b
INNER JOIN sys . schemas c ON b . schema_id = c . schema_id
WHERE b . type = ‘U’
AND NOT EXISTS ( SELECT a . name
FROM sys . key_constraints a
WHERE a . parent_object_id = b . OBJECT_ID
AND a . schema_id = c . schema_id
AND a . type = ‘PK’ )
SQL Server 2000
SELECT c . name , a . name
FROM sysobjects a
INNER JOIN sysusers c ON a . uid = c . uid
AND NOT EXISTS ( SELECT b . name
FROM sysindexes b
WHERE a . id = b . id
AND ( b . status & 2048 )<> 0 )
Следующие шаги
- Теперь, когда у вас есть эти запросы, найдите время, чтобы определить, где могут отсутствовать первичные ключи
- Возьмите за правило всегда создавать первичный ключ для всех ваших таблиц.
- Если у вас нет подходящего кандидата на первичный ключ в вашей таблице, посмотрите на использование столбца идентификаторов именно для этой цели.
Добавление и изменение первичного ключа таблицы в Access
Первичный ключ — это поле или набор полей со значениями, которые являются уникальными для всей таблицы. Значения ключа могут использоваться для обозначения всех записей, при этом каждая запись имеет отдельное значение ключа. Каждая таблица может содержать только один первичный ключ. Access может автоматически создавать поле первичного ключа при создании таблицы. Вы также можете самостоятельно указать поля, которые нужно использовать в качестве первичного ключа. В этой статье объясняется, как и зачем использовать первичные ключи.
Чтобы задать первичный ключ таблицы, откройте таблицу в режиме конструктора. Выберите нужное поле (или поля), а затем на ленте щелкните Ключевое поле.
Примечание: Эта статья относится только к классическим базам данных Access. В веб-приложениях Access и веб-базах данных первичный ключ для новых таблиц назначается автоматически. Несмотря на то что автоматические первичные ключи можно менять, делать это не рекомендуется.
В этой статье
- Общие сведения о первичных ключах в Access
- Определение первичного ключа в Access с помощью имеющихся полей
- Удаление первичного ключа
- Изменение первичного ключа в Access
- Дополнительные сведения
Общие сведения о первичных ключах в Access
Используя поля первичных ключей, Access быстро связывает данные из нескольких таблиц и объединяет их понятным образом. Вы можете добавить поля первичных ключей в другие таблицы, чтобы ссылаться на таблицу, которая является источником первичного ключа. В других таблицах поля называются внешними ключами. Например, поле «ИД клиента» в таблице «Клиенты» также может отображаться в таблице «Заказы». В таблице «Клиенты» оно является первичным ключом. В таблице «Заказы» оно называется внешним ключом. Проще говоря, внешний ключ — это первичный ключ другой таблицы. Дополнительные сведения см. в статье Основные сведения о создании баз данных.
1. Первичный ключ
При переносе существующих данных в базу данных в них уже может существовать поле, которое можно использовать как первичный ключ. Часто в роли первичного ключа таблицы выступает уникальный идентификационный номер, например порядковый или инвентарный номер или код. Например, в таблице «Клиенты» для каждого клиента может быть указан уникальный код клиента. Поле кода клиента является первичным ключом.
Для первичного ключа автоматически создается индекс, ускоряющий выполнение запросов и операций. Кроме того, приложение Access проверяет наличие и уникальность значений в поле первичного ключа.
При создании таблицы в режиме таблицы Access автоматически создает первичный ключ с именем «Код» и типом данных «Счетчик».
Создание приемлемого первичного ключа
Чтобы правильно выбрать первичный ключ, следует учитывать несколько характеристик.
- Ключ должен однозначно определять каждую строку.
- В нем не должно быть пустых или отсутствующих значений — он всегда содержит значение.
- Ключ крайне редко изменяется (в идеале — никогда).
Если не удается определить приемлемый ключ, создайте для него поле с типом данных «Счетчик». Поле «Счетчик» заполняется автоматически созданными значениями при первом сохранении каждой записи. Таким образом, поле «Счетчик» соответствует всем трем характеристикам приемлемого первичного ключа. Дополнительные сведения о добавлении поля «Счетчик» см. в статье Добавление поля счетчика в качестве первичного ключа.
Поле с типом данных «Счетчик» является хорошим первичным ключом.
Примеры неудачных первичных ключей
Любое поле, не имеющее одной или нескольких характеристик подходящего первичного ключа, не следует выбирать в качестве первичного ключа. Ниже представлено несколько примеров полей, которые не годятся на роль первичного ключа в таблице «Контакты», и пояснения, почему их не следует использовать.
Неподходящий первичный ключ
Может быть не уникальным и может изменяться
Адрес электронной почты
Почтовый индекс может соответствовать нескольким контактным данным
Сочетание фактов и цифр
Факты могут изменяться, тем самым усложняя работу. Если фактическая часть повторяется в виде отдельного поля, это может привести к путанице. Например, не следует соединять название города и порядковый номер (например, САМАРА0579), если название города уже указано в отдельном поле.
Номера социального страхования или ИНН
- Личные сведения запрещено указывать в государственных учреждениях и некоторых организациях.
- Некоторые люди не имеют ИНН
- На одного человека может быть зарегистрировано несколько ИНН на протяжении жизни
Составные ключи: использование сочетания нескольких полей в качестве первичного ключа
В некоторых случаях в качестве первичного ключа таблицы нужно использовать несколько полей. Например, для таблицы сведений о заказах, в которой хранятся элементы строк для заказов, в первичном ключе можно использовать два столбца: «Номер заказа» и «Артикул». Если ключ состоит из нескольких полей, он также называется составным ключом.
Определение первичного ключа в Access с помощью имеющихся полей
Для правильной работы первичного ключа поле должно однозначно определять каждую строку, не содержать пустых и отсутствующих значений и редко изменяться (в идеале — не изменяться никогда). Чтобы задать первичный ключ, выполните указанные ниже действия.
- Откройте базу данных, которую нужно изменить.
- В области навигации щелкните правой кнопкой мыши таблицу, в которой нужно определить первичный ключ, и в контекстном меню выберите пункт Конструктор.
Совет: Если область навигации не отображается, нажмите клавишу F11.
Удаление первичного ключа в Access
При удалении первичного ключа одно или несколько полей, использовавшихся ранее в качестве первичного ключа, больше не являются идентификаторами записи.
Удаление первичного ключа не приводит к удалению полей таблицы, но при этом удаляется индекс, созданный для первичного ключа.
-
Перед удалением первичного ключа необходимо убедиться, что он не используется в связях между таблицами. Если вы попытаетесь удалить первичный ключ, являющийся частью одной или нескольких связей, Access предупредит вас, что сначала необходимо удалить связи.
Чтобы удалить связь между таблицами, сделайте следующее:
- Закройте связанные таблицы, если они открыты. Связь между открытыми таблицами удалить невозможно.
- На вкладке Работа с базами данных в группе Отношения нажмите кнопку Схема данных.
- Выберите Добавить таблицы (Показать таблицу в Access 2013).
- Щелкните линию связи между таблицами, которую необходимо удалить (выделенная линия становится жирной), а затем нажмите клавишу DEL.
- На вкладке Конструктор связей в группе Связи нажмите кнопку Закрыть.
Совет: Если область навигации не отображается, нажмите клавишу F11.
Примечание: При сохранении новой таблицы без указания первичного ключа будет предложено создать этот ключ. Если нажать кнопку Да, приложение Access создаст поле «ИД» с типом данных «Счетчик» для сохранения уникального значения для каждой записи. Если в таблице уже есть поле с таким типом данных, оно будет использоваться в качестве первичного ключа.
Изменение первичного ключа в Access
Чтобы изменить первичный ключ таблицы, выполните указанные ниже действия.
- Удалите существующий первичный ключ, следуя инструкциям в разделе Удаление первичного ключа.
- Определите первичный ключ в соответствии с инструкциями в разделе Определение первичного ключа.
Дополнительные сведения
Дополнительные сведения о создании базы данных и выборе подходящего первичного ключа см. в указанных ниже статьях.
- Основные сведения о базах данных
- Структура базы данных Access
- Создание базы данных Access
- Создание таблиц в базе данных
1.2.5. Первичный и внешний ключ
Вот так вот незаметно мы подошли к очень важной теме – первичных и внешних ключей. Если первые используются почти всеми, то вторые почему-то игнорируются. А зря. Внешние ключи – это не проблема, это реальная помощь в целостности данных.
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, а на поля, которые должны быть уникальными установлено ограничение уникальности. Что выбрать? Зависит от конкретной задачи и от того, с чем вам удобнее работать.
1.2.6. Внешний ключ
Внешний ключ также является ограничением CONSTRAINT и отображает связь между двумя таблицами. Допустим, что у вас есть две таблицы:
- Names – содержит имена людей и состоит из полей идентификатора (ключевое поле), имя.
- Phones – таблица телефонов, которая состоит из идентификатора (ключевое поле), внешний ключ для связи с таблицей names и строковое поле для хранения номера телефона.
У одного человека может быть несколько телефонов, поэтому мы разделили хранение данных в разные таблицы. На рисунке 1.4 визуально показана связь между двумя таблицами. Если вы уже работали со связанными таблицами, то этого для вас будет достаточно. Если вы слышите о связях впервые, то попробуем посмотреть на проблему поближе.
Для примера возьмем таблицу из трех человек. В таблице 1.3 показано содержимое таблицы «Names». Здесь всего три строки и у каждой свой уникальный главный ключ. Для уникальности, когда будем создавать таблицу, сделаем ключ автоматически увеличиваемым полем.
Таблица 1.3 Содержимое таблицы Names
| Главный ключ | Фамилия |
| 1 | Петров |
| 2 | Иванов |
| 3 | Сидоров |
Таблица 1.4. Содержимое таблицы Phones
| Главный ключ | Внешний ключ | Телефон |
| 1 | 1 | 678689687 |
| 2 | 1 | 2324234 |
| 3 | 2 | 324234 |
| 4 | 3 | 32432423 |
| 5 | 3 | 2 |
| 6 | 3 | 12312312 |
В таблице 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. Таблица с внутренней связью
| Главный ключ | Внешний ключ | Должность |
| 1 | NULL | Генеральный директор |
| 2 | 1 | Коммерческий директор |
| 3 | 1 | Директор по общим вопросам |
| 4 | 2 | Начальник отдела снабжения |
| 5 | 2 | Начальник отдела сбыта |
| 6 | 3 | Начальник отдела кадров |
В результате мы получаем, что у генерального директора внешний ключ нулевой, т.е. эта должность стоит во главе всех остальных. У коммерческого директора и директора по общим вопросам внешний ключ указывает на строку генерального директора. Это значит, что эти две должности подчиняются непосредственно генеральному директору. И так далее.
Посмотрим, как можно создать все это в виде 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 | Иванов |
| 2 | Петров |
| 3 | Сидоров |
Таблица 1.7. Таблица телефонов
| Ключ | Телефон |
| 1 | 68768678 |
| 2 | 658756785 |
| 3 | 567575677 |
Таблица 1.8. Таблица телефонов
| Ключ | Связь с именем | Связь с телефоном |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 3 |
Давайте теперь посмотрим, какая будет логика поиска данных при связи многие ко многим. Допустим, что нам нужно найти все телефоны, которые принадлежат Иванову. У Иванова первичный ключ равен 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.