Data quality is an important consideration for any business. Data validations help in ensuring that businesses can operate successfully, take appropriate decisions and plan for the future. In Microsoft SQL Server, data validations are generally embedded into the database design by the use of constraints.

Constraints allow us to validate business and integrity rules regarding the values allowed in columns. Because a table may have multiple constraints defined on various columns in the table, I was interested in the sequence in which various constraints are evaluated when an attempt is made to INSERT data into a table in Microsoft SQL Server. My research towards answering this question is documented in this article.

Microsoft SQL Server allows us to define the six (6) types of constraints, a very high-level definition of each is provided below:

NOT NULL constraint - Prevents a column from accepting NULL values
CHECK constraint - Checks the values being inserted against a defined set of business rules for valid data range values in the column
UNIQUE constraint - Ensures that the given column is unique across all rows in the table
PRIMARY KEY - Uniquely identifies a row in a table
FOREIGN KEY - They identify and enforce relationships between tables
DEFAULT constraint - Ensures that when an explicit value is not specified by the client, a default value is used so as not to break logical integrity of the data

In order for me to study the constraint evaluation sequence, I came up with a set of requirements for a simple, hypothetical User Register screen followed by a hypothetical database design and test data.

The Requirements

Following are the requirements for a simple, hypothetical User Register screen. User Information should be evaluated for:

The following fields must NOT be NULL
1. User Name
2. Password
3. User Age
4. Country of Residence
User must be at least 18 years of age
User must have a well-formed E-mail address
User must belong to a pre-defined list of countries
User Name must start with a character
User Name must be unique
If the password is not supplied
1. The supplied password must be at least 8 characters in length
2. The default password should be set to a default value of “Sqlpwd!@3”
3. User should be promoted for choosing a new password during the next login if the default value was used

In addition, there are covering requirements:

Requirements must be evaluated in the order mentioned
The evaluation sequence must be the same irrespective of whether the insert is being attempted via the application or via a 3^rd party tool/interface/manual script

Setup

To setup my experiment, I created the following set of tables.

Table Design: dbo.Country
Table Name	dbo.Country
Column_name	Type	Length	Nullable
CountryId	int	4	no
CountryName	varchar	100	no
CountryISOCode	varchar	2	no

Table Design: dbo.UserRegistration
Table Name	dbo.UserRegistration
Column_name	Type	Length	Nullable
UserId	int	4	no
UserName	varchar	20	no
Password	varchar	20	no
UserEmail	varchar	50	yes
BirthDate	date	3	no
CountryId	int	4	no
IsDefaultPasswordUsed	int	4	no

The script for creating the tables is provided below:

USE tempdb;
GO
SET NOCOUNT ON;
GO
/**************** STEP 01: DEFINE THE TABLES ********************/--Safety Check
IF OBJECT_ID('dbo.UserRegistration','U') IS NOT NULL
    DROP TABLE dbo.UserRegistration;
GO
IF OBJECT_ID ('dbo.Country','U') IS NOT NULL
    DROP TABLE dbo.Country;
GO
--Create the table
CREATE TABLE dbo.Country (CountryId INT NOT NULL IDENTITY(1,1),
                          CountryName VARCHAR(100) NOT NULL,
                          CountryISOCode VARCHAR(2) NOT NULL
                         );
GO
CREATE TABLE dbo.UserRegistration (UserId INT NOT NULL IDENTITY(1,1),
                                   UserName VARCHAR(20) NOT NULL,
                                   Password VARCHAR(20) NOT NULL,
                                   UserEmail VARCHAR(50) NULL,
                                   BirthDate DATE NOT NULL,
                                   CountryId INT NOT NULL,
                                   IsDefaultPasswordUsed AS CASE WHEN Password = 'Sqlpwd!@3'
                                                                 THEN 1
                                                                 ELSE 0
                                                                 END
                                  );
GO

Next, I implemented the given business requirements via the following set of constraints.

Constraints on Table: dbo.Country
Table Name	dbo.Country
constraint_type	constraint_name	constraint_keys
PRIMARY KEY (clustered)	pk_CountryCountryId	CountryId

