Specifying Constraints

Specifying Constraints

You should consider a number of constraints to enforce the integrity of the database. Primary keys and foreign keys enforce Referential Integrity, which means that each record in the child table is linked to a record in the parent table (no orphaned records). The database model is evolving during the database design process, and is inseparable from RDBMS principle.

Primary key and UNIQUE constraints also enforce Entity Integrity — this concept refers to making sure that a row in the table is unique within the table (that is, its combination of column values is unique throughout the table). Entity Integrity is also enforced with NOT NULL constraints (NULL values are not allowed within this particular column). You should select a primary key for each table you create and apply the NOT NULL constraint for each column of every table that should never be NULL (for example, if you have a record for a customer, it must have a CUST_ID field populated with some unique value — no two customers should share the same ID).

Domain Integrity refers to the data itself and is enforced by using the appropriate data type, for example, CHECK constraints and DEFAULTs. CHECK constraints ensure that only specific data — in a range of values or formats — is entered into the column; while DEFAULT constraints specify default values for the column in case you do not have a value for it when inserting new records.

Data types for the column guard against entering invalid data into it. There can be no VARCHAR data in a NUMBER column, and SQL Server's IMAGE data cannot possibly be inserted into the column of an INTEGER data type.

In addition to these integrity constraints, it is possible to enforce custom constraints through use of triggers, stored procedures, and other RDBMS-specific features (see Chapter 14 for more information).