Cascade sql что это
Типы данных сами по себе ограничивают множество данных, которые можно сохранить в таблице. Однако для многих приложений такие ограничения слишком грубые. Например, столбец, содержащий цену продукта, должен, вероятно, принимать только положительные значения. Но такого стандартного типа данных нет. Возможно, вы также захотите ограничить данные столбца по отношению к другим столбцам или строкам. Например, в таблице с информацией о товаре должна быть только одна строка с определённым кодом товара.
Для решения подобных задач SQL позволяет вам определять ограничения для столбцов и таблиц. Ограничения дают вам возможность управлять данными в таблицах так, как вы захотите. Если пользователь попытается сохранить в столбце значение, нарушающее ограничения, возникнет ошибка. Ограничения будут действовать, даже если это значение по умолчанию.
5.3.1. Ограничения-проверки
Ограничение-проверка — наиболее общий тип ограничений. В его определении вы можете указать, что значение данного столбца должно удовлетворять логическому выражению (проверке истинности). Например, цену товара можно ограничить положительными значениями так:
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) );
Как вы видите, ограничение определяется после типа данных, как и значение по умолчанию. Значения по умолчанию и ограничения могут указываться в любом порядке. Ограничение-проверка состоит из ключевого слова CHECK , за которым идёт выражение в скобках. Это выражение должно включать столбец, для которого задаётся ограничение, иначе оно не имеет большого смысла.
Вы можете также присвоить ограничению отдельное имя. Это улучшит сообщения об ошибках и позволит вам ссылаться на это ограничение, когда вам понадобится изменить его. Сделать это можно так:
CREATE TABLE products ( product_no integer, name text, price numeric 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) );
Первые два ограничения определяются похожим образом, но для третьего используется новый синтаксис. Оно не связано с определённым столбцом, а представлено отдельным элементом в списке. Определения столбцов и такие определения ограничений можно переставлять в произвольном порядке.
Про первые два ограничения можно сказать, что это ограничения столбцов, тогда как третье является ограничением таблицы, так как оно написано отдельно от определений столбцов. Ограничения столбцов также можно записать в виде ограничений таблицы, тогда как обратное не всегда возможно, так как подразумевается, что ограничение столбца ссылается только на связанный столбец. (Хотя PostgreSQL этого не требует, но для совместимости с другими СУБД лучше следовать это правилу.) Ранее приведённый пример можно переписать и так:
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, описанное в следующем разделе.
Примечание
PostgreSQL не поддерживает ограничения CHECK , которые обращаются к данным, не относящимся к новой или изменённой строке. Хотя ограничение CHECK , нарушающее это правило, может работать в простых случаях, в общем случае нельзя гарантировать, что база данных не придёт в состояние, когда условие ограничения окажется ложным (вследствие последующих изменений других участвующих в его вычислении строк). В результате восстановление выгруженных данных может оказаться невозможным. Во время восстановления возможен сбой, даже если полное состояние базы данных согласуется с условием ограничения, по причине того, что строки загружаются не в том порядке, в котором это условие будет соблюдаться. Поэтому для определения ограничений, затрагивающих другие строки и другие таблицы, используйте ограничения UNIQUE , EXCLUDE или FOREIGN KEY , если это возможно.
Если вам не нужна постоянно поддерживаемая гарантия целостности, а достаточно разовой проверки добавляемой строки по отношению к другим строкам, вы можете реализовать эту проверку в собственном триггере. (Этот подход исключает вышеописанные проблемы при восстановлении, так как в выгрузке pg_dump триггеры воссоздаются после перезагрузки данных, и поэтому эта проверка не будет действовать в процессе восстановления.)
Примечание
В PostgreSQL предполагается, что условия ограничений CHECK являются постоянными, то есть при одинаковых данных в строке они всегда выдают одинаковый результат. Именно этим предположением оправдывается то, что ограничения CHECK проверяются только при добавлении или изменении строк, а не при каждом обращении к ним. (Приведённое выше предупреждение о недопустимости обращений к другим таблицам является частным следствием этого предположения.)
Однако это предположение может нарушаться, как часто бывает, когда в выражении CHECK используется пользовательская функция, поведение которой впоследствии меняется. PostgreSQL не запрещает этого, и если строки в таблице перестанут удовлетворять ограничению CHECK , это останется незамеченным. В итоге при попытке загрузить выгруженные позже данные могут возникнуть проблемы. Поэтому подобные изменения рекомендуется осуществлять следующим образом: удалить ограничение (используя ALTER TABLE ), изменить определение функции, а затем пересоздать ограничение той же командой, которая при этом перепроверит все строки таблицы.
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) , но в PostgreSQL явное ограничение 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 и использовать его в переносимых приложениях не следует. (Оно было добавлено в PostgreSQL только для совместимости с некоторыми другими СУБД.) Однако некоторые пользователи любят его использовать, так как оно позволяет легко переключать ограничения в скрипте. Например, вы можете начать с:
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, name text, price numeric );
в виде ограничения столбца и так:
CREATE TABLE products ( product_no integer, name text, price numeric, UNIQUE (product_no) );
в виде ограничения таблицы.
Чтобы определить ограничение уникальности для группы столбцов, запишите его в виде ограничения таблицы, перечислив имена столбцов через запятую:
CREATE TABLE example ( a integer, b integer, c integer, 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, которые функционально почти равнозначны первичным ключам, может быть сколько угодно, но назначить ограничением первичного ключа можно только одно.) Теория реляционных баз данных говорит, что первичный ключ должен быть в каждой таблице. В PostgreSQL такого жёсткого требования нет, но обычно лучше ему следовать.
Первичные ключи полезны и для документирования, и для клиентских приложений. Например, графическому приложению с возможностями редактирования содержимого таблицы, вероятно, потребуется знать первичный ключ таблицы, чтобы однозначно идентифицировать её строки. Первичные ключи находят и другое применение в СУБД; в частности, первичный ключ в таблице определяет целевые столбцы по умолчанию для сторонних ключей, ссылающихся на эту таблицу.
5.3.5. Внешние ключи
Ограничение внешнего ключа указывает, что значения столбца (или группы столбцов) должны соответствовать значениям в некоторой строке другой таблицы. Это называется ссылочной целостностью двух связанных таблиц.
Пусть у вас уже есть таблица продуктов, которую мы неоднократно использовали ранее:
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric );
Давайте предположим, что у вас есть таблица с заказами этих продуктов. Мы хотим, чтобы в таблице заказов содержались только заказы действительно существующих продуктов. Поэтому мы определим в ней ограничение внешнего ключа, ссылающееся на таблицу продуктов:
CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer 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 для зависимых столбцов потребуют просканировать подчинённую таблицу и найти строки, ссылающиеся на старые значения, полезно будет иметь индекс и для подчинённых столбцов. Но это нужно не всегда, и создать соответствующий индекс можно по-разному, поэтому объявление внешнего ключа не создаёт автоматически индекс по связанным столбцам.
Подробнее об изменении и удалении данных рассказывается в Главе 6. Вы также можете подробнее узнать о синтаксисе ограничений внешнего ключа в справке CREATE TABLE .
5.3.6. Ограничения-исключения
Ограничения-исключения гарантируют, что при сравнении любых двух строк по указанным столбцам или выражениям с помощью заданных операторов, минимум одно из этих сравнений возвратит false или NULL. Записывается это так:
CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) );
При добавлении ограничения-исключения будет автоматически создан индекс того типа, который указан в объявлении ограничения.
| Пред. | Наверх | След. |
| 5.2. Значения по умолчанию | Начало | 5.4. Системные столбцы |
Cascade sql что это
Внешние ключи применяются для установки связи между таблицами. Внешний ключ устанавливается для столбцов из зависимой, подчиненной таблицы, и указывает на один из столбцов из главной таблицы. Хотя, как правило, внешний ключ указывает на первичный ключ из связанной главной таблицы, но это необязательно должно быть непременным условием. Внешний ключ также может указывать на какой-то другой столбец, который имеет уникальное значение.
Общий синтаксис установки внешнего ключа на уровне столбца:
[FOREIGN KEY] REFERENCES главная_таблица (столбец_главной_таблицы) [ON DELETE ] [ON UPDATE ]
Для создания ограничения внешнего ключа на уровне столбца после ключевого слова REFERENCES указывается имя связанной таблицы и в круглых скобках имя связанного столбца, на который будет указывать внешний ключ. Также обычно добавляются ключевые слова FOREIGN KEY , но в принципе их необязательно указывать. После выражения REFERENCES идет выражение ON DELETE и ON UPDATE .
Общий синтаксис установки внешнего ключа на уровне таблицы:
FOREIGN KEY (стобец1, столбец2, . столбецN) REFERENCES главная_таблица (столбец_главной_таблицы1, столбец_главной_таблицы2, . столбец_главной_таблицыN) [ON DELETE ] [ON UPDATE ]
Например, определим две таблицы и свяжем их посредством внешнего ключа:
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE ); CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT REFERENCES Customers (Id), CreatedAt Date );
Здесь определены таблицы Customers и Orders. Customers является главной и представляет клиента. Orders является зависимой и представляет заказ, сделанный клиентом. Эта таблица через столбец CustomerId связана с таблицей Customers и ее столбцом Id. То есть столбец CustomerId является внешним ключом, который указывает на столбец Id из таблицы Customers.
Определение внешнего ключа на уровне таблицы выглядело бы следующим образом:
CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );
С помощью оператора CONSTRAINT можно задать имя для ограничения внешнего ключа. Обычно это имя начинается с префикса «FK_»:
CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, CONSTRAINT FK_Orders_To_Customers FOREIGN KEY (CustomerId) REFERENCES Customers (Id) );
В данном случае ограничение внешнего ключа CustomerId называется «FK_Orders_To_Customers».
ON DELETE и ON UPDATE
С помощью выражений ON DELETE и ON UPDATE можно установить действия, которые выполняться соответственно при удалении и изменении связанной строки из главной таблицы. И для определения действия мы можем использовать следующие опции:
- CASCADE : автоматически удаляет или изменяет строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице.
- NO ACTION : предотвращает какие-либо действия в зависимой таблице при удалении или изменении связанных строк в главной таблице. То есть фактически какие-либо действия отсутствуют.
- SET NULL : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение NULL.
- SET DEFAULT : при удалении связанной строки из главной таблицы устанавливает для столбца внешнего ключа значение по умолчанию, которое задается с помощью атрибуты DEFAULT. Если для столбца не задано значение по умолчанию, то в качестве него применяется значение NULL.
Каскадное удаление
По умолчанию, если на строку из главной таблицы по внешнему ключу ссылается какая-либо строка из зависимой таблицы, то мы не сможем удалить эту строку из главной таблицы. Вначале нам необходимо будет удалить все связанные строки из зависимой таблицы. И если при удалении строки из главной таблицы необходимо, чтобы были удалены все связанные строки из зависимой таблицы, то применяется каскадное удаление, то есть опция CASCADE :
CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, 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 IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET NULL );
Установка значения по умолчанию
CREATE TABLE Orders ( Id INT PRIMARY KEY IDENTITY, CustomerId INT, CreatedAt Date, FOREIGN KEY (CustomerId) REFERENCES Customers (Id) ON DELETE SET DEFAULT )
Create Foreign Keys with cascade delete SQL Server
Внешний ключ с каскадным удалением означает, что если запись в родительской таблице будет удалена, то соответствующие записи в дочерней таблице будут автоматически удалены. Это называется каскадным удалением в SQL Server.
Внешний ключ с каскадным удалением может быть создан с использованием оператора CREATE TABLE или оператора ALTER TABLE.
Создание внешнего ключа с каскадным удалением — использование оператора CREATE TABLE
Синтаксис
Синтаксис создания внешнего ключа с каскадным удалением с использованием оператора CREATE TABLE в SQL Server (Transact-SQL):
CREATE TABLE child_table
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
.
CONSTRAINT fk_name
FOREIGN KEY (child_col1, child_col2, . child_col_n)
REFERENCES parent_table (parent_col1, parent_col2, . parent_col_n)
ON DELETE CASCADE
[ ON UPDATE < NO ACTION | CASCADE | SET NULL | SET DEFAULT >]
);
child_table — имя дочерней таблицы, которую вы хотите создать.
column1 , column2 — столбцы, которые вы хотите создать в таблице. Каждый столбец должен иметь тип данных. Столбец должен быть определен как NULL или NOT NULL, и если это значение остается пустым, база данных принимает значение NULL как значение по умолчанию.
fk_name — имя ограничения внешнего ключа, которое вы хотите создать.
child_col1 , child_col2 , . child_col_n — столбцы в child_table , которые будут ссылаться на первичный ключ в parent_table (родительской таблице).
parent_table — имя родительской таблицы, первичный ключ которой будет использоваться в child_table .
parent_col1 , parent_col2 , . parent_col3 — столбцы, которые составляют первичный ключ в родительской таблице. Внешний ключ будет обеспечивать связь между этими данными и столбцами child_col1 , child_col2 , . child_col_n в child_table .
ON DELETE CASCADE — указывает, что дочерние данные удаляются при удалении родительских данных.
ON UPDATE — необязательный. Он указывает, что делать с дочерними данными при обновлении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.
NO ACTION — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что никакие действия не выполняются с дочерними данными при удалении или обновлении родительских данных.
CASCADE — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные либо удаляются, либо обновляются, когда родительские данные удаляются или обновляются.
SET NULL — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные установлены в NULL, когда родительские данные удаляются или обновляются.
SET DEFAULT — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные устанавливаются в значения по умолчанию, когда родительские данные удаляются или обновляются.
Пример
Давайте рассмотрим пример создания внешнего ключа с каскадным удалением в SQL Server (Transact-SQL) с помощью оператора CREATE TABLE.
Например:
Как создать первичные и внешние ключи MySQL
В этой инструкции рассказываем про первичный и внешний ключи SQL, зачем они нужны и как их создать различными способами.
Эта инструкция — часть курса «MySQL для новичков».
Смотреть весь курс
Введение
Работа современных приложений и сложных сайтов, которыми мы привыкли пользоваться, невозможна без баз данных. Поэтому правильно настроенные БД важны для приложения не меньше, чем их дизайн и код, который с этой базой взаимодействует. Работа современных реляционных баз данных сложно представить без корректно настроенных первичных и внешних ключей. В этой инструкции мы подробно разберем создание этих ключей MySQL, расскажем для чего они нужны и как их использовать.
Облачные базы данных
Что такое первичный и внешний ключи и зачем они нужны
Начнем рассмотрение данного вопроса с двух самых главных элементов: первичного и внешнего ключей.
Первичный ключ или primary key
Первичный ключ — особенное поле в SQL-таблице, которое позволяет однозначно идентифицировать каждую запись в ней. Как правило, эти поля используются для хранения уникальных идентификаторов объектов, которые перечислены в таблице, например, это может быть ID клиента или товара.
Первичный ключ имеет несколько свойств:
- каждая запись в таком поле должна быть уникальной;
- запись в поле не должна быть пустой;
- в одной таблице может быть только один ключ (существуют также составные ключи, которые могут включать в себя несколько полей, однако в этой статье мы не будем их рассматривать).
Внешний ключ или foreign key
Внешний ключ нужен для того, чтобы связать две разные SQL-таблицы между собой. Внешний ключ таблицы должен соответствует значению первичного ключа таблицы, с которой он связан. Это помогает сохранять согласованность базы данных путем обеспечения так называемой «ссылочной целостности» (referential integrity).
Давайте рассмотрим все это на примере простой базы данных. Для начала нам понадобится создать базу данных. Заходим в MySQL и создаем базу данных, в которой будут храниться записи о книгах в библиотеке:
create database slcbookshelf; 
Так как дальше мы будем работать с этой базой, вводим команду:
use slcbookshelf; И создаем таблицу, в которой будут храниться записи о книгах в библиотеке:
CREATE TABLE books ( book_id INT NOT NULL, book_name VARCHAR(255) NOT NULL, book_category VARCHAR(255)); Создание первичного ключа при создании таблицы и с помощью ALTER TABLE
В созданной выше таблице ни одно из полей не является первичным ключом. Увидеть это мы можем, выполнив команду:
DESC books; Вывод команды будет выглядеть следующим образом:
mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Первичный ключ при создании таблицы
Вы можете также создать его при создании таблицы, добавив в команду для создания таблицы следующую запись:
PRIMARY KEY (book_id) В таком случае поле book_id после создания таблицы будет являться первичным ключом для таблицы books.
Создание первичного ключа при помощи ALTER TABLE
Если таблица уже создана, а первичный ключ в ней не указан, вы можете создать ключевое поле, с помощью команды ALTER TABLE. Команда ALTER TABLE помогает изменять уже существующие столбцы, удалять их или добавлять новые. Чтобы определить первичный ключ в поле book_id, выполните команду:
ALTER TABLE books ADD PRIMARY KEY (book_id); Проверяем: mysql> DESC books; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | book_id | int | NO | PRI | NULL | | | book_name | varchar(255) | NO | | NULL | | | book_category | varchar(255) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) Установка внешнего ключа MySQL при создании таблицы и с помощью ALTER TABLE
Предположим, у нас есть еще одна таблица под названием authors, которую нам необходимо связать с текущей таблицей books с помощью внешнего ключа author_id.
Внешний ключ при создании таблицы
Для того, чтобы привязать к таблице внешний ключ сразу при создании таблицы, вам необходимо дополнить запрос, которым вы создаете таблицу следующей записью:
FOREIGN KEY (author_id) REFERENCES authors(author_id) В итоге запрос, которым создается такая таблица будет выглядеть следующим образом:
CREATE TABLE books ( book_id INT NOT NULL, book_name VARCHAR(255) NOT NULL, book_category VARCHAR(255), FOREIGN KEY (author_id) REFERENCES authors(author_id)); Создание внешнего ключа при помощи ALTER TABLE
Если вам нужно определить поле уже созданной таблицы в качестве внешнего ключа, вы можете воспользоваться командой ALTER TABLE и создать внешний ключ в таблице командой:
ALTER TABLE books ADD FOREIGN KEY (author_id) REFERENCES authors(author_id); Сценарии использования внешнего ключа
Внешний ключ является очень важным и мощным инструментом в работе современных баз данных. С помощью внешнего ключа, например, вы можете настроить параметры, которые зададут действия при удалении или обновлении строки в таблице, настроить каскадное удаление или запретить изменять строку в таблице. Давайте рассмотрим на примерах.
Каскадное удаление или 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. При попытке изменить значение, записанное в поле первичного ключа, изменение будет применено к внешнему ключу, связанному с данным полем. Этот метод используется крайне редко, так как первичные ключи практически не являются изменяемыми полями.
RESTRICT
Опция RESTRICT позволяет отклонять все запросы на изменение или удаление строк в главной таблице при условии, что в связанной таблице также имеются строки. Задается данное ограничение следующим запросом:
CREATE TABLE Orders( Id INT PRIMARY KEY AUTO_INCREMENT, Customer_Id INT, CreatedAt Date, FOREIGN KEY (Customer_Id) REFERENCES Customers (Id) ON DELETE RESTRICT); Заключение
В данной статье мы рассмотрели что такое первичный и внешний ключи SQL, зачем они нужны и как их создать различными способами: при создании таблицы или при помощи ALTER TABLE. Также мы рассмотрели несколько сценариев использования внешнего ключа для управления таблицами.
Как установить и использовать MySQL Workbench