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.
May include but is not limited to: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, cascading referential integrity, enabling/disabling, NOCHECK, SET IDENTITY_INSERT
Some of us (including me) with the ASP.NET WebForms background may think that validating data before it can be sent to the database eliminates the need of further refinements. However, we don’t have the power of ensuring that our database can be only accessed from our application, and some extra layers of validation certainly won’t hurt for anyone.
SQL Server 2008 allows a set of validation mechanisms, such as ensuring that two rows can’t hold the same values, identities, and cascading referential support. We can also define custom validation logic with the help of the CHECK keyword. But let’s start from the beginning.
SQL Server stores relational data. To be able to query this data efficiently, it’s a good idea to create a key for each column in a table. Primary keys are the tool that’s designed for this. A primary key creates a set of unique identifiers on a given column, thus preventing the same data to be inserted, and even creates clustered indexes.
There are three ways to define a primary key: in the CREATE TABLE clause, you can set one on the column level, on the table level, or you can use an ALTER TABLE clause to add one later. I think the only one worth mentioning is adding a constraint later: