Your lack of constraints is disturbing

It has been a while since I wrote some of my best practices posts. I decided to revisit these posts again to see if anything has changed, I also wanted to see if I could add some additional info.

SQL Server supports the following types of constraints:

NOT NULL
CHECK
UNIQUE
PRIMARY KEY
FOREIGN KEY

Using constraints is preferred to using DML Triggers, rules, and defaults. The query optimizer will also use constraint definitions to build high-performance query execution plans.

When I interview people, I always ask how you can make sure only values between 0 and 9 are allowed in an integer column. I get a range of different answers to this question, here are some of them:

• Convert to char(1) and make sure it is numeric
• Write logic in the application that will check for this
• Use a trigger
• Create a primary key table with only the values from 0 till 9 then make this column a foreign key in the table you want to check for this

Only 25% of the people will tell you to use something that you can use from within SQL, and only 10% will actually know that this something is called a check constraint, the other ones know that there is something where you can specify some values to be used.

Why do we need constraints at all?

So why do we need constraints? To answer that question, first you have to answer another question: how important is it that the data in your database is correct? I would say that that is most important, after all you can have all the data in the world but if it is wrong it is useless or it might even ending up costing you money. To make sure that you don't get invalid data, you use constraints.

Constraints work at the database level, it doesn't matter if you do the data checking from the app or web front-end, there could be someone modifying the data from SSMS. If you are importing files, constraints will prevent invalid data from making it into the tables.

Constraints don't just have to have a range, constraints can handle complex validations. You can have regular expressions in check constraints as well, check out SQL Server does support regular expressions in check constraints, you don't always need triggers for some examples

Constraints are faster than triggers

The reason that check constraints are preferable over triggers is that they are not as expensive as triggers, you also don't need an update and an insert trigger, one constraint is enough to handle both updates and inserts.

Constraints are making it hard for us to keep our database scripts from blowing up

This is a common complaint, when you script out the databases and the primary and foreign key tables are not in the correct order you will get errors. Luckily the tools these days are much better than they were 10 years ago. If you do it by hand just make sure that it is all in the correct order. Another complaint is that constraints are wasting developers time, they can't just populate the tables at random but have to go in the correct order as well.

Some examples of constraints

First create this table

CREATE TABLE SomeTable(code char(3) NOT NULL)
GO

Now let's say we want to restrict the values that you can insert to only accept characters from a through z, here is what the constraint looks like

ALTER TABLE SomeTable ADD CONSTRAINT ck_bla
CHECK (code LIKE '[a-Z][a-Z][a-Z]' )
GO

If you now run the following insert statement....

INSERT SomeTable VALUES('123')

You get this error message back

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_bla". The conflict occurred in database "tempdb", table "dbo.SomeTable", column 'code'.
The statement has been terminated.

What if you have a tinyint column but you want to make sure that values are less then 100? Easy as well, first create this table

CREATE TABLE SomeTable2(SomeCol tinyint NOT NULL)
GO

Now add this constraint

ALTER TABLE SomeTable2 ADD CONSTRAINT ck_SomeTable2
CHECK (SomeCol < 100 )
GO

Try to insert the value 100

INSERT SomeTable2 VALUES('100')

Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "ck_SomeTable2". The conflict occurred in database "tempdb", table "dbo.SomeTable2", column 'SomeCol'.
The statement has been terminated.

Okay, what happens if you try to insert -1?

INSERT SomeTable2 VALUES('-1')

Msg 244, Level 16, State 1, Line 1
The conversion of the varchar value '-1' overflowed an INT1 column. Use a larger integer column.
The statement has been terminated.

As you can see you also get an error, however this is not from the constraint but the error is raised because the tinyint datatype can't be less than 0

Check constraint can also be tied to a user defined function and you can also use regular expressions. Ranges can also be used, for example salary >= 15000 AND salary <= 100000

For a post about foreign key constraints, go here: Foreign Keys don't always need a primary key

SQL Server does support regular expressions in check constraints, you don't always need triggers

Someone posted the following question

I need to add table called group with a column called code How do I add a check constraint to the column so it will only allow the following alphabetic characters (D, M, O, P or T) followed by 2 numeric characters.

Someone posted the following answer

You cannot do this out of the box - MS SQL Server does support CHECK CONSTRAINTS - but for things like a maximum or minimum INT value, or a string length or such. What you're looking for would be a CHECK based on a regular expression - and out of the box, SQL Server does not offer that capability. You could theoretically write a .NET assembly, deploy it inside SQL Server, and then use it to enforce the CHECK - not a trivial undertaking.

