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:

ALTER TABLE MyTable
ADD CONSTRAINT pk_employeeid PRIMARY KEY(EmployeeID)

When your primary key constraints are no longer required, you can easily get rid of them by calling the DROP CONSTRAINT clause.

The next one is FOREIGN KEY. FOREIGN KEY ensures that a given value exist in the referenced table’s primary key or unique column. The syntax is the same as it was with primary key. The more interesting part is the question of cascading referential identities. You can specify actions to happen when you try to delete a value that existing foreign keys point to. There are two available events for you: ON DELETE and ON UPDATE. The actions are the same: NO ACTION, CASCADE, SET NULL, SET DEFAULT.

It’s worth mentioning the NOCHECK clause here. You can call it when you set up a FOREIGN KEY constraint, the code is WITH NOCHECK. It forces SQL Server to skip checking whether or not the existing values are matching the foreign key constraint, just makes sure that any further values will match.

Another beautiful constraint is IDENTITY(SEED, INCREMENTATION). IDENTITY generates an auto-incrementing value for inserts, thus making sure for you that all values are different in a column. A beautiful line of code:

RowID INT NOT NULL PRIMARY KEY IDENTITY(1,1)

Sometimes you might need to override the behavior of an IDENTITY column. To do so, call SET IDENTITY_INSERT ON. Be sure to switch it back when you’re done. With IDENTITY_INSERT set to on, you can insert explicit values into identity columns.  If you have specified something larger than the current identity value, it will be used as the new identity value from that time (therefore, any values between the old one and the new one will be skipped).

Advertisements

Tags: , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: