Data Integrity Foundations with Constraints (Part 2)

Last week we started to at the use of constraints to support with data integrity in our databases. As a refresher on our introduction from last time:

Data integrity helps give us assurance that our data is accurate, complete, and free from errors or inconsistencies. Some of this comes from making sure that data is correct before it enters are databases – and this is where constraints come in.

In simple terms, constraints are rules that define and enforce the properties and relationships of data within a database. These help protect us from erroneous data, and can also help SQL Server to understand our data better to make more informed decisions in how to query it.

Last week we looked at Primary Key constraints, Unique constraints, and Foreign Key constraints, but there are still a few more for us to tackle this week.

Default constraints

By adding a default constraint to one of our columns we can specify a value to be automatically used for that field when the column isn’t specified when adding a new record. These can be particularly useful if you’ve added a new column to a table, or if you’re looking to record a value automatically such as a timestamp for when a record is created.

CREATE TABLE dbo.DefaultConstraints (
	ID INT IDENTITY(1, 1) PRIMARY KEY,
	ProductName VARCHAR(100),
	IsAvailable BIT DEFAULT(0),
	CostPrice MONEY
		CONSTRAINT DF_CostPrice DEFAULT(0),
	RetailPrice MONEY
);

ALTER TABLE dbo.DefaultConstraints
ADD CONSTRAINT DF_RetailPrice DEFAULT (0) FOR RetailPrice;

It’s worth noting with default constraints that they will only be used when the column isn’t specified and not when a value isn’t specified. For example:

INSERT INTO dbo.DefaultConstraints (ProductName)
VALUES ('Apples');

INSERT INTO dbo.DefaultConstraints (ProductName, IsAvailable, CostPrice, RetailPrice)
VALUES ('Bananas', NULL, NULL, NULL);

As we’ve explicitly specified NULL values for our Bananas these have been inserted. For our Apples record we didn’t include the columns as part of our INSERT statement so those fields have inherited their default value.

Default Constraints can be found within the Constraints node of our table:

Check constraints

Where a Default Constraint can help us with data integrity when it’s entering our tables, we can use Check Constraints to continually enforce rules on data which is created or modified within our tables.

Within the definition of a Check Constraint we can specify rules – including multiple through use of an AND or OR clause – which are checked and will stop data modifications which would break them, for example:

CREATE TABLE dbo.CheckConstraints (
	ID INT IDENTITY(1, 1) PRIMARY KEY,
	ProductName VARCHAR(100),
	CostPrice MONEY,
	RetailPrice MONEY,
	CONSTRAINT CK_CostRetailPrice
		CHECK (ISNULL(CostPrice, -1) > 0
			AND ISNULL(RetailPrice, -1) > 0
			AND CostPrice < RetailPrice)
);

In this instance we’ve ensured that the CostPrice and RetailPrice fields are not null, have a value populated greater than zero, and also that the Retail Price must be greater than the Cost Price.

If we try to modify our data in a way which doesn’t pass this check we’ll receive an error and the modification won’t take place:

The INSERT statement conflicted with the CHECK constraint “CK_CostRetailPrice”. The conflict occurred in database “Blog”, table “dbo.CheckConstraints”.

These checks will be carried out for any INSERT or UPDATE commands against the data so we can be confident that integrity is maintained whilst the constraint is in place.

As with Default Constraints, the Check Constraints can also be found within the Constraints node of our table:

The NOT NULL constraint

This one is slightly different to what we’ve already seen in that this isn’t specifically a constraint declaration within our schema. A NOT NULL constraint is applied when a column is defined and simply states that the contents of that column must be populated with something – they can’t be NULL values.

The declaration for these is in line with the column declaration, for example:

CREATE TABLE dbo.NotNull (
	ID INT IDENTITY(1, 1) PRIMARY KEY,
	SomeText VARCHAR(50) NOT NULL,
	SomeValue INT NOT NULL
);

If we were to try and insert any NULL values, the INSERT will fail and no new record would be added. When this happens you’ll see an exception thrown similar to the below:

Cannot insert the value NULL into column ‘SomeText’, table ‘dbo.NotNull’; column does not allow nulls. INSERT fails.

As these NOT NULL declarations are made against columns they are shown in the Columns node for a table rather than under the Constraints:

Empty constraints

Our last and certainly least example here isn’t of much use at all. It turns out that you can pop a constraint into your column declaration which quite simply does nothing:

CREATE TABLE dbo.EmptyConstraint (
	ID INT IDENTITY(1, 1) PRIMARY KEY,
	SomeValue INT CONSTRAINT EmptyConstraint NULL
);

We’ve specified a constraint and given it a name… but haven’t specified anything for it to do. This declaration will run absolutely fine and our table with fields will be created, but no constraint will be created against the table for this.

How very useless.

Wrap up

Here we’ve wrapped up our list of constraints that can help us to provide assurances for data integrity in our tables. These constraints both help to protect quality in our data and also help to describe our data a little more to the SQL engine or fellow developers.

In addition to the Primary Key, Foreign Key and Unique Constraints last time out, we’ve now looked at Default Constraints, Check Constraints, and NOT NULL constraints, all with examples of their usage.

I’d like to hear from folks about the constraints you typically use, if there’s any they’d like to know more about, and if you’re for or against using any of these in particular?