I am using Enterprise Manager to create a relationship between two tables
(Bookings and BookingTypes).  The relationship on column BookingTypeSerialNo
(int) in both tables with no cascading actions.  BookingTypes contains a
column called OfficeCopies which is a tinyint.  I've created a check
constraint on the OfficeCopies column...

Constraint name: CK_BookingTypes_OfficeCopies
Constraint expression: ([OfficeCopies] <= 5)

When I try to create my relationship in Enterprise Manager I get the
following message displayed on screen which I click the "Save change script"
button...

'BookingTypes' table
- Error validating check constraint 'CK_BookingTypes_OfficeCopies'.


My question is why do I get this message.  What has the unrelated column got
to do with my script I am try to generate for the relationship creation.  If
I let it continue and run the script in Query Analyser it works fine, but I
was just wondering why this appears?

Thanks

Chris,

I'm guessing the Save Change Script is trying to validate the CHECK
constraint after you create the relationship - both ops will be included in
the script if SAVE was not issued after the creation of the CHECK
constraint.

HTH

Jerry
Show quoteHide quote

Strangely, if I change the constraint expression to:-

([OfficeCopies] < 6)

it works.  This is totally confusing.  It only works because there is one
character less than before.  Take this script...


CREATE TABLE [dbo].[BookingTypes] (
[BookingTypeSerialNo] [int] IDENTITY (1, 1) NOT NULL ,
[Type] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Code] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Color] [int] NOT NULL ,
[InvoiceRequired] [bit] NOT NULL ,
[InvoicePaperSize] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InvoiceNoPrefix] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[CustomInvoiceDesignSerialNo] [int] NULL ,
[OfficeCopies] [tinyint] NOT NULL ,
[BookingHeading] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[BookingDefaultDays] [smallint] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BookingTypes] WITH NOCHECK ADD
CONSTRAINT [PK_TestTypes] PRIMARY KEY  CLUSTERED
(
  [BookingTypeSerialNo]
)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[BookingTypes] ADD
CONSTRAINT [CK_TestTypes_OfficeCopies] CHECK ([OfficeCopies] <= 5)
GO


If you load it up in Enterprise Manager and change the Column Color to
Colour instead, the table saves fine without error.  Change it back to Color
and you get the Error validating check constraint message.  So just because
something in the whole table definition changes by one character it makes it
work or not work.

If I run SQL Profiler and try to change my check constraint from <= 5 to <=
6 it produces the following when it tries to check the constraint...

select 0 from ( select cast(1 as int) as 'BookingTypeSerialNo', cast(N'1' as
varchar(25)) as 'Type', cast(N'1' as varchar(6)) as 'Code', cast(1 as int)
as 'Color', cast(1 as bit) as 'InvoiceRequired', cast(N'1' as varchar(6)) as
'InvoicePaperSize', cast(N'1' as varchar(6)) as 'InvoiceNoPrefix', cast(1 as
int) as 'CustomInvoiceDesignSerialNo', cast(1 as tinyint) as 'OfficeCopies',
cast(N'1' as varchar(25)) as 'BookingHeading', cast(1 as smallint) as
'BookingDefaultDays' ) ExprTest where ([OfficeCopies] <= 6

The above has a missing bracket.  If I instead change the <= 5 to < 6 it
produces the following correct check (no missing brackets this time)...

select 0 from ( select cast(1 as int) as 'BookingTypeSerialNo', cast(N'1' as
varchar(25)) as 'Type', cast(N'1' as varchar(6)) as 'Code', cast(1 as int)
as 'Color', cast(1 as bit) as 'InvoiceRequired', cast(N'1' as varchar(6)) as
'InvoicePaperSize', cast(N'1' as varchar(6)) as 'InvoiceNoPrefix', cast(1 as
int) as 'CustomInvoiceDesignSerialNo', cast(1 as tinyint) as 'OfficeCopies',
cast(N'1' as varchar(25)) as 'BookingHeading', cast(1 as smallint) as
'BookingDefaultDays' ) ExprTest where ([OfficeCopies] < 6)


This is really strange.  I this just Enterprise Manager at fault?... If I
let it continue it still saves fine so I am guessing so.

Thanks,
Chris




Show quoteHide quote

Who Added Indexing to Table?
sql weird
NOLOCK
NEED HELP ABOUT CURSOR
Retrieval of IDENTITY after "bulk" insert?