Check Constraint fails!

Hi Everybody,
Can anybody help me on the following query...
I have a table structure as follows
CREATE TABLE [dbo].[event_logs] (
[WSE_Idx] [int] NULL ,
[WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Date_Generated] [datetime] NULL ,
[WSE_lDate_Generated] [datetime] NULL ,
[WSE_Date_Written] [datetime] NULL ,
[WSE_lDate_Written] [datetime] NULL ,
[WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
go
It contains data. I tried to create the following 'check constraint' to the above table
alter table event_logs
add constraint ck_event_logs
check((WSE_Category = 'application' and wse_log_type in ('Audit Success','error')) OR
(WSE_Category = 'system' and wse_log_type in ('Warning')) OR
(WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
It is giving the following error...
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE CHECK constraint 'ck_event_logs'.
The conflict occurred in database 'slm', table 'event_logs'.
Even I modified the above alter table script as follows, still it is giving the same error.
alter table event_logs
add constraint ck_event_logs
check(WSE_Category like '%applica%')
I created the similar table structure with different table name and applied the check constraint,
it works. No error. Ofcourse table doesn't have data (Empty table).
I have created RULE on this 'event_logs' table (with data). It works fine. No Error.
Can anybody tell me why this 'Check Constraint' is giving problem?.
tks in advance,
vasumData in a table are not valid for 'check constraint' that you specified.
You mast correct data in your table or in ALTER TABLE statement put WITH
NOCHECK option.
Look ALTER TABLE in BOL.
"vasum" <anonymous@.discussions.microsoft.com> wrote in message
news:748BD3CB-E545-4DEA-B05B-103EEF45BFAE@.microsoft.com...
> Hi Everybody,
> Can anybody help me on the following query...
> I have a table structure as follows
> CREATE TABLE [dbo].[event_logs] (
> [WSE_Idx] [int] NULL ,
> [WSE_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Date_Generated] [datetime] NULL ,
> [WSE_lDate_Generated] [datetime] NULL ,
> [WSE_Date_Written] [datetime] NULL ,
> [WSE_lDate_Written] [datetime] NULL ,
> [WSE_tzname] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Source] [varchar] (254) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Category] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Event] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_User_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Agent] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [WSE_Log_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> )
> go
> It contains data. I tried to create the following 'check constraint' to
the above table
> alter table event_logs
> add constraint ck_event_logs
> check((WSE_Category = 'application' and wse_log_type in ('Audit
Success','error')) OR
> (WSE_Category = 'system' and wse_log_type in ('Warning')) OR
> (WSE_Category = 'security' and wse_log_type in ('Audit Failure')))
> It is giving the following error...
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE CHECK constraint
'ck_event_logs'.
> The conflict occurred in database 'slm', table 'event_logs'.
> Even I modified the above alter table script as follows, still it is
giving the same error.
> alter table event_logs
> add constraint ck_event_logs
> check(WSE_Category like '%applica%')
> I created the similar table structure with different table name and
applied the check constraint,
> it works. No error. Ofcourse table doesn't have data (Empty table).
> I have created RULE on this 'event_logs' table (with data). It works fine.
No Error.
> Can anybody tell me why this 'Check Constraint' is giving problem?.
> tks in advance,
> vasum
>|||thanks for the timely help. I works. I used 'with nocheck' option. Able to create new check constraint and this new check constraint is validating the any new rows coming into the table