Constraints on Table: dbo.UserRegistration
Table Name	dbo.UserRegistration
constraint_type	constraint_name	constraint_keys
CHECK on column BirthDate	chk_UserRegistrationBirthDate	(datediff(year,[BirthDate],getdate())>=(18))
CHECK on column Password	chk_UserRegistrationPassword	(len(ltrim(rtrim([Password])))>=(8))
CHECK on column UserEmail	chk_UserRegistrationUserEmail	([UserEmail] like '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%')
CHECK on column UserName	chk_UserRegistrationUserName	([UserName] like '[A-Za-z]%')
DEFAULT on column Password	df_UserRegistrationPassword	('Sqlpwd!@3')
FOREIGN KEY	fk_UserRegistration_Country	CountryId
		REFERENCES tempdb.dbo.Country (CountryId)
PRIMARY KEY (clustered)	pk_UserRegistrationUserId	UserId
UNIQUE (non-clustered)	uk_UserRegistrationUserName	UserName

The script for these constraints is provided below:

USE tempdb;
GO
SET NOCOUNT ON;
GO
--Primary key
ALTER TABLE dbo.Country
    ADD CONSTRAINT pk_CountryCountryId PRIMARY KEY CLUSTERED (CountryId);
GO
--Primery Key
ALTER TABLE dbo.UserRegistration
    ADD CONSTRAINT pk_UserRegistrationUserId PRIMARY KEY CLUSTERED (UserId);
GO
--User must belong to a pre-defined set of countries
ALTER TABLE dbo.UserRegistration
    ADD CONSTRAINT fk_UserRegistration_Country FOREIGN KEY (CountryId) REFERENCES dbo.Country (CountryId);
GO
--User Name must start with a character
ALTER TABLE dbo.UserRegistration
    ADD CONSTRAINT chk_UserRegistrationUserName CHECK (UserName LIKE '[A-Za-z]%');
GO
--User name must be unique
ALTER TABLE dbo.UserRegistration
    ADD CONSTRAINT uk_UserRegistrationUserName UNIQUE (UserName);
GO
--If a no password is supplied, set it to default 'Sqlpwd!@3'
ALTER TABLE dbo.UserRegistration
    ADD CONSTRAINT df_UserRegistrationPassword DEFAULT ('Sqlpwd!@3') FOR [Password];
GO
--Password must be a minimum of 8 characters
ALTER TABLE dbo.UserRegistration
    ADD CONSTRAINT chk_UserRegistrationPassword CHECK (LEN(LTRIM(RTRIM(Password))) >= 8);
GO
--User must have a valid E-mail 
--(basic checking done here)
ALTER TABLE dbo.UserRegistration
    ADD CONSTRAINT chk_UserRegistrationUserEmail CHECK (UserEmail LIKE '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%');
GO
--User must be at least 18 years in age
ALTER TABLE dbo.UserRegistration
    ADD CONSTRAINT chk_UserRegistrationBirthDate CHECK (DATEDIFF(YEAR,BirthDate,GETDATE()) >= 18);
GO

Studying Constraint Execution Sequence

Because we have a very specific sequence of validation defined in the business requirements, I used the following statements to insert some test data:

USE tempdb;
GO
SET NOCOUNT ON;
GO
INSERT INTO dbo.Country(CountryName, CountryISOCode)
VALUES ('India','IN'),
       ('The Great Britain','GB'),
       ('The United States','US'),
       ('Virgin Islands (U.S.)','VI'),
       ('Mexico','MX'),
       ('Puerto Rico','PR'),
       ('Canada','CA');
GO
--All fields valid
PRINT '
All fields valid, User name not unique. 
       Expected Result: Must pass.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
SELECT 'AUser3' AS UserName,
       'AUser3@sql' AS Password,
       '01-01-1950' AS BirthDate,
       cntry.CountryId AS CountryId,
       'abc@xyz.com' AS UserEmail
FROM dbo.Country AS cntry
WHERE cntry.CountryISOCode = 'IN';
GO
--All fields NULL
PRINT '
Add data into dbo.UserRegistration table with all mandatory fields NULL.
       Expected Result: FAIL on account of NULL BirthDate
';
GO
INSERT INTO dbo.UserRegistration (BirthDate, Password, UserName, CountryId, UserEmail)
VALUES (NULL,NULL,NULL,NULL,NULL);
GO
--Password undefined, All fields NULL
PRINT '
Add data into dbo.UserRegistration table with --Password undefined, All fields NULL.
       Expected Result: FAIL on account of NULL BirthDate
