Design data integrity
May include but is not limited to: primary key, foreign key, check constraint, default constraint, NULL/NOT NULL, unique constraint, DML triggers
SQL Server is a relational database engine – and this means that you can – and probably should – care for the relations of your data. This is called normalization, and there are quite a few ways to implement it – mostly by using constraints on how data should be structured.
The easiest type of constraints is unique. It does nothing more than its name implies – ensures that there all values in a given column are unique. You can even insert a NULL value into a column constrained by UNIQUE – if you allow NULLs in that column – but because each values must be unique, you can insert only one NULL.
Primary keys are like unique constraints, in a way that they enforce unique values. But primary keys are less tolerant than unique – there can be only one primary key in a table (but that can be a combination of two or more columns, too), and a primary key column cannot contain NULL values. If you define a primary key constraint on more than one column, you need to make sure that any possible combinations of those columns are unique. Thus it allows repetitive values in a given column, as long as values in other columns won’t make the same combination.
When you define primary keys, the database engine creates a unique index for the involved columns – making searches faster, and enforcing uniqueness. Primary keys come especially handy in the case of joins – and foreign keys.
A check constraint can be used to enforce a logical expression on data that’s accepted in a single column. This logic doesn’t have to depend on values contained in tables, and any kind of expression is accepted, as long as it results in a Boolean true or false value. You can even define a check constraint on a table, thus constraining multiple columns – and you can define multiple check constraints on a single column, too. However, there are some limitations. As SQL works with a three-valued logic, NULL values aren’t restricted by check constraints – they just being inserted/update silently and slippery.
When you define a check constraint on an existing table, along with any future data, the constraint enforces integrity on existing data, too. To override this behavior, use the WITH NOCHECK clause in your ALTER TABLE statement.
I guess there’s not much to describe on a default constraint. It ensures that whenever no data is provided to a column, a predefined value is used – that’s all.
My best friends in SQL Server are foreign keys. I always use them – and I really love to see them making a web around my tables in a database diagram – and do this for a purpose. Relational data – that’s all. A foreign key is a column or set of columns which maps to a primary key column in another table. In this way, you can define one-to-many relationship between tables. The most classic: customer-order.
There’s one other thing to mention when using foreign keys – cascading changes. You can specify an action to use when deleting the primary key of a foreign key in the REFERENCES clause. The two types are CASCADE and NO ACTION. NO ACTION is the default, it throws and error if you attempt to delete rows referenced by other rows. CASCADE starts a cascading action, which results in the update or delete of related rows. You can’t use CASCADE with tables with a timestamp value.
DML (Data Modification Language) triggers are a good way to provide data integrity, too. There are two types of them: INSTEAD OF and AFTER. You can guess that what these names mean – I leave it to you.
You can use an AFTER trigger only on tables; while INSTEAD OF triggers can be used on views – a very effective thing to handle multiple tables underlying a view. Both types of triggers produce inserted and deleted virtual tables.
An important thing to notice is that you can fire triggers recursively, specifying the RECURSIVE_TRIGGERS option. However, you can easily start an infinite loop, which breaks after 32 rounds.