Library

Database constraints in Firebird and InterBase

NOTICE: This document is the chapter from the book "The InterBase World" which was written by Alexey Kovyazin and Serg Vostrikov.

This chapter is devoted to the constraints of InterBase and Firebird databases. Database constraints are rules that define interrelations between tables and can check and modify the data in a database. These rules are realized as special database objects. The main advantage of using constraints consists in capability to implement data check, and a part of business-logic of the application at a level of a database, i.e. to centralize and simplify it, so to make the development of databases applications easier and more reliable.

Beginning developers often neglect using database constraints, considering that they hamper creative work. However, actually such opinion is formed on insufficient knowledge of the theory and practice of database design.

At the same time, the most experienced designers venture to refuse using some types of constraints, owing to what their applications win in speed. Experience of expert designers allows them to understand server’s work very well and predict precisely its behavior in complicated cases, therefore it is better for InterBase beginning programmers not to appeal to similar actions of experienced colleagues.

Within this book we do not consider database design, therefore for more information on this question, see the list of literature at the end of the book. Here we will only review all types of constraints in InterBase database and will consider the examples of their application.

Types of constraints in a database

There are the following types of constraints in InterBase database:

• PRIMARY KEY;
• UNIQUE KEY;
• FOREIGN KEY

• CHECK

In the previous chapters, we mentioned some of these constraints, because it was necessary for logical presentation of the material, but now we will consider their syntax, application and implementation in more detail. Database constraints are of two types - based on one field and based on several fields of the table. The Syntax of both types of constraints is given below.

= [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}]
}

The syntax of constraints based on several fields is the following:

= [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 ( )}

Example of typical constraint

Actually, constraints based on one field are a special case of constraints based on several fields.

The example of creating a primary key constraint using these two different approaches is given below. Let's create a table containing only one field and set a primary key constraint to it.

Here is the example of the primary key using the syntax of constraint based on one field:

CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); In this example, a primary key with name pktest is created for ID_PK field. As a result, we have a rather compact description in one line. We can use the syntax of constraints based on several fields for the same aim: CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));

Creating constraints

Using this option, you can set the name to the created constraint as in case of applying the syntax of constraints based on one field, as in case of constraints based on several fields. If you have not specified the name for constraint, InterBase will automatically generate it. Nevertheless, it is better set the name to the created constraint to improve readability of database scheme and simplify management of constraints later on.

Primary and unique keys

In this case, "person" is represented by one record in the table, therefore we can ask a more general question - how can we distinguish one record in (any) the table from other record in the same table. For this purpose, constraints – primary keys are used. The primary key represents one or a few fields in the table, which combination is unique for every record. There are no repeating values of a primary key for one table.

Unique keys perform the same function – they also serve for one-valued identification of records in the table. The difference between primary and unique keys is that there can be only one primary key in the table, and as to unique keys – a few. It noted that both a primary and a unique key can be used as a reference basis for foreign keys (see further).

The formal description of notions of primary and unique keys, as well as other important definitions can be found in the application "Glossary" at the end of the book. Syntax of creating a primary and a unique key based on a unique field is the following:

< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}

Examples of primary and unique keys:

CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*a primary key*/

uk1 VARCHAR(50) NOT NULL UNIQUE,/*a unique key */

uk2 INTEGER NOT NULL UNIQUE /* one more unique key */);

Syntax of creating primary and unique keys based on several fields:

= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])

Such syntax allows to create keys on the basis of a combination of fields. Here are the examples of creating primary and unique keys from several fields:

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), /*primary key pkt based on two fields*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*unique key ukt1 based on two fields*/

Pay attention that all the fields included in primary and unique keys, should be declared as NOT NULL as these keys cannot have undefined value Apart from creating the constraint of primary and unique keys, when creating the table there is a capability to add constraints to the table that already exists. In this case DDL: ALTER TABLE statement is used. Syntax of adding the constraints of primary or unique key to the existing table is similar to above described:

ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])

Let’s consider the example of creating primary and unique key using ALTER TABLE:

CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));

Then we add keys. Primary first:

ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);

Then unique: ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);

It should be marked that only the owner of this table or system administrator SYSDBA (for more details about the owners and SYSDBA user, see the chapter "Security in InterBase: users, their functions and rights " – part 4) can perform addition (as well as deleting) of primary and unique keys to the table.

Foreign keys

The point of creating a foreign key is the following: if two tables serve for storing interrelated information, it is necessary to guarantee that this interrelation will always be correct. For example, the document "waybill" containing general heading (date, number of the waybill, etc.) and set of detailed records (description of goods, quantity, etc.).

For storing such document two tables are created in a database – one for storing headings of waybills, and the second – for storing contents of the waybill – records about goods and their quantity. Such tables are called main and subordinate or table-master and the detail-table.

According to common sense, the contents of the waybill cannot exist without the presence of its heading. In other words, we cannot insert a record about the goods if we have not created the heading of the waybill, and we cannot delete a record of heading if there are records about the goods. For realization of such behavior, table of heading and table of details join using a foreign key constraint.

Let's consider the sense of setting foreign key constraints by the example of the tables containing the information on waybills. For this purpose, we will create two tables for storing the waybill – TITLE table for storing the heading and INVENTORY table for storing the information on goods included in the waybill.

CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));

Pay attention that we have defined a primary key in the table of heading based on ID_TITLE field at once. The rest fields of TITLE table contain trivial information about the waybill heading – date, number, comment.

Now let’s define the table for storing information on goods included into the waybill:

CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);

Let's see what fields are included in INVENTORY table. First, it is ID_INVENTORY – a primary key of this table. Then goes integer field FK_TITLE serving as the reference to identifier of ID_TITLE heading in the table of waybills headings. Then follow ProductName, Kolvo and Positio fields describing the description of goods, its quantity and a position in the waybill. FK_TITLE field is the most important for our example. If we want to output the information on goods of a certain waybill, we should use the following query, in which the mas_ID_TITLE parameter defines the heading identifier:

SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE

Virtually, in the described situation nothing prevents from filling INVENTORY table with the records referring to non-existent records in TITLE table. In addition, nothing interferes with deleting the heading of already existing waybill, because of what records about the goods can become "ownerless". The server will not prohibit from executing all these inserts and removals. Thus, the control over data integrity in a database is completely placed on the client application. However, you know that several applications developed, perhaps, by different programmers can work with one database, what may lead to different data interpretation and errors. Consequently, it is essential to set the explicit constraint that only such records about the goods that have the correct reference to the waybill heading can be put to INVENTORY table. This, in fact, is a foreign key constraint that allows inserting only those values, which are in the other table, into the fields included in constraints.

Such constraint can be created using a foreign key. For the given example, we have to set foreign key constraints for FK_TITLE field and bind it up with ID_TITLE primary key in TITLE. We can add a foreign key to already existing table by the following command:

ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)

Frequently when adding a foreign key, the error appears – object is in use. The matter is that for creating a foreign key, we have to open a database in a burst mode – that there will be no other users at the same time. Also we should not refer to the modified table – it may cause object is in use.

Here INVENTORY is a name of the table the foreign key constraint is set for; fktitle1 is a name of the foreign key; FK_TITLE - the fields making the foreign key; TITLE is a name of table giving the values (the reference basis) for the foreign key; ID_TITLE - fields of the primary or unique key in TITLE table, which serve as the reference basis for the foreign key. A Complete syntax of the foreign key constraint (with a possibility to create constraints based on several fields) is given below:

= [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}]

As you can see, definitions contain a big set of options. To begin with, let's consider a base definition of a foreign key, which is most frequently used in real databases, and then we will analyze the possible options.

A declarative form of a foreign key constraint is most often used when a set of fields (col [, col …]), which will make the constraint, is specified; as well as the other_table that contains a list of possible values for the foreign key in fields [(other_col [, other_col …]).

Here is the example of such definition when creating the table:

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);

Pay attention that in this definition keywords of FOREIGN KEY are omitted, as well as the only field FK_TITLE is implied to be used as a foreign key. A more complete form of creating the foreign key simultaneously with the table is given in the following example:

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);

Using NULL in fields of a foreign key

Extended capabilities of reference integrity support using foreign key

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

These options allow to define different operations when updating or deleting foreign key values.

For example, we can set that when deleting a primary key in the table-master, all the records with the same foreign key in the subordinate table should be deleted. In this case, we have to define a foreign key in the following way:

ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE

Actually, for implementation of these operations there is a system trigger, which executes certain operations. In table 1.2 there is a description of operations of different options (pay attention, that options NO ACTION|CASCADE|SET DEFAULT|SET NULL cannot be used in one sentence ON XXX).

CHECK constraint

One of the most useful constraints in a database is check constraint. Its function is very simple – to check the value inserted into the table for any condition and according to execution of this condition, to insert the data or to. Its syntax is quite simple:

= [CONSTRAINT constraint] CHECK ( )}

Here constraint is a name of constraint; is a search condition, in which inserted / updated value can be as a parameter. If the search condition is fulfilled, It is allowed to insert / update this value, if is not –an error appears. The simplest example of check:

create table checktst( ID integer CHECK(ID>0));

This check determines if inserted / updated value of ID field is more than zero, and depending on the result allows to insert / update a new value or to inform about the error (see the chapter "Extended capabilities of language of InterBase stored procedures " (part1)).

There are also more complicated variants of checks. A complete syntax of search condition is the following:

= {
{ | ()}
| [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 }

Thus, CHECK gives a big set of options for checking inserted / updated values. You should remember about the following constraints when using CHECK:

• The data for CHECK is taken only from a current record. You should not take the data for expression in CHECK from the other records of the same table - they can be altered by other users
• A field may have only one CHECK constraint
• If for field definition the domain having CHECK domain constraint is used, it cannot be redefined at the level of a concrete field in the table. It must be said that CHECK are implemented by system triggers, therefore we have to be more cautious when using very long conditions, which can strongly slow down processes of inserting and updating the records.

Deleting constraints

constraintname is a name of the constraint that should be deleted. If a certain name was specified when creating the constraint, we should use it, but if not, we have to open any InterBase administration tool, search for all constraints concerned with it and find out what system name InterBase generated for the required constraint.

It should be noted that only the owner of the table or SYSDBA system administrator can delete the constraints.