';
GO
INSERT INTO dbo.UserRegistration (BirthDate, UserName, CountryId, UserEmail)
VALUES (NULL,NULL,NULL,NULL);
GO
--Password NULL, UserName Invalid, all others valid fields
PRINT '
Password NULL, UserName Invalid, all others valid fields
       Expected Result: FAIL on account of NULL fields.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
SELECT '007' AS UserName,
       NULL AS Password,
       '01-01-1950' AS BirthDate,
       cntry.CountryId AS CountryId,
       'abc@xyz.com' AS UserEmail
FROM dbo.Country AS cntry
WHERE cntry.CountryISOCode = 'IN';
GO
--Password Undefined, UserName Invalid, all others valid fields
PRINT '
Password Undefined, UserName Invalid, all others valid fields
       Expected Result: FAIL on account invalid UserName.
';
GO
INSERT INTO dbo.UserRegistration (UserName, BirthDate, CountryId, UserEmail)
SELECT '007' AS UserName,
       '01-01-1950' AS BirthDate,
       cntry.CountryId AS CountryId,
       'abc@xyz.com' AS UserEmail
FROM dbo.Country AS cntry
WHERE cntry.CountryISOCode = 'IN';
GO
--User under age limit, all others valid fields
PRINT '
User under age limit, all others valid fields
       Expected Result: FAIL on account of invalid BirthDate.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
SELECT 'AUser1' AS UserName,
       'AUser1@sql' AS Password,
       '01-01-2014' AS BirthDate,
       cntry.CountryId AS CountryId,
       'abc@xyz.com' AS UserEmail
FROM dbo.Country AS cntry
WHERE cntry.CountryISOCode = 'IN';
GO
--User under age limit, country invalid, all others valid fields
PRINT '
User under age limit, country invalid, all others valid fields
       Expected Result: FAIL on account of invalid BirthDate.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
VALUES ('AUser2', 'AUser2@sql','01-01-2014', 99,'abc@xyz.com');
GO
--All fields valid, User name not unique
PRINT '
All fields valid, User name not unique
       Expected Result: FAIL on account of non-unique UserName.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
SELECT 'AUser3' AS UserName,
       'AUser3@sql' AS Password,
       '01-01-1950' AS BirthDate,
       cntry.CountryId AS CountryId,
       'abc@xyz.com' AS UserEmail
FROM dbo.Country AS cntry
WHERE cntry.CountryISOCode = 'IN';
GO

Here's the result:

All fields valid, User name not unique.

Expected Result: Must pass.

Add data into dbo.UserRegistration table with all mandatory fields NULL.

Expected Result: FAIL on account of NULL BirthDate

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'BirthDate', table 'tempdb.dbo.UserRegistration'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Add data into dbo.UserRegistration table with --Password undefined, All fields NULL.

Expected Result: FAIL on account of NULL BirthDate

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'BirthDate', table 'tempdb.dbo.UserRegistration'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Password NULL, UserName Invalid, all others valid fields

Expected Result: FAIL on account of NULL fields.

Msg 515, Level 16, State 2, Line 1

Cannot insert the value NULL into column 'Password', table 'tempdb.dbo.UserRegistration'; column does not allow nulls. INSERT fails.

The statement has been terminated.

Password Undefined, UserName Invalid, all others valid fields

Expected Result: FAIL on account invalid UserName.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_UserRegistrationUserName". The conflict occurred in database "tempdb", table "dbo.UserRegistration", column 'UserName'.

The statement has been terminated.

User under age limit, all others valid fields

Expected Result: FAIL on account of invalid BirthDate.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_UserRegistrationBirthDate". The conflict occurred in database "tempdb", table "dbo.UserRegistration", column 'BirthDate'.

The statement has been terminated.

User under age limit, country invalid, all others valid fields

Expected Result: FAIL on account of invalid BirthDate.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_UserRegistrationBirthDate". The conflict occurred in database "tempdb", table "dbo.UserRegistration", column 'BirthDate'.

The statement has been terminated.

All fields valid, User name not unique

