Что такое primary key mysql. Внешние ключи FOREIGN KEY. Многие ко многим

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), снимите флажок По умолчанию в области

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

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

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

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

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

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

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

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

ALTER TABLE CUSTOMERS ADD PRIMARY KEY (ID);

Примечание

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

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

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

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

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

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

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

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

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

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

5.3.1. Ограничения-проверки

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

Name text, price numeric CHECK (price > 0) );

Как вы видите, ограничение определяется после типа данных, как и значение по умолчанию. Значения по умолчанию и ограничения могут указываться в любом порядке. Ограничение-проверка состоит из ключевого слова CHECK , за которым идёт выражение в скобках. Это выражение должно включать столбец, для которого задаётся ограничение, иначе оно не имеет большого смысла.

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

CONSTRAINT positive_price CHECK (price > 0));

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

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

CREATE TABLE products (product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) );

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

Про первые два ограничения можно сказать, что это ограничения столбцов, тогда как третье является ограничением таблицы, так как оно написано отдельно от определений столбцов. Ограничения столбцов также можно записать в виде ограничений таблицы, тогда как обратное не всегда возможно, так как подразумевается, что ограничение столбца ссылается только на связанный столбец. (Хотя Postgres Pro этого не требует, но для совместимости с другими СУБД лучше следовать это правилу.) Ранее приведённый пример можно переписать и так:

CREATE TABLE products (product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CHECK (price > discounted_price));

Или даже так:

CREATE TABLE products (product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0 AND price > discounted_price));

Это дело вкуса.

Ограничениям таблицы можно присваивать имена так же, как и ограничениям столбцов:

CREATE TABLE products (product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CONSTRAINT valid_discount CHECK (price > discounted_price));

Следует заметить, что ограничение-проверка удовлетворяется, если выражение принимает значение true или NULL. Так как результатом многих выражений с операндами NULL будет значение NULL, такие ограничения не будут препятствовать записи NULL в связанные столбцы. Чтобы гарантировать, что столбец не содержит значения NULL, можно использовать ограничение NOT NULL, описанное в следующем разделе.

5.3.2. Ограничения NOT NULL

Ограничение NOT NULL просто указывает, что столбцу нельзя присваивать значение NULL. Пример синтаксиса:

CREATE TABLE products (product_no integer NOT NULL , name text NOT NULL , price numeric);

Ограничение NOT NULL всегда записывается как ограничение столбца и функционально эквивалентно ограничению CHECK ( имя_столбца IS NOT NULL) , но в Postgres Pro явное ограничение NOT NULL работает более эффективно. Хотя у такой записи есть недостаток - назначить имя таким ограничениям нельзя.

Естественно, для столбца можно определить больше одного ограничения. Для этого их нужно просто указать одно за другим:

CREATE TABLE products (product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0));

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

Для ограничения NOT NULL есть и обратное: ограничение NULL . Оно не означает, что столбец должен иметь только значение NULL, что конечно было бы бессмысленно. Суть же его в простом указании, что столбец может иметь значение NULL (это поведение по умолчанию). Ограничение NULL отсутствует в стандарте SQL и использовать его в переносимых приложениях не следует. (Оно было добавлено в Postgres Pro только для совместимости с некоторыми другими СУБД.) Однако некоторые пользователи любят его использовать, так как оно позволяет легко переключать ограничения в скрипте. Например, вы можете начать с:

CREATE TABLE products (product_no integer NULL, name text NULL, price numeric NULL);

и затем вставить ключевое слово NOT , где потребуется.

Подсказка

При проектировании баз данных чаще всего большинство столбцов должны быть помечены как NOT NULL.

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

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

CREATE TABLE products (product_no integer UNIQUE

в виде ограничения столбца и так:

CREATE TABLE products (product_no integer, name text, price numeric, UNIQUE (product_no) );

в виде ограничения таблицы.

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

UNIQUE (a, c) );

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

Вы можете назначить уникальному ограничению имя обычным образом:

CREATE TABLE products (product_no integer CONSTRAINT must_be_different UNIQUE, name text, price numeric);

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

Вообще говоря, ограничение уникальности нарушается, если в таблице оказывается несколько строк, у которых совпадают значения всех столбцов, включённых в ограничение. Однако два значения NULL при сравнении никогда не считаются равными. Это означает, что даже при наличии ограничения уникальности в таблице можно сохранить строки с дублирующимися значениями, если они содержат NULL в одном или нескольких столбцах ограничения. Это поведение соответствует стандарту SQL, но мы слышали о СУБД, которые ведут себя по-другому. Имейте в виду эту особенность, разрабатывая переносимые приложения.

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

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

