#0285 – SQL Server – Using Regular Expressions with CHECK constraints


I was recently asked a good question by one of the team members. They wanted to know if it would be possible to control the insertion of values in a table to match a given set of business rules via check constraints. What attracted my attention and prompted me to spend some time with them was the fact that they knew CHECK constraints were the answer, but were unaware about how to use CHECK constraints in this scenario.


The solution that I provided was to use regular expressions in the CHECK constraint definition. Here’s a small example that will guide the reader on using regular expressions in CHECK constraints.


The example


Assume that the fictional AdventureWorks BiCycle Company has a business rule that all invoices being processed must begin with the letters AWC followed by a 5-digit number beginning at 10000, making the pattern (AWCxyyyy, where x = some integer greater than or equal to 1, y = some integer).


We will attempt to achieve this via a simple CHECK constraint. To begin with, we will create a sample temporary table:

USE tempdb;
GO
–Safety Check
IF OBJECT_ID(‘tempdb..#InvoicesProcessed’) IS NOT NULL
DROP TABLE #InvoicesProcessed;
GO

–Create a dummy table
CREATE TABLE #InvoicesProcessed
(InvoiceNumber VARCHAR(10) NOT NULL,
ProcessDate DATETIME
)
GO

–Add a simple default constraint for the ProcessDate field
ALTER TABLE tempdb..#InvoicesProcessed
ADD CONSTRAINT df_ProcessDate DEFAULT GETDATE() FOR ProcessDate;
GO


Using Regular Expressions in a CHECK constraint definition


Now comes time to create the CHECK constraint that will actually implement the business logic. We will be using regular expressions in the constraint. To summarize, the requirements for an Invoice Number are:



  • Total length = 8 characters
  • 1st 3 characters must be AWC
  • Next 5 characters are numbers
  • The sequence of numbers must begin from 10000

The pattern therefore is (AWCxyyyy, where x = some integer greater than or equal to 1, y = some integer), which translates to the following CHECK constraint:

USE tempdb;
GO
–Now. add the CHECK constraint with the regular expressions
ALTER TABLE tempdb..#InvoicesProcessed
ADD CONSTRAINT chk_InvoiceNumber
CHECK (InvoiceNumber LIKE ‘AWC[1-9][0-9][0-9][0-9][0-9]’);
GO

The Test


Let us know test the constraint by adding some valid and some invalid values:

USE tempdb;
GO
–Add some valid test data
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES (‘AWC10021’);
GO

–Add some invalid test data
–Failure Reason: 1st numeric value after AWC must be greater than 1
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES (‘AWC00021’);
GO

–Failure Reason: Does not start with AWC
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES (‘00010021’);
GO

–Failure Reason: Trailing characters are not numeric
INSERT INTO #InvoicesProcessed (InvoiceNumber)
VALUES (‘AWC100AB’);
GO


As can be seen from the messages tab, we notice that only 1 record was added to the table – the others failed because they did not meet the Invoice Number requirements.


(1 row(s) affected)
Msg 547, Level 16, State 0, Line 4
The INSERT statement conflicted with the CHECK constraint “chk_InvoiceNumber”. The conflict occurred in database “tempdb”, table “dbo.#InvoicesProcessed__________________________________________________________________________________________________000000000004”, column ‘InvoiceNumber’.

The statement has been terminated.
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint “chk_InvoiceNumber”. The conflict occurred in database “tempdb”, table “dbo.#InvoicesProcessed__________________________________________________________________________________________________000000000004”, column ‘InvoiceNumber’.

The statement has been terminated.
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the CHECK constraint “chk_InvoiceNumber”. The conflict occurred in database “tempdb”, table “dbo.#InvoicesProcessed__________________________________________________________________________________________________000000000004”, column ‘InvoiceNumber’.

The statement has been terminated.


Let us validate the Inserts by SELECTing data from the table.

USE tempdb;
GO
–Select from the table
SELECT ip.InvoiceNumber,
ip.ProcessDate
FROM #InvoicesProcessed AS ip;
GO

image


Conclusion


Regular Expressions are extremely powerful and CHECK constraints is just one of the many Microsoft SQL Server provides areas where they can be leveraged to enforced the rules required by the business.


Further Reading:



  • Temporary Table Naming – Maximum Allowed Length and associated naming logic [Link]

Until we meet next time,



Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s