Expected Result: FAIL on account of non-unique UserName.

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'uk_UserRegistrationUserName'. Cannot insert duplicate key in object 'dbo.UserRegistration'. The duplicate key value is (AUser3).

The statement has been terminated.

As can be confirmed by a self-test (and is also shown in the results provided above), the tests were successful. However, the following test did not work as expected:

USE tempdb;
GO
SET NOCOUNT ON;
GO
--User under age limit, name invalid, all others valid fields
PRINT '
User under age limit, name invalid, all others valid fields
       Expected Result: FAIL on account of invalid BirthDate.
       Actual Result: FAILs on account of invalid user name
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
SELECT '007' AS UserName,
       'AUser7@sql' AS Password,
       '01-01-2014' AS BirthDate,
       cntry.CountryId AS CountryId,
       'abc@xyz.com' AS UserEmail
FROM dbo.Country AS cntry
WHERE cntry.CountryISOCode = 'IN';
GO
--User E-mail invalid, Age invalid, all others valid fields
PRINT '
User E-mail invalid, Age invalid, all others valid fields
       Expected Result: FAIL on account of invalid Age.
       Actual Result: FAILs on account of invalid E-mail
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
SELECT 'JB007' AS UserName,
       'AUser7@sql' AS Password,
       '01-01-2014' AS BirthDate,
       cntry.CountryId AS CountryId,
       'abc-at-the-rate-of-xyz.com' AS UserEmail
FROM dbo.Country AS cntry
WHERE cntry.CountryISOCode = 'IN';
GO

User under age limit, name invalid, all others valid fields

Expected Result: FAIL on account of invalid BirthDate.

Actual Result: FAILs on account of invalid user name

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_UserRegistrationUserName". The conflict occurred in database "tempdb", table "dbo.UserRegistration", column 'UserName'.

The statement has been terminated.

User E-mail invalid, Age invalid, all others valid fields

Expected Result: FAIL on account of invalid Age.

Actual Result: FAILs on account of invalid E-mail

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_UserRegistrationUserEmail". The conflict occurred in database "tempdb", table "dbo.UserRegistration", column 'UserEmail'.

The statement has been terminated.

The solution

The solution to achieving the business requirement is in realizing that the constraint evaluation sequence depends upon the sequence in which the constraints were created.

The script below drops all previously created constraints and re-creates them in the order mandated by the business requirements:

USE tempdb;
GO
SET NOCOUNT ON;
GO
ALTER TABLE dbo.UserRegistration
DROP CONSTRAINT chk_UserRegistrationUserName,
                uk_UserRegistrationUserName,
                df_UserRegistrationPassword,
                chk_UserRegistrationPassword,
                chk_UserRegistrationUserEmail,
                chk_UserRegistrationBirthDate
GO
--User must be at least 18 years in age
ALTER TABLE dbo.UserRegistration
      ADD CONSTRAINT chk_UserRegistrationBirthDate CHECK (DATEDIFF(YEAR,BirthDate,GETDATE()) >= 18);
GO
--User must have a valid E-mail 
--(basic checking done here)
ALTER TABLE dbo.UserRegistration
      ADD CONSTRAINT chk_UserRegistrationUserEmail CHECK (UserEmail LIKE '[a-z,0-9,_,-]%@[a-z,0-9,_,-]%.[a-z][a-z]%');
GO
--User Name must start with a character
ALTER TABLE dbo.UserRegistration
      ADD CONSTRAINT chk_UserRegistrationUserName CHECK (UserName LIKE '[A-Za-z]%');
GO
--User name must be unique
ALTER TABLE dbo.UserRegistration
      ADD CONSTRAINT uk_UserRegistrationUserName UNIQUE (UserName);
GO
--If a no password is supplied, set it to default 'Sqlpwd!@3'
ALTER TABLE dbo.UserRegistration
      ADD CONSTRAINT df_UserRegistrationPassword DEFAULT ('Sqlpwd!@3') FOR [Password];
GO
--Password must be a minimum of 8 characters
ALTER TABLE dbo.UserRegistration
      ADD CONSTRAINT chk_UserRegistrationPassword CHECK (LEN(LTRIM(RTRIM(Password))) >= 8);
GO

If we perform the failing test again, we can see that the validations happen in the order required by the business.

USE tempdb;
GO
SET NOCOUNT ON;
GO
--User under age limit, name invalid, all others valid fields
PRINT '
User under age limit, name invalid, all others valid fields
     Expected Result: FAIL on account of invalid BirthDate.
     Actual Result: FAIL on account of invalid BirthDate.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
SELECT '007' AS UserName,
       'AUser7@sql' AS Password,
       '01-01-2014' AS BirthDate,
       cntry.CountryId AS CountryId,
       'abc@xyz.com' AS UserEmail
FROM dbo.Country AS cntry
WHERE cntry.CountryISOCode = 'IN';
GO
--User E-mail invalid, Age invalid, all others valid fields
PRINT '
User E-mail invalid, Age invalid, all others valid fields
       Expected Result: FAIL on account of invalid Age.
       Actual Result: FAILs on account of invalid Age.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
SELECT 'JB007' AS UserName,
       'AUser7@sql' AS Password,
       '01-01-2014' AS BirthDate,
       cntry.CountryId AS CountryId,
       'abc-at-the-rate-of-xyz.com' AS UserEmail
FROM dbo.Country AS cntry
WHERE cntry.CountryISOCode = 'IN';
GO

User under age limit, name invalid, all others valid fields

Expected Result: FAIL on account of invalid BirthDate.

Actual Result: FAIL on account of invalid BirthDate.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_UserRegistrationBirthDate". The conflict occurred in database "tempdb", table "dbo.UserRegistration", column 'BirthDate'.

The statement has been terminated.

User E-mail invalid, Age invalid, all others valid fields

Expected Result: FAIL on account of invalid Age.

Actual Result: FAILs on account of invalid Age.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_UserRegistrationBirthDate". The conflict occurred in database "tempdb", table "dbo.UserRegistration", column 'BirthDate'.

The statement has been terminated.

Studying Constraint Execution Sequence-Keys

Once we adjusted the sequence, let us perform another test:

USE tempdb;
GO
SET NOCOUNT ON;
GO
--Cannot be fixed because Country validation is done through keys
--User Country invalid, name invalid, all others valid fields
PRINT '
User Country invalid, name invalid, all others valid fields
     Expected Result: FAIL on account of invalid Country.
     Actual Result: FAILs on account of invalid user name.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
VALUES ('007', 'AUser7@sql','01-01-1950', 99,'abc@xyz.com');
GO

User Country invalid, name invalid, all others valid fields

Expected Result: FAIL on account of invalid Country.

Actual Result: FAILs on account of invalid user name.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_UserRegistrationUserName". The conflict occurred in database "tempdb", table "dbo.UserRegistration", column 'UserName'.

The statement has been terminated.

We can see that the validation did not work as expected. The business requires us to validate the country information before we go ahead and check for the user name.

Howerver, this particular requirement cannot be realized, and here’s why:

Foreign Keys are the last to be evaluated

Foreign keys ensure relational integrity between two tables. If the record fails to pass the constraints defined on the referencing table, there is no point in validating the data against the referenced table.

For SQL Server to evaluate the relationship between two tables, it should be established first that the record in the referencing table passes all check constraints and the primary key. Here’s a set of scripts that prove this theory:

USE tempdb;
GO
SET NOCOUNT ON;
GO
--Cannot be fixed because Country validation is done through keys
--User Country invalid, name invalid, all others valid fields
PRINT '
User Country invalid, name invalid, all others valid fields
     Expected Result: FAIL on account of invalid Country.
     Actual Result: FAILs on account of invalid user name.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
VALUES ('007', 'AUser7@sql','01-01-1950', 99,'abc@xyz.com');
GO
--UserId invalid, User Country invalid, all others valid fields
PRINT '
UserId invalid, User Country invalid, all others valid fields
        Expected Result: FAIL on account of invalid Country.
        Actual Result: FAIL on account of invalid UserId.
';
GO
SET IDENTITY_INSERT dbo.UserRegistration ON;
GO
INSERT INTO dbo.UserRegistration (UserId, UserName, Password, BirthDate, CountryId, UserEmail)
VALUES (1, 'JB007', 'AUser7@sql','01-01-1950', 99,'abc@xyz.com');
GO
SET IDENTITY_INSERT dbo.UserRegistration OFF;
GO
--User Country invalid, all others valid fields
PRINT '
User Country invalid, all others valid fields
        Expected Result: FAIL on account of invalid Country.
        Actual Result: FAIL on account of invalid Country.
