Syntax of SQL statements using constraints - 9. Transactions and

Это окончание невероятной информации про транзакции.

...

​​in PRIMARY KEY must be unique for each row of the table. Duplicate values ​​or values ​​containing NULL will be discarded. A single PRIMARY KEY constraint can be defined for a single table. In terms of the SQL standard, this is called the primary key of the table.

UNIQUE restriction . A UNIQUE constraint for a table or column means that a group of one or more columns forms a potential table key, in which null values ​​are allowed. This means that two strings containing the same and non-equal NULL values ​​are considered to violate uniqueness and are not allowed. Two strings containing NULL values are considered different and are allowed. For a single table, several UNIQUE constraints can be defined.

Remark From the point of view of the relational data model (see Chapter 3, note on the rules for the integrity of entities and foreign keys), the UNIQUE type constraint does not define a potential key, since A potential key must not contain NULL values.

FOREIGN KEY and REFERENCES restrictions . The FOREIGN KEY ... REFERENCES ... constraint for a table and the REFERENCES ... constraint for a column determine the foreign key of the table. The REFERENCES ... constraint for a column defines a simple foreign key, i.e. key consisting of one column. The FOREIGN KEY ... REFERENCES ... constraint for a table can define both a simple and a complex foreign key, i.e. key consisting of several columns of the table. The column or column group of the table referenced by the foreign key must have PRIMARY KEY or UNIQUE constraints. The columns referenced by the foreign key must have the same data type as the columns that are part of the foreign key. The table may have a link to itself. The foreign key constraint is violated if the values ​​that are present in the foreign key do not match the values ​​of the corresponding key of the parent table for one row from the parent table. Operations that violate a foreign key constraint are rejected. How should the values ​​of the foreign key and the key of the parent table coincide, and what actions should be taken when changing the keys in the parent table are described below in the reference specification.

NOT NULL constraint . The NOT NULL constraint does not allow NULL values ​​to appear in a column.

Reference Specification . Reference specification defines the characteristics of a table's foreign key.

MATCH {FULL | PARTIAL} . The MATCH FULL clause requires full matching of the values ​​of the external and primary keys. The MATCH PARTIAL clause allows for a partial match of the values ​​of the external and primary keys. The MATCH clause may also be omitted. For the case of MATCH PARTIAL, rows may appear in the child table that have foreign key values ​​that do not uniquely match the values ​​of the parent key. Those. one row of the child table may have non-unique references not several rows of the parent table. This is very different from the relational data model, and this difference is provoked by the assumption of NULL values. To consider the various options for matching the external and parent keys, consider the following example.

Example 17 Suppose there are two tables:

X	Y
one	Aa
one	Bb
2	Cc
2	Dd
3	Ee
3	Ff

Table 4 Table A (Parent)

Z	X	Y
one	one	Aa
2	one	Null
3	Null	Cc
four	Null	Null
five	four	Gg

Table 5 Table B (Subsidiary)

Table A has a primary key (X, Y). Table B has a primary key Z, and a foreign key (X, Y) that refers to the primary key of table A. The various options for matching the rows of the child table B with the rows of the parent table A are listed below:

	The columns X and Y of table B allow nulls.	The columns X and Y of table B do not allow null values.
MATCH is missing	1 row - valid, matches 1 row of table A. 2 line - valid, does not match anything. 3 line - valid, does not match anything. 4 line - valid, does not match anything. 5 line - not valid.	1 row - valid, matches 1 row of table A. 2 line - not valid. 3 line - not valid. 4 line - not valid. 5 line - not valid.
MATCH FULL	1 row - valid, matches 1 row of table A. 2 line - not valid. 3 line - not valid. 4 line - valid, does not match anything. 5 line - not valid.	1 row - valid, matches 1 row of table A. 2 line - not valid. 3 line - not valid. 4 line - not valid. 5 line - not valid.
MATCH PARTIAL	1 row - valid, matches 1 row of table A. 2 line - valid, non-uniquely coincides with 1 and 2 lines of table A. 3rd line - valid, uniquely coincides with 3rd line of table A. 4 line - valid, does not match anything. 5 line - not valid.	1 row - valid, matches 1 row of table A. 2 line - not valid. 3 line - not valid. 4 line - not valid. 5 line - not valid.

The MATCH clause is ignored if all foreign key columns have NOT NULL constraints.

Offers ON UPDATE and ON DELETE . The ON UPDATE and ON DELETE clauses define actions performed by reference. The actions performed by reference are mainly described earlier in this chapter. Difficulties in understanding how these actions are performed arise if MATCH PARTIAL is set and the columns that are part of the foreign key allow NULL values. Details of these actions, taking into account possible difficulties, are described in [9].

Attributes restrictions . The constraint attributes determine at which point the constraints are checked. A constraint can be defined as NOT DEFERRABLE (non-deferrable) or DEFERRABLE (deferred). If no constraint attributes are specified, NOT DEFERRABLE is assumed by default.

If a constraint is defined as NOT DEFERRABLE ( non-deferred ), then the constraint is always checked immediately after each INSERT, UPDATE or DELETE statement, which may violate the constraint.

If a constraint is defined as a DEFERRABLE ( postponed ), then the constraint may have two verification modes — immediately after the execution of the operation or at the end of the transaction. The verification mode can be changed at any time inside a transaction with the SET CONSTRAINTS command. When defining a constraint, you can specify the initial inspection mode INITIALLY DEFERRED (initially pending) or INITIALLY IMMEDIATE (initial immediately verifiable).

Syntax of SQL statements using constraints

The SQL standard describes the following statements in which restrictions can be used:

• CREATE DOMAIN - create domain
• ALTER DOMAIN - change domain
• DROP DOMAIN - delete domain
• CREATE TABLE - create table
• ALTER TABLE - change table
• DROP TABLE - delete table
• CREATE ASSERTION - create statement
• DROP ASSERTION - delete statement
• COMMIT WORK - commit a transaction
• SET CONSTRAINTS - set the time limit check

CREATE DOMAIN Domain Name [ AS ] Data Type

[ DEFAULT Default ]
[ Restriction name ] check restriction [ Restriction attributes ]

This statement specifies the domain on which the table columns can be determined. Because the name of the column that will be based on this domain is not known in advance, then the VALUE keyword is used in the CHECK domain constraint to refer to the value of this domain. In a specific table, the DBMS will replace the word VALUE with the name of the table column.

Example 18 The following operator creates a Salary domain based on an integer data type, and the values ​​from this domain cannot take non-positive values ​​(but can take the value NULL!). By default, this restriction is checked immediately, but it may be delayed:

  CREATE DOMAIN Salary AS integer CHECK (VALUE> 0) DEFERRABLE INITIALLY IMMEDIATE 

ALTER DOMAIN Domain Name
{ SET DEFAULT Default }
| { DROP DEFAULT }
| { ADD [ Constraint Name ] check restriction [ Constraint Attributes ]}
| { DROP CONSTRAINT Restriction Name }

This statement changes the existing domain. The standard prohibits making multiple changes with a single ALTER DOMAIN command. Those. if you want to remove the CHECK constraint and add the default value, you will have to execute two ALTER DOMAIN statements.

DROP DOMAIN Domain Name CASCADE | RESTRICT

This statement destroys an existing domain. If the RESTRICT option is specified, the domain is not destroyed if there are references to it from the columns of the tables. If the CASCADE option is specified, the following actions occur:

• The domain data type is passed to columns based on this domain.
• If the column has no default value and the default value for the domain is defined, it becomes the default value for the column.
• All domain constraints become column constraints.

CREATE TABLE Table Name
({ Column definition | [ table constraint ]}., ..)

Column definition :: =
Column Name { Domain Name | Data Type [ Size ]}
[ Column Restriction ... ]
[ DEFAULT Default ]

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

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

Example 19

 CREATE TABLE Salespeaple
(Salespeaple_Id Id_Nums PRIMARY KEY,
Fam CHAR(20) NOT NULL,
Im CHAR(15),
BirthDate DATE,
Salary Salary_Domain DEFAULT 1000,
City_Id INTEGER REFERENCES City ON UPDATE CASCADE ON DELETE RESTRICT,
District_Id INTEGER,
CONSTRAINT AltKey UNIQUE(Fam, Im, BirthDate),
CHECK (City_Id IS NOT NULL OR District_Id IS NOT NULL),
FOREIN KEY District_Id REFERENCES District ON UPDATE CASCADE ON DELETE RESTRICT)

Этот оператор создает таблицу Salespeaple с колонками (Salespeaple_Id, Fam, Im, BirthDate, Salary, City_Id, District_Id) и следующими ограничениями:

• Колонка Salespeaple_Id наследует все ограничения домена Id_Nums. Кроме того, эта колонка образует первичный ключ таблицы (следовательно, не допускает NULL-значений).
• Колонка Fam не допускает NULL-значений.
• Колонка Salary наследует все ограничения домена Salary_Domain. Кроме того, эта колонка имеет значения по умолчанию1000.
• Колонка City_Id является внешним ключом, ссылающимся на первичный ключ таблицы City. При изменении первичного ключа в таблице City соответствующие значения внешнего ключа в таблице Salespeaple будут каскадно изменены. При удалении строки из таблицы City будет выполняться проверка, нет ли ссылок на удаляемую строку из таблицы Salespeaple. Если такие ссылки найдутся, то операция удаления в таблице City будет отвергнута.
• Колонка District_Id также является внешним ключом, ссылающимся на первичный ключ таблицы District. Этот внешний ключ, в отличие от предыдущего, определен как ограничение таблицы. Действия, определенные по ссылке аналогичны предыдущим.
• Колонки (Fam, Im, BirthDate) образуют альтернативный ключ таблицы. Это ограничение имеет наименование AltKey.
• Колонки City_Id и District_Id не могут одновременно принимать NULL-значения (хотя каждая из них по отдельности допускает использование NULL-значений).

ALTER TABLE Имя таблицы
{ ADD [ COLUMN ] Определение столбца }
| { ALTER [ COLUMN ] Имя столбца { SET DEFAULT Значение по умолчанию | DROP DEFAULT }}
| { DROP [ COLUMN ] Имя столбца RESTRICT | CASCADE }
| { ADD Ограничение таблицы }
| { DROP CONSTRAINT Имя ограничения RESTRICT | CASCADE }

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

DROP TABLE Имя таблицы CASCADE | RESTRICT

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

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

Если указан параметр CASCADE, то удаляются также и все объекты, ссылающиеся на эту таблицу.

CREATE ASSERTION Имя утверждения
Ограничение check
[ Атрибуты ограничения ]

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

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

Example 20

 CREATE ASSERTION Check_Pay CHECK (Salespeaple.Salary IS NOT NULL) OR
 (Salespeaple.Commission IS NOT NULL) DEFERRABLE INITIALLY IMMEDIATE 

DROP ASSERTION Имя утверждения

Этот оператор позволяет удалять имеющееся утверждение.

COMMIT WORK

Этот оператор фиксирует транзакцию. При этом проверяются все отложенные до конца транзакции ограничения. Если одно из ограничений не выполняется, то транзакция откатывается.

SET CONSTRAINT { Имя ограничения .,.. | ALL }
{ DEFERRED | IMMEDIATE }

Этот оператор позволяет во время выполнения транзакции менять момент проверки всех (ALL) или некоторых ограничений. Этот оператор действует только на ограничения, определенные как DEFERRABLE (потенциально откладываемые). Если ограничение A находилось в состоянии IMMEDIATE (немедленно проверяемое), то оператор SET CONSTRAINT A DEFERRED переводит его в состояние DEFERRED (с отложенной проверкой) и тогда все операции, потенциально могущие нарушить это ограничение, будут выполняться без проверки. Проверка будет произведена в конце транзакции или в момент подачи команды SET CONSTRAINT A IMMEDIATE.

findings

Транзакция - это неделимая, с точки зрения воздействия на СУБД, последовательность операций манипулирования данными, выполняющаяся по принципу " все или ничего ", и переводящая базу данных из одного целостного состояния в другое целостное состояние.

Транзакция обладает четырьмя важными свойствами, известными как свойства АСИД :

• (А) Атомарность .
• (С) Согласованность .
• (И) Изоляция .
• (Д) Долговечность .

База данных находится в согласованном состоянии , если для этого состояния выполнены все ограничения целостности .

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

Ограничения целостности классифицируются несколькими способами:

• По способам реализации .
• По времени проверки .
• По области действия .

По способам реализации различают:

• Декларативную поддержку ограничений целостности - средствами языка определения данных (DDL).
• Процедурную поддержку ограничений целостности - посредством триггеров и хранимых процедур.

По времени проверки ограничения делятся на:

• Немедленно проверяемые ограничения .
• Ограничения с отложенной проверкой .

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

• Ограничения домена .
• Ограничения атрибута .
• Ограничения кортежа .
• Ограничения отношения .
• Ограничения базы данных .

Стандарт языка SQL поддерживает только декларативные ограничения целостности, реализуемые как:

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

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

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

Часть 1 9. Transactions and database integrity
Часть 2 Syntax of SQL statements using constraints - 9. Transactions and

See also

• Briefly about relational databases
• 10.1. Transactions and Database Integrity