CREATE TABLE products (product_no integer UNIQUE NOT NULL, name text, price numeric); CREATE TABLE products (product_no integer PRIMARY KEY , name text, price numeric);

Первичные ключи могут включать несколько столбцов; синтаксис похож на запись ограничений уникальности:

CREATE TABLE example (a integer, b integer, c integer, PRIMARY KEY (a, c) );

При добавлении первичного ключа автоматически создаётся уникальный индекс-B-дерево для столбца или группы столбцов, перечисленных в первичном ключе, и данные столбцы помечаются как NOT NULL .

Таблица может иметь максимум один первичный ключ. (Ограничений уникальности и ограничений NOT NULL, которые функционально почти равнозначны первичным ключам, может быть сколько угодно, но назначить ограничением первичного ключа можно только одно.) Теория реляционных баз данных говорит, что первичный ключ должен быть в каждой таблице. В Postgres Pro такого жёсткого требования нет, но обычно лучше ему следовать.

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

5.3.5. Внешние ключи

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

Пусть у вас уже есть таблица продуктов, которую мы неоднократно использовали ранее:

CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric);

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

REFERENCES products (product_no) , quantity integer);

С таким ограничением создать заказ со значением product_no , отсутствующим в таблице products (и не равным NULL), будет невозможно.

В такой схеме таблицу orders называют подчинённой таблицей, а products - главной . Соответственно, столбцы называют так же подчинённым и главным (или ссылающимся и целевым).

Предыдущую команду можно сократить так:

CREATE TABLE orders (order_id integer PRIMARY KEY, product_no integer REFERENCES products , quantity integer);

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

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

CREATE TABLE t1 (a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );

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

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

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

CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric); CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text, ...); CREATE TABLE order_items (product_no integer REFERENCES products, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (product_no, order_id));

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

Мы знаем, что внешние ключи запрещают создание заказов, не относящихся ни к одному продукту. Но что делать, если после создания заказов с определённым продуктом мы захотим удалить его? SQL справится с этой ситуацией. Интуиция подсказывает следующие варианты поведения:

Запретить удаление продукта

Удалить также связанные заказы

Что-то ещё?

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

CREATE TABLE products (product_no integer PRIMARY KEY, name text, price numeric); CREATE TABLE orders (order_id integer PRIMARY KEY, shipping_address text, ...); CREATE TABLE order_items (product_no integer REFERENCES products ON DELETE RESTRICT , order_id integer REFERENCES orders ON DELETE CASCADE , quantity integer, PRIMARY KEY (product_no, order_id));

Ограничивающие и каскадные удаления - два наиболее распространённых варианта. RESTRICT предотвращает удаление связанной строки. NO ACTION означает, что если зависимые строки продолжают существовать при проверке ограничения, возникает ошибка (это поведение по умолчанию). (Главным отличием этих двух вариантов является то, что NO ACTION позволяет отложить проверку в процессе транзакции, а RESTRICT - нет.) CASCADE указывает, что при удалении связанных строк зависимые от них будут так же автоматически удалены. Есть ещё два варианта: SET NULL и SET DEFAULT . При удалении связанных строк они назначают зависимым столбцам в подчинённой таблице значения NULL или значения по умолчанию, соответственно. Заметьте, что это не будет основанием для нарушения ограничений. Например, если в качестве действия задано SET DEFAULT , но значение по умолчанию не удовлетворяет ограничению внешнего ключа, операция закончится ошибкой.

Аналогично указанию ON DELETE существует ON UPDATE , которое срабатывает при изменении заданного столбца. При этом возможные действия те же, а CASCADE в данном случае означает, что изменённые значения связанных столбцов будут скопированы в зависимые строки.

Обычно зависимая строка не должна удовлетворять ограничению внешнего ключа, если один из связанных столбцов содержит NULL. Если в объявление внешнего ключа добавлено MATCH FULL , строка будет удовлетворять ограничению, только если все связанные столбцы равны NULL (то есть при разных значениях (NULL и не NULL) гарантируется невыполнение ограничения MATCH FULL). Если вы хотите, чтобы зависимые строки не могли избежать и этого ограничения, объявите связанные столбцы как NOT NULL .

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

Последнее обновление: 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

С помощью выражений 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 используется редко.

Установка NULL

При установки для внешнего ключа опции 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);

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

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.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.