Vincoli di database in Firebird e InterBase
NOTICE: Этот документ является главой из книги “Мир InterBase”, написанной Алексеем Ковязиным и Сергом Востриковым.
Эта глава посвящена ограничениям баз данных InterBase и Firebird. Ограничения базы данных - это правила, определяющие взаимосвязи между таблицами, которые могут проверять и изменять данные в базе данных. Эти правила реализованы в виде специальных объектов базы данных. Основное преимущество использования ограничений заключается в возможности реализовать проверку данных и часть бизнес-логики приложения на уровне базы данных, то есть централизовать и упростить её, что делает разработку приложений баз данных более лёгкой и надёжной.
Начинающие разработчики часто пренебрегают использованием ограничений базы данных, считая, что они мешают творческой работе. Однако на самом деле такое мнение формируется из-за недостаточного знания теории и практики проектирования баз данных.
В то же время наиболее опытные проектировщики решаются отказаться от использования некоторых типов ограничений, благодаря чему их приложения выигрывают в скорости. Опыт экспертов-проектировщиков позволяет им очень хорошо понимать работу сервера и точно предсказывать его поведение в сложных случаях, поэтому начинающим программистам InterBase лучше не прибегать к подобным действиям опытных коллег.
В рамках этой книги мы не рассматриваем проектирование баз данных, поэтому для получения дополнительной информации по этому вопросу обратитесь к списку литературы в конце книги. Здесь мы рассмотрим только все типы ограничений в базе данных InterBase и примеры их применения.
Типы ограничений в базе данных
В базе данных InterBase существуют следующие типы ограничений:
- PRIMARY KEY;
- UNIQUE KEY;
- FOREIGN KEY
- могут включать автоматические триггеры - ON UPDATE и ON DELETE;
- CHECK
В предыдущих главах мы упоминали некоторые из этих ограничений, поскольку это было необходимо для логического изложения материала, но теперь мы рассмотрим их синтаксис, применение и реализацию более подробно. Ограничения базы данных бывают двух типов - основанные на одном поле и основанные на нескольких полях таблицы. Синтаксис обоих типов ограничений приведён ниже.
= [CONSTRAINT constraint]
[ …]
= {UNIQUE | PRIMARY KEY
| CHECK ( )
| REFERENCES other_table [( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
}
Синтаксис ограничений, основанных на нескольких полях, следующий:
= [CONSTRAINT constraint]
[< tconstraint> …]
= {{PRIMARY KEY | UNIQUE} ( col [, col …])
| FOREIGN KEY ( col [, col …]) REFERENCES other_table[( other_col [, other_col …])]
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
| CHECK ( )}
Разница в синтаксисе между ограничениями, основанными на одном поле и на нескольких полях, очевидна - в последнем случае мы можем указать несколько полей, включённых в ограничение. В случае ограничения, основанного на одном поле, все описанные параметры относятся только к текущему полю. Конечно, эти два типа ограничений имеют разный способ применения: ограничения, основанные на одном поле, просто добавляются к определению требуемого поля, а ограничения, основанные на нескольких полях, указываются после запятой в общем определении таблицы. Подробные примеры приведены в следующих частях этой главы.
Пример типичного ограничения
На самом деле ограничения, основанные на одном поле, являются частным случаем ограничений, основанных на нескольких полях.
Пример создания ограничения первичного ключа с использованием этих двух различных подходов приведён ниже. Давайте создадим таблицу, содержащую только одно поле, и установим для неё ограничение первичного ключа.
Вот пример первичного ключа с использованием синтаксиса ограничения, основанного на одном поле:
CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); В этом примере для поля ID_PK создаётся первичный ключ с именем pktest. В результате мы получаем довольно компактное описание в одну строку. Для той же цели мы можем использовать синтаксис ограничений, основанных на нескольких полях: CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));
Создание ограничений
Рассмотрим создание ограничений более подробно. Первым в описании общего синтаксиса ограничений идёт параметр [CONSTRAINT constraint]. Как вы можете видеть, этот параметр заключён в квадратные скобки, то есть является необязательным.
Используя этот параметр, вы можете задать имя создаваемому ограничению как в случае применения синтаксиса ограничений, основанных на одном поле, так и в случае ограничений, основанных на нескольких полях. Если вы не указали имя для ограничения, InterBase автоматически сгенерирует его. Тем не менее, лучше задавать имя создаваемому ограничению, чтобы улучшить читаемость схемы базы данных и упростить управление ограничениями в дальнейшем.
Задав имя ограничению, следует определить его тип. Рассмотрим различные типы ограничений в том порядке, в котором они указаны в описании общего синтаксиса ограничений.
Первичные и уникальные ключи
Первичные ключи - один из основных типов ограничений базы данных. Они применяются для однозначной идентификации записей в таблице. Предположим, что мы храним список людей в базе данных. Вполне возможно, что будут два (или более) человека с одинаковыми фамилией, именем и отчеством. Как мы можем отличить одного человека от другого (конечно, вопрос состоит в том, чтобы отличить одного человека от другого согласно информации, хранящейся в базе данных)?
В этом случае «человек» представлен одной записью в таблице, поэтому мы можем задать более общий вопрос - как мы можем отличить одну запись в (любой) таблице от другой записи в той же таблице. Для этой цели используются ограничения - первичные ключи. Первичный ключ представляет собой одно или несколько полей в таблице, комбинация которых уникальна для каждой записи. В таблице не существует повторяющихся значений первичного ключа.
Уникальные ключи выполняют ту же функцию - они также служат для однозначной идентификации записей в таблице. Разница между первичными и уникальными ключами заключается в том, что в таблице может быть только один первичный ключ, а уникальных ключей может быть несколько. Отмечено, что и первичный, и уникальный ключ могут использоваться в качестве основы для ссылок внешних ключей (см. далее).
Формальное описание понятий первичного и уникального ключей, а также другие важные определения можно найти в приложении «Глоссарий» в конце книги. Синтаксис создания первичного и уникального ключа на основе одного поля следующий:
< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}
Примеры первичного и уникального ключей:
CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*первичный ключ*/
uk1 VARCHAR(50) NOT NULL UNIQUE,/*уникальный ключ */
uk2 INTEGER NOT NULL UNIQUE /\* ещё один уникальный ключ */);
Синтаксис создания первичного и уникального ключей на основе нескольких полей:
= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Такой синтаксис позволяет создавать ключи на основе комбинации полей. Вот примеры создания первичного и уникального ключей из нескольких полей:
CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*первичный ключ pkt на основе двух полей*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*уникальный ключ ukt1 на основе двух полей*/
Обратите внимание, что все поля, включённые в первичные и уникальные ключи, должны быть объявлены как NOT NULL, так как эти ключи не могут иметь неопределённое значение. Помимо создания ограничений первичного и уникального ключей при создании таблицы, существует возможность добавлять ограничения к уже существующей таблице. В этом случае используется оператор DDL: ALTER TABLE. Синтаксис добавления ограничений первичного или уникального ключа к существующей таблице аналогичен описанному выше:
ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])
Рассмотрим пример создания первичного и уникального ключа с помощью ALTER TABLE:
CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));
Затем добавляем ключи. Сначала первичный:
ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);
Затем уникальный: ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);
Следует отметить, что только владелец этой таблицы или системный администратор SYSDBA (подробнее о владельцах и пользователе SYSDBA см. главу «Безопасность в InterBase: пользователи, их функции и права» - часть 4) может выполнять добавление (а также удаление) первичных и уникальных ключей к таблице.
Внешние ключи
Следующее ограничение, часто используемое в базах данных InterBase, - это ограничение внешнего ключа. Это очень мощный инструмент для обеспечения ссылочной целостности в базе данных, который позволяет не только контролировать наличие правильных ссылок в базе данных, но и автоматически управлять этими ссылками!
Смысл создания внешнего ключа заключается в следующем: если две таблицы служат для хранения взаимосвязанной информации, необходимо гарантировать, что эта взаимосвязь всегда будет корректной. Например, документ «накладная», содержащий общий заголовок (дата, номер накладной и т. д.) и набор подробных записей (описание товаров, количество и т. д.).
Для хранения такого документа в базе данных создаются две таблицы - одна для хранения заголовков накладных, а вторая - для хранения содержимого накладной - записей о товарах и их количестве. Такие таблицы называются главной и подчинённой, или таблицей-мастером и таблицей деталей.
Согласно здравому смыслу, содержимое накладной не может существовать без наличия её заголовка. Другими словами, мы не можем вставить запись о товаре, если мы не создали заголовок накладной, и мы не можем удалить запись заголовка, если существуют записи о товарах. Для реализации такого поведения таблица заголовков и таблица деталей соединяются с помощью ограничения внешнего ключа.
Рассмотрим смысл установки ограничений внешнего ключа на примере таблиц, содержащих информацию о накладных. Для этой цели мы создадим две таблицы для хранения накладной - таблицу TITLE для хранения заголовка и таблицу INVENTORY для хранения информации о товарах, включённых в накладную.
CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));
Обратите внимание, что мы сразу определили первичный ключ в таблице заголовков на основе поля ID_TITLE. Остальные поля таблицы TITLE содержат тривиальную информацию о заголовке накладной - дату, номер, комментарий.
Теперь определим таблицу для хранения информации о товарах, включённых в накладную:
CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);
Давайте посмотрим, какие поля включены в таблицу INVENTORY. Во-первых, это ID_INVENTORY - первичный ключ этой таблицы. Затем идёт целочисленное поле FK_TITLE, служащее ссылкой на идентификатор ID_TITLE заголовка в таблице заголовков накладных. Затем следуют поля ProductName, Kolvo и Positio, описывающие наименование товара, его количество и позицию в накладной. Поле FK_TITLE является наиболее важным для нашего примера. Если мы хотим вывести информацию о товарах определённой накладной, мы должны использовать следующий запрос, в котором параметр mas_ID_TITLE определяет идентификатор заголовка:
SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE
Фактически, в описанной ситуации ничто не мешает заполнить таблицу INVENTORY записями, ссылающимися на несуществующие записи в таблице TITLE. Кроме того, ничто не мешает удалить заголовок уже существующей накладной, из-за чего записи о товарах могут стать «бесхозными». Сервер не запретит выполнять все эти вставки и удаления. Таким образом, контроль целостности данных в базе данных полностью возлагается на клиентское приложение. Однако вы знаете, что с одной базой данных могут работать несколько приложений, разработанных, возможно, разными программистами, что может привести к различной интерпретации данных и ошибкам. Следовательно, необходимо установить явное ограничение, чтобы в таблицу INVENTORY могли попадать только такие записи о товарах, которые имеют корректную ссылку на заголовок накладной. Это, по сути, и есть ограничение внешнего ключа, которое позволяет вставлять в поля, включённые в ограничение, только те значения, которые присутствуют в другой таблице.
Tale vincolo può essere creato utilizzando una chiave esterna. Per l’esempio fornito, dobbiamo impostare i vincoli di chiave esterna per il campo FK_TITLE e collegarlo alla chiave primaria ID_TITLE nella tabella TITLE. Possiamo aggiungere una chiave esterna a una tabella già esistente con il seguente comando:
ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)
Spesso quando si aggiunge una chiave esterna, appare l’errore - oggetto in uso. Il problema è che per creare una chiave esterna, dobbiamo aprire il database in modalità esclusiva - in modo che non ci siano altri utenti contemporaneamente. Inoltre, non dovremmo fare riferimento alla tabella modificata - potrebbe causare l’errore “oggetto in uso”.
Qui INVENTORY è il nome della tabella per cui è impostato il vincolo di chiave esterna; fktitle1 è il nome della chiave esterna; FK_TITLE - i campi che costituiscono la chiave esterna; TITLE è il nome della tabella che fornisce i valori (la base di riferimento) per la chiave esterna; ID_TITLE - i campi della chiave primaria o univoca nella tabella TITLE, che servono come base di riferimento per la chiave esterna. La sintassi completa del vincolo di chiave esterna (con la possibilità di creare vincoli basati su più campi) è riportata di seguito:
= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Come puoi vedere, le definizioni contengono un ampio insieme di opzioni. Per iniziare, consideriamo una definizione base di chiave esterna, che è la più utilizzata nei database reali, e poi analizzeremo le possibili opzioni.
Una forma dichiarativa del vincolo di chiave esterna viene spesso utilizzata quando viene specificato un insieme di campi (col [, col …]), che costituiranno il vincolo; così come other_table che contiene un elenco di valori possibili per la chiave esterna nei campi [(other_col [, other_col …])].
Ecco l’esempio di tale definizione durante la creazione della tabella:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);
Nota che in questa definizione le parole chiave FOREIGN KEY sono omesse, e si intende che l’unico campo FK_TITLE venga utilizzato come chiave esterna. Una forma più completa di creazione della chiave esterna contemporaneamente alla tabella è fornita nell’esempio seguente:
CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);
Utilizzo di NULL nei campi di una chiave esterna
Nei campi, sulla base dei quali viene creata una chiave esterna, è consentito applicare campi NULL. Questa possibilità è aggiunta per consentire riferimenti reciproci. Ad esempio, se ci sono due tabelle che si riferiscono l’una all’altra utilizzando chiavi esterne. Se non consentiamo il riferimento vuoto (cioè NULL) in queste chiavi esterne, sarà impossibile aggiungere qualsiasi record alle tabelle collegate: per aggiungere un record alla prima tabella, è richiesto un record nella seconda tabella, e viceversa.
L’utilizzo di NULL come riferimento vuoto consente di creare riferimenti reciproci di due tabelle che si riferiscono incrociatamente, e anche di memorizzare strutture gerarchiche in tabelle relazionali - in questo caso i nodi radice si riferiscono a record “vuoti” (cioè contengono semplicemente NULL).
Capacità estese di supporto dell’integrità referenziale utilizzando la chiave esterna
Di solito la variante dichiarativa del vincolo di chiave esterna è sufficiente; il server controlla solo che sia impossibile inserire valori errati nella tabella con la chiave esterna o - se si tenta di farlo, appare un errore. Ma InterBase consente di eseguire una serie di operazioni automatiche durante la modifica/eliminazione di una chiave esterna. A questo scopo, viene utilizzato il seguente insieme di opzioni di chiave esterna:
[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]
Queste opzioni consentono di definire diverse operazioni durante l’aggiornamento o l’eliminazione dei valori della chiave esterna.
Ad esempio, possiamo impostare che quando si elimina una chiave primaria nella tabella master, tutti i record con la stessa chiave esterna nella tabella subordinata vengano eliminati. In questo caso, dobbiamo definire una chiave esterna nel seguente modo:
ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE
In realtà, per l’implementazione di queste operazioni esiste un trigger di sistema che esegue determinate operazioni. Nella tabella 1.2 c’è una descrizione delle operazioni delle diverse opzioni (nota che le opzioni NO ACTION|CASCADE|SET DEFAULT|SET NULL non possono essere utilizzate nella stessa clausola ON XXX).
Tabella 1.2
| Evento | Operazione | |||
| NO ACTION | CASCADE | SET DEFAULT | SET NULL | |
| ON DELETE | Quando si elimina una chiave esterna non fare nulla - è usato per impostazione predefinita | Quando si elimina, eliminare tutti i record correlati dalla tabella subordinata | Quando si modifica impostare il campo della chiave esterna a un valore predefinito |
Quando si modifica impostare il campo della chiave esterna a NULL |
| ON UPDATE | Quando si modifica non fare nulla - è usato per impostazione predefinita | Quando si modifica un record, modificare tutti i record correlati nella tabella subordinata | Quando si elimina impostare il campo della chiave esterna a un valore predefinito |
Quando si elimina impostare il campo della chiave esterna a NULL |
Se non specifichiamo nulla o specifichiamo NO ACTION, dobbiamo occuparci noi stessi della modifica di una chiave esterna (in caso di modifica di una chiave primaria), e quando si elimina la chiave primaria dobbiamo eliminare preventivamente i record dalla tabella subordinata. Presta molta attenzione quando usi l’opzione CASCADE: il suo uso imprudente può portare all’eliminazione di un gran numero di record correlati.
Vincolo CHECK
Uno dei vincoli più utili in un database è il vincolo check. La sua funzione è molto semplice - controllare il valore inserito nella tabella rispetto a qualsiasi condizione e, in base all’esecuzione di questa condizione, inserire i dati o meno. La sua sintassi è abbastanza semplice:
= [CONSTRAINT constraint] CHECK ( )}
Qui constraint è il nome del vincolo; è una condizione di ricerca, in cui il valore inserito/aggiornato può essere un parametro. Se la condizione di ricerca è soddisfatta, è consentito inserire/aggiornare questo valore; se non lo è - appare un errore. L’esempio più semplice di check:
create table checktst( ID integer CHECK(ID>0));
Questo check determina se il valore inserito/aggiornato del campo ID è maggiore di zero e, in base al risultato, consente di inserire/aggiornare un nuovo valore o di segnalare l’errore (vedi il capitolo “Capacità estese del linguaggio delle stored procedure di InterBase” (parte 1)).
Esistono anche varianti più complesse di check. La sintassi completa della condizione di ricerca è la seguente:
= {
{ | ()}
| [NOT] BETWEEN AND
| [NOT] LIKE [ESCAPE ]
| [NOT] IN ( [ , …] | )
| IS [NOT] NULL
| {[NOT] {= | < | >} | >= | <=}
{ALL | SOME | ANY} ()
| EXISTS ( )
| SINGULAR ( )
| [NOT] CONTAINING
| [NOT] STARTING [WITH]
| ()
| NOT
| OR
| AND }
Pertanto, CHECK offre un ampio insieme di opzioni per controllare i valori inseriti/aggiornati. Dovresti ricordare i seguenti vincoli quando usi CHECK:
- I dati per CHECK vengono presi solo dal record corrente. Non dovresti prendere i dati per l’espressione in CHECK da altri record della stessa tabella - potrebbero essere modificati da altri utenti
- Un campo può avere solo un vincolo CHECK
- Se per la definizione del campo viene utilizzato un dominio con vincolo CHECK di dominio, non può essere ridefinito a livello di un campo concreto nella tabella. Va detto che CHECK è implementato da trigger di sistema, quindi dobbiamo essere più cauti quando usiamo condizioni molto lunghe, che possono rallentare notevolmente i processi di inserimento e aggiornamento dei record.
Eliminazione dei vincoli
Molto spesso, eliminiamo diversi vincoli per le ragioni più disparate. Per eliminare un vincolo, dovremmo usare l’istruzione ALTER TABLE nella seguente forma: ALTER TABLE tablename DROP CONSTRAINT constraintname
constraintname è il nome del vincolo che deve essere eliminato. Se è stato specificato un certo nome durante la creazione del vincolo, dovremmo usarlo; ma se non è stato specificato, dobbiamo aprire uno strumento di amministrazione InterBase, cercare tutti i vincoli correlati e scoprire quale nome di sistema InterBase ha generato per il vincolo richiesto.
Va notato che solo il proprietario della tabella o l’amministratore di sistema SYSDBA può eliminare i vincoli.