Prev: Get column value in delete trigger
Next: Importing nightly from another DB
From: pascal13131 on 16 Jun 2010 16:52
I get this error:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint
"CheckotherExists". The conflict occurred in database "AddressBook", table
"dbo.MemberAttendance".

when trying to create this check constraint:

alter table MemberAttendance add constraint CheckotherExists check
(dbo.CheckotherPartnerExists (MemberNum, otherPoints)>0)

Can anybody see why?

ALTER FUNCTION [dbo].[CheckSpousePartnerExists](@MemberNum int,
@SpousePoints int)
RETURNS bit -- 1 pass check, 0 fail
AS
BEGIN
declare @result bit
declare @SpousePartner varchar (32)
if @SpousePoints > 0
begin
select @SpousePartner = SpousePartner from Members m where m.MemberNum
= @MemberNum
if @SpousePartner is null or @SpousePartner = ''
set @result=0
else set @result=1
end
else set @result = 1 -- no need to check for spouse since no points are
being awarded.

return @result
END


CREATE TABLE [dbo].[MemberAttendance](
[MemberNum] [int] NOT NULL,
[EventNumber] [int] NOT NULL,
[PointsEarned] [int] NULL,
[otherAttend] [bit] NULL,
[otherPoints] [int] NULL,
CONSTRAINT [PK_MemberAttendance] PRIMARY KEY CLUSTERED
(
[MemberNum] ASC,
[EventNumber] 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

ALTER TABLE [dbo].[MemberAttendance] WITH CHECK ADD CONSTRAINT
[FK_MemberAttendance_Members] FOREIGN KEY([MemberNum])
REFERENCES [dbo].[Members] ([MemberNum])
GO

ALTER TABLE [dbo].[MemberAttendance] CHECK CONSTRAINT
[FK_MemberAttendance_Members]
GO

ALTER TABLE [dbo].[MemberAttendance] WITH CHECK ADD CONSTRAINT
[FK_MemberAttendance_PaidEvents] FOREIGN KEY([EventNumber])
REFERENCES [dbo].[PaidEvents] ([EventNumber])
GO

ALTER TABLE [dbo].[MemberAttendance] CHECK CONSTRAINT
[FK_MemberAttendance_PaidEvents]
GO

CREATE TABLE [dbo].[Members](
[MemberNum] [int] NOT NULL,
[FirstName] [varchar](32) NULL,
[MiddleName] [varchar](32) NULL,
[SpousePartner] [varchar](32) NULL,
[LastName] [varchar](32) NOT NULL,
[Address1] [varchar](64) NULL,
[Address2] [varchar](64) NULL,
[City] [varchar](32) NULL,
[State] [varchar](32) NULL,
[Zip] [varchar](32) NULL,
[PhoneNumber] [varchar](16) NULL,
[CellNumber] [varchar](16) NULL,
[CallSign] [varchar](16) NULL,
[StatusName] [varchar](16) NULL,
[Joined] [smalldatetime] NOT NULL,
[Expire] [smalldatetime] NOT NULL,
[MethodName] [varchar](16) NULL,
[MiscCodeName] [varchar](16) NULL,
[PictureIDCheck] [bit] NULL,
[PictureIDCheckDate] [smalldatetime] NOT NULL,
[EmailAddress] [varchar](64) NULL,
[KeyCode] [varchar](12) NULL,
CONSTRAINT [PK_Members] PRIMARY KEY CLUSTERED
(
[MemberNum] 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
From: Erland Sommarskog on 16 Jun 2010 17:53
pascal13131 (pascal13131(a)discussions.microsoft.com) writes:
> Msg 547, Level 16, State 0, Line 1
> The ALTER TABLE statement conflicted with the CHECK constraint
> "CheckotherExists". The conflict occurred in database "AddressBook", table
> "dbo.MemberAttendance".
>
> when trying to create this check constraint:
>
> alter table MemberAttendance add constraint CheckotherExists check
> (dbo.CheckotherPartnerExists (MemberNum, otherPoints)>0)
>
> Can anybody see why?

I guess there is data that violates the constraint. But that's an
academic question.

The correct answer is: don't do this! Calling a scalar UDF in a
CHECK constraint can cause severe performance issues. You should
implement this check in a trigger instead, where you can make a
set-based check. When you use a scalar-UDF, you will convert the
INSERT statement to a cursor behind the scenes.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: --CELKO-- on 17 Jun 2010 14:04
Thank you for the DDL.

It is completely wrong.

Too many NULLs, not enough constraints and assembly language BIT flags
in SQL?! Can you give me an example of a VARCHAR(32) ZIP code? Did you
know that all address lines in the US fit into CHAR(35) by USPS rules?
Can you give me a CHAR(32) first name, even in Greek, Polish or
Amerind? How can you allow spouse_partner_name to be NULL or empty
string and treat two TOTALLY different things as the same?

Could you be more vague than “misc_code_name”? Is it a code? Is it a
name? Why don't you know what it is? Is “status_name” really
“<something>_status” or a “<something>_name”? By ISO-11179 rules and
basic data modeling there is no such thing as a status_name in a
properly designed schema.

Why do you have these two columns?

picture_id_check BIT NOT NULL,
picture_id_check_date DATE NOT NULL,

The date of the picture id validation is more information than the
silly punch card bit flag.

Why are there no semi-colons in your code (they are now mandatory in
many places in SQL Server and have always been part of the language)?

You did not do any research or thought on this.

Please learn how to write declarative code, then UN-learn procedural
code. Use CASE and not IF-THEN-ELSE control flow. Oh, there is no
agreement on the use of {-1, 0, +1} for Booleans in procedural
programming languages. But let's be ignore that and use what you had.

CREATE FUNCTION Spouse_Exists (@member_nbr INTEGER)
RETURNS BIT -- 1 has spouse, 0 has no spouse
AS
RETURN
CASE WHEN 0 < (SELECT spouse_points
FROM Membership AS M
WHERE M.member_nbr = @member_nbr)
THEN CAST (0 AS BIT)
ELSE CAST (1 AS BIT)
END;

Lot cleaner, unh?

But what about the spouse_partner_name part of this? You do it with a
check constraint:

CHECK
(CASE WHEN spouse_points = 0
AND spouse_partner_name IS NULL
THEN 'T' ELSE 'F' END)

and

spouse_partner_name VARCHAR (20)
CHECK (TRIM (BOTH(spouse_partner_name) <> '')

Now use this pattern to begin to fix that horrible DDL you posted and
try to make it into usable SQL. Your mindset is still in punch cards
and assembly language coding. Get a copy of THINKING IN SETS; it
might help.
From: pascal13131 on 17 Jun 2010 16:24
I can think of better ways to promote your book then to berate people who
posts questions on this forum. The derogatory tone of your post is
unnecessary. Do you do the same thing to the attendees of your lectures
because they have less expertise than you?

We're all learning here and we learn from people like you who have devoted a
tremendous amount of time to researching these specialties.

I on the other had am just a working stiff trying to develop a system for a
club in my spare time. I do the best I can with what I have. I don't need to
be denigrated in the process simply because I lack your years of experience.
And I'm perfectly willing to learn from the experts in the field and so I
will look up your book, “Thinking in Sets” because I do have the desire to
write the best code possible.

Thanks for the tip.


"--CELKO--" wrote:

> Thank you for the DDL.
>
> It is completely wrong.
>
> Too many NULLs, not enough constraints and assembly language BIT flags
> in SQL?! Can you give me an example of a VARCHAR(32) ZIP code? Did you
> know that all address lines in the US fit into CHAR(35) by USPS rules?
> Can you give me a CHAR(32) first name, even in Greek, Polish or
> Amerind? How can you allow spouse_partner_name to be NULL or empty
> string and treat two TOTALLY different things as the same?
>
> Could you be more vague than “misc_code_name”? Is it a code? Is it a
> name? Why don't you know what it is? Is “status_name” really
> “<something>_status” or a “<something>_name”? By ISO-11179 rules and
> basic data modeling there is no such thing as a status_name in a
> properly designed schema.
>
> Why do you have these two columns?
>
> picture_id_check BIT NOT NULL,
> picture_id_check_date DATE NOT NULL,
>
> The date of the picture id validation is more information than the
> silly punch card bit flag.
>
> Why are there no semi-colons in your code (they are now mandatory in
> many places in SQL Server and have always been part of the language)?
>
> You did not do any research or thought on this.
>
> Please learn how to write declarative code, then UN-learn procedural
> code. Use CASE and not IF-THEN-ELSE control flow. Oh, there is no
> agreement on the use of {-1, 0, +1} for Booleans in procedural
> programming languages. But let's be ignore that and use what you had.
>
> CREATE FUNCTION Spouse_Exists (@member_nbr INTEGER)
> RETURNS BIT -- 1 has spouse, 0 has no spouse
> AS
> RETURN
> CASE WHEN 0 < (SELECT spouse_points
> FROM Membership AS M
> WHERE M.member_nbr = @member_nbr)
> THEN CAST (0 AS BIT)
> ELSE CAST (1 AS BIT)
> END;
>
> Lot cleaner, unh?
>
> But what about the spouse_partner_name part of this? You do it with a
> check constraint:
>
> CHECK
> (CASE WHEN spouse_points = 0
> AND spouse_partner_name IS NULL
> THEN 'T' ELSE 'F' END)
>
> and
>
> spouse_partner_name VARCHAR (20)
> CHECK (TRIM (BOTH(spouse_partner_name) <> '')
>
> Now use this pattern to begin to fix that horrible DDL you posted and
> try to make it into usable SQL. Your mindset is still in punch cards
> and assembly language coding. Get a copy of THINKING IN SETS; it
> might help.
> .
>
From: J.B. Moreno on 18 Jun 2010 03:11
--CELKO-- <jcelko212(a)earthlink.net> wrote:

> Can you give me a CHAR(32) first name, even in Greek, Polish or
> Amerind?

Could be a hoax, but if you google for the longest first name, it's
quite a bit more than 32 characters.

(Although CHAR anything other than 1 or 2 is questionable, make it
varchar and be generous).

--
J.B. Moreno
 |  Next  |  Last
Pages: 1 2 3 4
Prev: Get column value in delete trigger
Next: Importing nightly from another DB