CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Rushabh Parekh
Rushabh Parekh

Reputation: 23

SQL Server 2008 check constraint not working

CREATE TABLE [dbo].[StateMaster]([StateId] [int] IDENTITY(1,1) NOT NULL,[StateName] [varchar](30) NOT NULL,[IsDisable] [bit] NOT NULL,CONSTRAINT CHK_StateDisable CHECK *(**IsDisable>=0 and IsDisable<=1**)*,
CONSTRAINT [PK_StateMaster] PRIMARY KEY CLUSTERED 
(
    [StateId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[StateMaster] ADD  CONSTRAINT [DF_StateMaster_IsDisable]  DEFAULT ((0)) FOR [IsDisable]
GO

After executing this insert:

insert into StateMaster values ('ABC',***2***)`

I get this output:

select * from StateMaster


StateId StateName   IsDisable
1       ABC         **1**

Upvotes: 1

Views: 43

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

You are not getting the expected output because you are defining the column IsDisable as BIT which can accept only True/False or 1/0. So For BIT datatype all values except 0 and NULL is 1.

SELECT CAST(NULL AS BIT),
       CAST(0 AS BIT),
       CAST(1 AS BIT),
       CAST(4 AS BIT);

Gives you the below result

enter image description here

So You may try changing the data type of IsDisable to INT and then try the same insert. Iwillll throw the following error

Msg 547, Level 16, State 0, Line 17 The INSERT statement conflicted with the CHECK constraint "CHK_StateDisable". The conflict occurred in database "MyDb", table "dbo.StateMaster", column 'IsDisable'. The statement has been terminated.

Upvotes: 3

Related Questions