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.

Continue reading

Advertisements

Create and modify constrains

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:
Continue reading

Manage data integrity

The topic of data integrity is an important one. You should always make sure that your data is in a consistent state, particularly during modifications. To do this, you should use the built-in functionality of the DataSet class, which allows you to deal with cascading updates and deletes, manage constraints, and the rest.

There are two key classes in this part of the framework: UniqueConstarint and ForeignKeyConstarint. Both of them defines rules that must be applied to the data stored in the DataSet (as long as the EnforceConstaint property is set to true). Let’s start with the ForeignKeyConstraint, because it’s slightly more complex than the other one.

You set up a ForeignKeyConstraint like this:

ForeignKeyConstraint fkc = new ForeignKeyConstraint(parentColumn, childColumn);

After you have declared it, it behaves in the exact same manner as if it was set up in the data source. There are four actions which can take place when you delete the foreign key column, depending on how you set the DeleteRule property. Also, the UpdateRule can be set to the same values, which are:

Continue reading