0

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 :)

Weafs.py
  • 22,731
  • 9
  • 56
  • 78
mounaim
  • 1,132
  • 7
  • 29
  • 56

1 Answers1

1

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.

Andrew
  • 7,602
  • 2
  • 34
  • 42
  • I opted for this option after realizing I couldn't disable the constraint in the same script as the one for the creation of my table..Between I didn't understand your last remark :) – mounaim Nov 07 '14 at 13:20
  • 1
    I used Management Studio's table designer to create the check constraint. It offers you those options and I disabled them all. You can see them in this image: http://i.stack.imgur.com/U80Q9.png – Andrew Nov 07 '14 at 15:33
  • Thank you Andrew for the hint :) – mounaim Nov 07 '14 at 15:49