';
GO
INSERT INTO dbo.UserRegistration (UserName, Password, BirthDate, CountryId, UserEmail)
VALUES ('JB007', 'AUser7@sql','01-01-1950', 99,'abc@xyz.com');
GO

User Country invalid, name invalid, all others valid fields

Expected Result: FAIL on account of invalid Country.

Actual Result: FAILs on account of invalid user name.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "chk_UserRegistrationUserName". The conflict occurred in database "tempdb", table "dbo.UserRegistration", column 'UserName'.

The statement has been terminated.

UserId invalid, User Country invalid, all others valid fields

Expected Result: FAIL on account of invalid Country.

Actual Result: FAIL on account of invalid UserId.

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'pk_UserRegistrationUserId'. Cannot insert duplicate key in object 'dbo.UserRegistration'. The duplicate key value is (1).

The statement has been terminated.

User Country invalid, all others valid fields

Expected Result: FAIL on account of invalid Country.

Actual Result: FAIL on account of invalid Country.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "fk_UserRegistration_Country". The conflict occurred in database "tempdb", table "dbo.Country", column 'CountryId'.

The statement has been terminated.

Conclusion

Although a table may have multiple constraints on a table, we can establish the following constraint evaluation sequence based on the tests conducted above:

Summarizing Constraint Evaluation Sequence
Constraint Type	Evaluation Order	Remarks
NOT NULL	2	Evaluated in the order of the Column List in an INSERT/UPDATE statement
CHECK	3	In case of multiple constraints, they are evaluated in order of creation
UNIQUE	4
PRIMARY KEY	5
FOREIGN KEY	6
DEFAULT	1	If undefined fields are found in the Column List in an INSERT statement, the default value is substituted first.

This sequence can be leveraged in the deployment DDL script to allow a specific sequence of evaluation even if the data insert is done from the back-end via a 3^rd party tool/interface/manual script.

For your reference the scripts used in this study are available as Resources on this article.

Disclaimer

A few disclaimers about this information and code:

The scripts in this article are provided "as-is", i.e. without warranties of any kind and are intended for demonstration purposes only
Request you to use these scripts for understanding and study purposes in your development environments only - they are not meant for use in production. The author is not responsible for any damage caused by misuse of these scripts
Scripts are tested on SQL Server 2012 SP1
The opinions expressed herein are his own personal opinions and do not represent his employer’s view in any way

About the author

Nakul Vachhrajani is a Technical Specialist and systems development professional with iGATE having a total IT experience of more than 9 years. Nakul is an active blogger with BeyondRelational.com (310+ blogs), and can also be found on forums at SQLServerCentral and BeyondRelational.com. Nakul has also been a guest columnist for SQLAuthority.com and SQLServerCentral.com. Nakul presented a webcast on the “Underappreciated Features of Microsoft SQL Server” at the Microsoft Virtual Tech Days Exclusive Webcast series (May 02-06, 2011) on May 06, 2011. He is also the author of a research paper on Database upgrade methodologies, which was published in a Computer Society of India (CSI) journal, published nationwide. In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students.

Blog | LinkedIn | Twitter | Google+

Leveraging Constraint Evaluation Sequence in SQL Server

The Requirements

Setup

Studying Constraint Execution Sequence

The solution

Studying Constraint Execution Sequence-Keys

Foreign Keys are the last to be evaluated

Conclusion

Disclaimer

About the author

Resources

Rate

Share

Categories

Share

Rate

Leveraging Constraint Evaluation Sequence in SQL Server

The Requirements

Setup

Studying Constraint Execution Sequence

The solution

Studying Constraint Execution Sequence-Keys

Foreign Keys are the last to be evaluated

Conclusion

Disclaimer

About the author

Resources

Rate

Share

Categories

Share

Rate

Related content

Changing SQL Server Database Logical Names

Session complete status and estimated completion time

On Error () Logging by Using Event Handlers

SQL Server consolidation strategies and best practices

Kilimanjaro, Gemini and Madison - Get the lowdown