While SQL server does not support a full implementation of regular expression, you can do what the person asked for without a problem in T-SQL. Here is what the regular expression looks like

[DMOPT][0-9][0-9]

A constraint like that will allow allow the following alphabetic characters (D, M, O, P or T) followed by 2 numeric characters. Enough talking let's look at some code, first create this table

CREATE TABLE blatest(code char(3))

Now add the check constraint

ALTER TABLE blatest ADD  CONSTRAINT ck_bla 
CHECK (code like '[DMOPT][0-9][0-9]' )
GO

Now we can run some tests by inserting some data

INSERT blatest VALUES('a12') --fails
INSERT blatest VALUES('M12')  --good
INSERT blatest VALUES('D12') --good
INSERT blatest VALUES('DA1') --fails

As you can see we got the following message twice

Server: Msg 547, Level 16, State 1, Line 1 The INSERT statement conflicted with the CHECK constraint "ck_bla". The conflict occurred in database "Test", table "dbo.blatest", column 'code'. The statement has been terminated. 

If you want to insert D12 but not d12, in other words you need the constraint to be case sensitive.

You will need to create the constraint like this check (code like '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS ) What we did is used the SQL_Latin1_General_CP1_CS_AS collation, to find out what this collation does, run the following

SELECT * FROM ::fn_helpcollations()
WHERE name = 'SQL_Latin1_General_CP1_CS_AS'

Here is what is returned as the description Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data

Let's create the constraint, first we need to drop the old constraint

ALTER TABLE blatest DROP CONSTRAINt ck_bla
GO

Now we will create the new case sensitive constraint

ALTER TABLE blatest ADD CONSTRAINT ck_bla 
CHECK (code LIKE '[DMOPT][0-9][0-9]' COLLATE SQL_Latin1_General_CP1_CS_AS )
GO

INSERT blatest VALUES('D12') --good
INSERT blatest VALUES('d12') --fails

The insert with D12 will succeed but the one with d12 will not. As you can see you can use regular expressions in check constraints, there is no need to use a trigger in a case like this.

Dealing with temporary tables and named constraints

The other day one of our recently changed stored procedures in the development environment started to fail with a message like the following

There is already an object named 'PK_#SomeName' in the database.

I looked at the proc code and noticed something like the following

ALTER TABLE #test ADD CONSTRAINT PK_#test PRIMARY KEY CLUSTERED (id)

Books On Line specifies the following about constraint names

constraint_name

Is the name of a constraint. Constraint names must be unique within the schema to which the table belongs.

Before I give you an example of how you can get around this, let's first see how it breaks

Copy and paste the following code in a window in SQL Server Management Studio, execute the code below

CREATE TABLE #test (id int not null)


ALTER TABLE #test ADD CONSTRAINT PK_#test PRIMARY KEY CLUSTERED (id)

Now take the same exact code, paste it in a new window (use the same database) and execute it, you should see the following error

Msg 2714, Level 16, State 5, Line 3
There is already an object named 'PK_#test' in the database.
Msg 1750, Level 16, State 0, Line 3
Could not create constraint. See previous errors.

As you can see the message clearly tells you that there is already an object with that name in the database. So how can you get around this? There are two ways, the first is to use an unnamed constraint

Open a new window and execute the following

CREATE TABLE #test (id int NOT NULL, PRIMARY KEY (id))

)

You can now do this in a couple of new windows and it won't fail.

Just to prove that the constraint works as expected, run the following code in some of those windows

INSERT #test VALUES(1)
INSERT #test VALUES(1)

You will get a message similar to the one below

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__#test_____3213E83F8E75389B'. Cannot insert duplicate key in object 'dbo.#test'. The duplicate key value is (1).
The statement has been terminated.

Instead of a primary key, you could also use a unique index. Contrary to constraints names, index names do not have to be unique within the schema to which the table belongs

You can run the following code in a couple of windows and it won't fail

CREATE TABLE #test (id int not null)

CREATE UNIQUE CLUSTERED INDEX PK_#test on #test(id)

If you run the code below you will see that it will fail on the second insert

INSERT #test VALUES(1)

INSERT #test VALUES(1)

Here is the expected error message

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.#test' with unique index 'PK_#test'. The duplicate key value is (1).
The statement has been terminated.

So to sum it up, do NOT use named constraint with temporary tables, especially not inside stored procedures, if two sessions run the same proc you will get a failure


Use unnamed constraints or use a unique index on a non nullable column instead