CODEWITHSUNDEEP
sql-serversql-server-2008check-constraints
mounaim
mounaim

Reputation: 1180

How can I disable constraint at table creation time : SQL Server 2008

I want to create an additional table in my database : 

CREATE TABLE "DEM_TRACE" (
        "Id" NUMERIC(6), 
        "Indice" VARCHAR(3), 
        "Sec_Id" NUMERIC(10), 
        "QUOTE_PART_NUM" NUMERIC(38) DEFAULT 0, 
        "QUOTE_PART_DEN" NUMERIC(38) DEFAULT 1, 
        "OBSERV" NVARCHAR(250), 
        "DATE_CONV_BOR" DATETIME, 
        "DATE_RECE_CONV" DATETIME,
        "CODE_DROIT" NUMERIC(3) DEFAULT 73, 
        "NOM_PA" NVARCHAR(50), 
        WITH NOCHECK CONSTRAINT CK_QUOTE_PART_1
        CHECK(QUOTE_PART_NUM<=QUOTE_PART_DEN)
)

I want CK_QUOTE_PART_1 to be disabled after creation of my table. When I added the WITH NOCHECK clause before the constraint definition, I'm getting Msg 156 and Msg 319 errors pointing me to line 11 of my code (WITH NOCHECK CONSTRAINT CK_QUOTE_PART_1) : 

Message  : Incorrect Syntax near the word WITH

What is the problem with my code ?

Thank you in advance :)

Upvotes: 0

Views: 71

Answers (1)

Andrew
Andrew

Reputation: 7880

What if you run this?

CREATE TABLE "DEM_TRACE" (
        "Id" NUMERIC(6), 
        "Indice" VARCHAR(3), 
        "Sec_Id" NUMERIC(10), 
        "QUOTE_PART_NUM" NUMERIC(38) DEFAULT 0, 
        "QUOTE_PART_DEN" NUMERIC(38) DEFAULT 1, 
        "OBSERV" NVARCHAR(250), 
        "DATE_CONV_BOR" DATETIME, 
        "DATE_RECE_CONV" DATETIME,
        "CODE_DROIT" NUMERIC(3) DEFAULT 73, 
        "NOM_PA" NVARCHAR(50)
)
GO

ALTER TABLE [dbo]."DEM_TRACE"  WITH NOCHECK ADD  CONSTRAINT [CK_QUOTE_PART_1] CHECK NOT FOR REPLICATION ((QUOTE_PART_NUM<=QUOTE_PART_DEN))
GO

ALTER TABLE [dbo]."DEM_TRACE" NOCHECK CONSTRAINT [CK_QUOTE_PART_1]
GO

Note that I removed all checkings: existing data on creation, inserts, updates and replication.

Upvotes: 1

Related Questions