Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Leveraging Constraint Evaluation Sequence in SQL Server

By Nakul Vachhrajani,

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:

  1. The following fields must NOT be NULL
    1. User Name
    2. Password
    3. User Age
    4. Country of Residence
  2. User must be at least 18 years of age
  3. User must have a well-formed E-mail address
  4. User must belong to a pre-defined list of countries
  5. User Name must start with a character
  6. User Name must be unique
  7. 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 3rd 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 3rd 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+

Resources:

Constraint Evaluation Sequence - A Study.sql
Total article views: 2577 | Views in the last 30 days: 28
 
Related Articles
FORUM

Is immediate field level validation possible in T-Sql?

Will field level check constraints fire immediately or when posting?

FORUM

Check constraints validating HTML

Is it possible to write an expression for a column check constraint

FORUM

SQL SERVER CE COLUMN INVALID ERROR

Valid columns indicating column invalid

FORUM

Constraints

Constraint with a check

FORUM

Constraints

Violating the primary key constraint

Tags
administration    
constraints    
ddl    
sql server    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones