Thursday, October 19, 2017

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



No comments: