Constraints Check

-- drop table EmployeeFK

-- drop table EmpLoyeePK

create table EmployeeFK(EmployeeID int not null, fk int not null)

create table EmpLoyeePK(Employeeuniquenumber int not null)

-- create primary key on EmpLoyeePK

alter table EmpLoyeePK

add constraint EmpLoyeePKunique primary key (Employeeuniquenumber)

-- create foriegn key on EmployeeFK

alter table EmployeeFK

add constraint EmployeeFKID foreign key (fk)

    references EmpLoyeePK (Employeeuniquenumber)

--insert some records

insert EmpLoyeePK values(100)

insert EmpLoyeePK values(200)

insert EmpLoyeePK values(300)

insert EmpLoyeePK values(400)

insert EmpLoyeePK values(500)

insert EmployeeFK values(1,100)

insert EmployeeFK values(2,100)

insert EmployeeFK values(3,500)

insert EmployeeFK values(4,500)

----------------------------

-- 1. enabled and trusted

select name,is_disabled,is_not_trusted from sys.foreign_keys

where name ='EmployeeFKID' or name='EmpLoyeePKunique'

-- 2. disable the constraint

alter table EmployeeFK NOCHECK CONSTRAINT EmployeeFKID

select name,is_disabled,is_not_trusted from sys.foreign_keys

where name ='EmployeeFKID' or name='EmpLoyeePKunique'

-- 3. constraint is enabled now but data is not guaranteed and the result is not trusted.

-- which means SQL Server Engine needs to check the column and it is not trusted.

alter table  EmployeeFK CHECK CONSTRAINT EmployeeFKID

select name,is_disabled,is_not_trusted from sys.foreign_keys

where name ='EmployeeFKID' or name='EmpLoyeePKunique'

--4. To workaround this , drop the constraint and recreate from the scratch

--SQL Server will ensure data is guaranteed no violation and checked.

alter table EmployeeFK  DROP CONSTRAINT EmployeeFKID

alter table EmployeeFK WITH CHECK

add constraint EmployeeFKID foreign key (fk)

    references EmpLoyeePK (Employeeuniquenumber)

select name,is_disabled,is_not_trusted from sys.foreign_keys

where name ='EmployeeFKID' or name='EmpLoyeePKunique'