Which CHECK constraint causes the exception?

[ferg]

I have a Customer table. The table has two different CHECK constraints.
Then there is the Customer details dialog, which provides the user with
an UI for changing users. I have some UPDATE sql, which is called once
the user clicks the OK button on this dialog.
[C#]
try
{
int rows = cmd.ExecuteNonQuery();
}
catch(SqlException se)
{
if(se.Number == 547) // CHECK CONSTRAINT violation
{
// Warn user
}
}
[/C#]

The question is. If a SqlException is thrown _and_ found to be a CHECK
CONSTRAINT violation, what is the recomended way to find out which
CHECK constraint caused the exception?
I can do a substring match on the Message property, but this seems
badly wrong.

Any ideas?

Fergus

[Miha Markic [MVP C#]]

Hi ,

Did you try inspecting se.Errors?

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

"ferg" <fe******@gmail.com> wrote in message
news:11*********************@v46g2000cwv.googlegro ups.com...

I have a Customer table. The table has two different CHECK constraints.
Then there is the Customer details dialog, which provides the user with
an UI for changing users. I have some UPDATE sql, which is called once
the user clicks the OK button on this dialog.
[C#]
try
{
int rows = cmd.ExecuteNonQuery();
}
catch(SqlException se)
{
if(se.Number == 547) // CHECK CONSTRAINT violation
{
// Warn user
}
}
[/C#]

The question is. If a SqlException is thrown _and_ found to be a CHECK
CONSTRAINT violation, what is the recomended way to find out which
CHECK constraint caused the exception?
I can do a substring match on the Message property, but this seems
badly wrong.

Any ideas?

Fergus

[ferg]

Sure, but the only field that's different seems to be the Message
field, which is a string field, which I don't want to extract type
information from.
My solution is to do two UPDATEs - UPDATE 1 can only violate CHECK
constraint 1, UPDATE 2 can only violate CHECK constraint 2 - so I can
then provide the appropriate feedback to the user.

You're in a world of hurt, my friend :( ...

I did a small test - and (using SQLExpress 2005) the error message text is quite explicit about the constraint that was hit, and the table and database it related to - so you can, if you want, get the infoirmation you need from it. I understand, however, your reluctance to parse an error message.

The alternative to parsing the error message for the name of the check constraint is to query, up front, the check constraints (and foreign key and not-null constraints) so that your app can enforce the constraints (likely in UI input validation) before you update the database.

1. Query the database for the constraints applicable to your update/insert and use the result to have your UI enforce the constraint. Then you'll not encounter the database giving the error and you can give feedback to the user at the earliest opportunity (i.e. before waiting n seconds for the insert/update to run and fail).

2. Parse the error message. You can get the basic text of the error message from sys.messages (I'm assuming you're using MS SQLServer) so can reliably find where the variable parts of the error message are inserted.

Below are the table definition, the test queries I ran, and the results I got - from which you can work out whether or how to apply either of these two approaches...

HTH.
Chris T-Ross

Create statement for the test table
Note - this DDL was generated by SQL Server Management Studio Express. The important pieces are the table name, the two column names that participate in the constraint, and the (table level) constraint. Using SSMESE I couldn't find out how to add a column-level constraint - but the information in the system tables, etc. is relevant regardless of the nature of the constraint.

USE [EXPERIMENTAL]

GO

CREATE TABLE [dbo].[Table_1](

[theKeyCol] [int] IDENTITY(1,1) NOT NULL,

[theConstraintCol] [int] NULL,

[theRefCol] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [theKeyCol] ASC ) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Table_1] WITH CHECK ADD CONSTRAINT [CK_Table_1] CHECK (([theConstraintCol]<=len([theRefCol])))

GO

ALTER TABLE [dbo].[Table_1] CHECK CONSTRAINT [CK_Table_1]

GO



The SQL I used to do the research



-- make sure I'm playing in the right namespace

use experimental



-- query system information for the columns in my table.

select * from sys.columns

where name like 'the%'

order by name

object_id
name
column_id
system_type_id
user_type_id
max_length
precision
scale
collation_name
is_nullable
is_ansi_padded
is_rowguidcol
is_identity
is_computed
is_filestream
is_replicated
is_non_sql_subscribed
is_merge_published
is_dts_replicated
is_xml_document
xml_collection_id
default_object_id
rule_object_id

277576027
theConstraintCol
2
56
56
4
10
0
NULL
1
0
0
0
0
0
0
0
0
0
0
0
0
0

277576027
theKeyCol
1
56
56
4
10
0
NULL
0
0
0
1
0
0
0
0
0
0
0
0
0
0

277576027
theRefCol
3
231
231
100
0
0
NULL
0
1
0
0
0
0
0
0
0
0
0
0
0
0



(3 row(s) affected)



-- query system information for information about the check constraint.

-- This reveals that the definition of the constraint can be found. This

-- query, however, is not really sufficient to determine the UI constraints

-- for the constraints on any given table/column pair. (see below for a solution)

select * from sys.check_constraints

--where name like 'the%'

order by name



name
object_id
principal_id
schema_id
parent_object_id
type
type_desc
create_date
modify_date
is_ms_shipped
is_published
is_schema_published
is_disabled
is_not_for_replication
is_not_trusted
parent_column_id
definition
uses_database_collation
is_system_named

CK_Table_1
309576141
NULL
1
277576027
C
CHECK_CONSTRAINT
2006-05-21 13:56:55.373
2006-05-21 13:56:55.373
0
0
0
0
0
0
0
([theConstraintCol]<=len([theRefCol]))
1
0



(1 row(s) affected)





-- Just look in system information for how the table is defined

select * from sys.objects

order by type, name



Name
object_id
principal_id
schema_id
parent_object_id
type
type_desc
create_date
modify_date
is_ms_shipped
is_published
is_schema_published

CK_Table_1
309576141
NULL
1
277576027
C
CHECK_CONSTRAINT
2006-05-21 13:56:55.373
2006-05-21 13:56:55.373
0
0
0

queue_messages_1977058079
1993058136
NULL
4
1977058079
IT
INTERNAL_TABLE
2005-10-14 01:36:25.360
2005-10-14 01:36:25.380
1
0
0

queue_messages_2009058193
2025058250
NULL
4
2009058193
IT
INTERNAL_TABLE
2005-10-14 01:36:25.377
2005-10-14 01:36:25.383
1
0
0

queue_messages_2041058307
2057058364
NULL
4
2041058307
IT
INTERNAL_TABLE
2005-10-14 01:36:25.377
2005-10-14 01:36:25.383
1
0
0

PK_Data_Extensions
245575913
NULL
1
229575856
PK
PRIMARY_KEY_CONSTRAINT
2006-05-06 00:04:08.153
2006-05-06 00:04:08.153
0
0
0

PK_Data_SystemFeatures
213575799
NULL
1
197575742
PK
PRIMARY_KEY_CONSTRAINT
2006-05-05 23:57:20.340
2006-05-05 23:57:20.340
0
0
0

PK_FeatureAttributes
133575514
NULL
1
117575457
PK
PRIMARY_KEY_CONSTRAINT
2006-05-05 23:42:12.687
2006-05-05 23:42:12.687
0
0
0

PK_MetaData_Features
2137058649
NULL
1
2121058592
PK
PRIMARY_KEY_CONSTRAINT
2006-05-05 19:27:29.437
2006-05-05 19:27:29.437
0
0
0

PK_Table_1
293576084
NULL
1
277576027
PK
PRIMARY_KEY_CONSTRAINT
2006-05-21 13:46:09.030
2006-05-21 13:46:09.030
0
0
0

Sysallocunits
7
NULL
4
0
S
SYSTEM_TABLE
2005-10-14 01:36:15.910
2005-10-14 01:36:15.910
1
0
0

... a bunch of other tables were cut from here to save space.

sysxprops
49
NULL
4
0
S
SYSTEM_TABLE
2005-10-14 01:36:18.063
2005-10-14 01:36:18.063
1
0
0

EventNotificationErrorsQueue
2009058193
NULL
1
0
SQ
SERVICE_QUEUE
2005-10-14 01:36:25.377
2005-10-14 01:36:25.377
1
0
0

QueryNotificationErrorsQueue
1977058079
NULL
1
0
SQ
SERVICE_QUEUE
2005-10-14 01:36:25.360
2005-10-14 01:36:25.360
1
0
0

ServiceBrokerQueue
2041058307
NULL
1
0
SQ
SERVICE_QUEUE
2005-10-14 01:36:25.377
2005-10-14 01:36:25.377
1
0
0

Data_Extensions
229575856
NULL
1
0
U
USER_TABLE
2006-05-06 00:04:08.153
2006-05-06 00:04:08.177
0
0
0

Data_SystemFeatures
197575742
NULL
1
0
U
USER_TABLE
2006-05-05 23:57:20.340
2006-05-05 23:57:20.350
0
0
0

MetaData_FeatureAttributes
117575457
NULL
1
0
U
USER_TABLE
2006-05-05 23:42:12.687
2006-05-06 22:33:46.420
0
0
0

MetaData_Features
2121058592
NULL
1
0
U
USER_TABLE
2006-05-05 19:27:29.437
2006-05-06 22:29:14.700
0
0
0

Table_1
277576027
NULL
1
0
U
USER_TABLE
2006-05-21 13:46:08.793
2006-05-21 13:56:55.373
0
0
0

IX_Data_Extensions
261575970
NULL
1
229575856
UQ
UNIQUE_CONSTRAINT
2006-05-06 00:04:08.153
2006-05-06 00:04:08.153
0
0
0

IX_FeatureAttributes_AttributeName
149575571
NULL
1
117575457
UQ
UNIQUE_CONSTRAINT
2006-05-05 23:42:12.903
2006-05-05 23:42:12.903
0
0
0

IX_MetaData_Features_FeatureName
5575058
NULL
1
2121058592
UQ
UNIQUE_CONSTRAINT
2006-05-05 19:27:29.437
2006-05-05 19:27:29.437
0
0
0



(61 row(s) affected)



-- Get table & column-specific constraint definitions. If a constraint

-- is table specific, the column name is reported as [Table Constraint],

-- otherwise, the column name is reported. Using the constraint definition

-- text (it is an expression) to determine how to apply UI constraints will

-- not be fun, of course, ... but possibly safer than assuming that the

-- paramter tokens in an error message string won't change.

SELECT O.name as "Table Name",

ISNULL(COL.Name, '[Table Constraint]') as "Column Name",

CC.Definition AS "Constraint"

FROM sys.check_constraints CC

INNER JOIN sys.objects O ON CC.Parent_Object_ID = O.Object_ID

LEFT OUTER JOIN sys.columns COL ON CC.Parent_Column_ID = COL.Column_ID





Table Name
Column Name
Constraint

Table_1
[Table Constraint]
([theConstraintCol]<=len([theRefCol]))



(1 row(s) affected)



-- Some SQL that violates the constraint.

INSERT Table_1 (theconstraintcol, therefcol) values (40, 'toast')



Msg 547, Level 16, State 0, Line 21

The INSERT statement conflicted with the CHECK constraint "CK_Table_1". The conflict occurred in database "EXPERIMENTAL", table "dbo.Table_1".

The statement has been terminated.



-- Me - confirming that the error # I got was the same you reported for a check constraint violation

select @@error



(no column name)

547



(1 row(s) affected)



-- Get the message template for the 547 error message.

-- If you parse this, and compare it with the message you got, you can 'safely'

-- extract the constraint name (with which you can then get the constraint definition).

-- 'Safely', of course, assumes that Microsoft don't change how they codify

-- inserting variable text into error messages in the next version of SQL

-- Server - which would break your parse logic. You could, of course, work

-- around that, too ...

select * from sys.messages

where message_id = 547



message_id
language_id
severity
is_event_logged
text

547
1033
16
0
The %ls statement conflicted with the %ls constraint "%.*ls". The conflict occurred in database "%.*ls", table "%.*ls"%ls%.*ls%ls.



(1 row(s) affected)