database constraints

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.

Types of constraints in a database

• PRIMARY KEY
• UNIQUE KEY
• FOREIGN KEY

can switch on automatic triggers – ON UPDATE and ON DELETE;
CHECK

Primary and Unique keys
Primary keys are one of main types of database constraints. They are applied for one-valued identification of records in the table. 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

Foreign keys
The next constraint frequently used is a foreign key constraint. This is a very powerful tool for supplying reference integrity in a database, which allows not only to supervise the presence of correct references in a database, but also to control these references automatically! 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.

Example: two tables are created in a database – one for storing the header, and the second for storing contents of the header (header data). Such tables are called main and subordinate or table-master and the detail-table. The contents of the header data cannot exist without the presence of its header. In other words, we cannot insert a record about the header data if we have not created the header, and we cannot delete a record of header if there are remaining records about the header data. For realization of such behavior, table of header and table of header data join using a foreign key constraint.

Using NULL in fields of a foreign key
In fields, on basis of which a foreign key is created, it is allowed to apply NULL- fields. This possibility is added for allowing mutual references. For example, if there are two tables referring to each using foreign keys. If we do not allow the empty reference (i.e. for NULL) in these foreign keys, it will be impossible to add any record to joined tables: in order to add a record to the first table, it is required to have a record in the second table, and vice versa.
Using NULL as the empty reference allows to create mutual references of two cross- referring tables, and also to store hierarchical structures in relational tables – at that root nodes refer to "empty" records (i.e. simply contain NULL). A declarative variant of a foreign key constraint is quite enough, the RDBMS maintains the possible values inserted into the table with the foreign key

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.