Disable and Enable the Foreign Key Constraint on the table in SQL Server

     Its  interesting concept but need to know while working on huge database.Recently my friend attended one of the interview .He was rejected there.Interviewer asked him one question which he found difficult while attending that interview.So he asked me to help him to understand the solution.
   He said I  never heard of disabling and enabling constraints but I heard about triggers.Sometime we work on test environment while development. Whenever we deliver any feature to QA team .They test lot of positive and negative scenario for regression.
Let us assume there are some negative cases which we have to test  .Let say we have two table
1.Table having primary key .
2. Table having Foreign key.
e,.g

 CREATE TABLE TEST_PRIMARY
  (
     ID INT PRIMARY KEY
  )

GO

CREATE TABLE TEST_FOREIGN
  (
     ID     INT IDENTITY (1, 1),
     PVALUE INT,
     FOREIGN KEY (PVALUE) REFERENCES TEST_PRIMARY(ID)
  )

GO

INSERT TEST_PRIMARY
       (ID)
SELECT 1
UNION
SELECT 2
UNION
SELECT 3

GO

INSERT INTO TEST_FOREIGN
            (PVALUE)
VALUES      (1),
            (2),
            (3)

GO
  Now QA want to test  some negative case for example they are interested to know how system will behave if  constraints are not working properly means there are no constraints on table.

e.g

Syntax :
EXEC Sp_msforeachtable   "ALTER TABLE <table Name> NOCHECK CONSTRAINT all"
Query :
EXEC Sp_msforeachtable   "ALTER TABLE  TEST_FOREIGN NOCHECK CONSTRAINT all" 

Now if  you will try to  insert value into TEST_FOREIGN other than reference value it will allow.

e.g.

INSERT INTO TEST_FOREIGN
            (PVALUE)
VALUES      (4),

  Normally it should not allow.

Now  we can enable constraints back again
e.g
EXEC sp_msforeachtable "ALTER TABLE <table name> WITH CHECK CHECK CONSTRAINT all"

EXEC sp_msforeachtable "ALTER TABLE Test_foreign WITH CHECK CHECK CONSTRAINT all"

Note : before enabling constraint we need to delete data which are conflicting  primary key relation from TEST_FOREIGN table.