SQL Server Constraints – Is a Check Constraint Applied Before or After a Default Constraint on Insert?

check-constraintsconstraintdefault valuesql server

As per the title: In SQL server is a check constraint applied before or after a default constraint on insert?

So taking the following table (Unnecessary columns omitted for brevity)…

CREATE TABLE [dbo].[Customer](
    [CountryCode] [varchar](5) NULL CONSTRAINT [CK_Customer_DefaultCountryCode]  DEFAULT ([app].[GetUnknownCountryIsoCountryCode]()),
) ON [PRIMARY]

…Which also has the following check constraint…

ALTER TABLE [admin].[Customer]  WITH CHECK 
    ADD  CONSTRAINT [CK_CustomerHasCountryWhenIntercompanyBusinessModel] 
    CHECK  (
        ([app].[BusinessModelMustHaveCountry]([BusinessModelId]) = (1) AND [app].[CheckCountryCodeExists]([CountryCode]) = (1) OR 
        [app].[BusinessModelMustHaveCountry]([BusinessModelId]) <> (1))
    )
GO

… will the default constraint be applied and populate the default value before the check constraint is applied?

Best Answer

The check constraint is applied after.

You can see this from the fact that the following succeeds.

DECLARE @T TABLE(X INT DEFAULT 1 CHECK (X = 1)); 

INSERT @T DEFAULT VALUES;

Also the execution plan shows that the assert operator operates on the values output from the table insert operator so is checking the values that were actually inserted.

Related Solutions

Postgresql – check key if exists in other table without fk constraint

I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement

But you could do something like this:

insert into b (id_b, id_a)
select 1, 2 
where exists (select 1 from a where id_a = 2);

or alternatively:

insert into b (id_b, id_a)
select 1, id_a 
from a where id_a = 2

If you want to insert multiple rows, you can use a values clause:

insert into b (id_b, id_a)
select * 
from (
   values 
      (1,2), (2,3), (3,4)
) as t(id_b, id_a) 
where exists (select 1  
              from a 
              where a.id_a = t.id_a);

This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.

SQL Server – Unique Index Per Subset of Data Based on Another Column

Create a unique constraint over two columns.

alter table dbo.Emails add constraint UQ_Emails unique (CustomerId, EmailValue);

This will prevent duplicate emails per customer. Different customers can still have the same email.

From comments:

What if I have an IsDefault field too? In that case, the requirement would be:

There should be only one active email per customer, and each customer should not have duplicate emails"

You can do that if you add a filtered unique index:

create unique nonclustered index UX_Emails 
on dbo.Emails (CustomerID) 
where IsDefault = 1;

Best Answer

Related Solutions

Postgresql – check key if exists in other table without fk constraint

SQL Server – Unique Index Per Subset of Data Based on Another Column

Related Question