Constraints in Database

Introduction:

Constraints provide a powerful yet easy way to enforce the data integrity in your database. Data integrity comes in three forms:

Entity integrity Ensures that a table has a primary key. In SQL Server 2005, you can guarantee entity integrity by defining PRIMARY KEY or UNIQUE constraints or by building unique indexes. Alternatively, you can write a trigger to enforce entity integrity, but this is usually far less efficient.

Domain integrity Ensures that data values meet certain criteria. In SQL Server 2005, domain integrity can be guaranteed in several ways. Choosing appropriate data types can ensure that a data value meets certain conditionsfor example, that the data represents a valid date. Other approaches include defining CHECK constraints or FOREIGN KEY constraints or writing a trigger. You might also consider DEFAULT constraints as an aspect of enforcing domain integrity.

Referential integrity Enforces relationships between two tables, a referenced table, and a referencing table. SQL Server 2000 allows you to define FOREIGN KEY constraints to enforce referential integrity, and you can also write triggers for enforcement. It's crucial to note that there are always two sides to referential integrity enforcement. If data is updated or deleted from the referenced table, referential integrity ensures that any data in the referencing table that refers to the changed or deleted data is handled in some way. On the other side, if data is updated or inserted into the referencing table, referential integrity ensures that the new data matches a value in the referenced table.

In this section, I'll briefly describe some of the internal aspects of managing constraints. Constraints are also called declarative data integrity because they are part of the actual table definition. This is in contrast to programmatic data integrity, which uses stored procedures or triggers, which are discussed in Inside Microsoft SQL Server 2005: T-SQL Programming.

Here are the five types of constraints:

1. PRIMARY KEY

2. UNIQUE

3. FOREIGN KEY

4. CHECK

5. DEFAULT

You might also sometimes see the IDENTITY property and the nullability of a column described as constraints. I typically don't consider these attributes to be constraints; instead, I think of them as properties of a column, for two reasons. First, each constraint has its own row in the sys.objects catalog view, but IDENTITY and nullability information is not available in sys.objects, only in sys.columns and sys.identity_columns. This makes me think that these properties are more like data types, which are also viewable through sys.columns. Second, when you use the command SELECT INTO to make a copy of a table, all column names and data types are copied, as well as IDENTITY information and column nullability, but constraints are not copied to the new table. This makes me think that IDENTITY and nullability are more a part of the actual table structure than constraints are.