473,573 Members | 2,816 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Which CHECK constraint causes the exception?

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.ExecuteNonQ uery();
}
catch(SqlExcept ion 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

Apr 7 '06 #1
3 6309
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******** *************@v 46g2000cwv.goog legroups.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.ExecuteNonQ uery();
}
catch(SqlExcept ion 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

Apr 7 '06 #2
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.

Apr 7 '06 #3
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,

[theConstraintCo l] [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 (([theConstraintCo l]<=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_subs cribed
is_merge_publis hed
is_dts_replicat ed
is_xml_document
xml_collection_ id
default_object_ id
rule_object_id

277576027
theConstraintCo l
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_const raints

--where name like 'the%'

order by name



name
object_id
principal_id
schema_id
parent_object_i d
type
type_desc
create_date
modify_date
is_ms_shipped
is_published
is_schema_publi shed
is_disabled
is_not_for_repl ication
is_not_trusted
parent_column_i d
definition
uses_database_c ollation
is_system_named

CK_Table_1
309576141
NULL
1
277576027
C
CHECK_CONSTRAIN T
2006-05-21 13:56:55.373
2006-05-21 13:56:55.373
0
0
0
0
0
0
0
([theConstraintCo l]<=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_i d
type
type_desc
create_date
modify_date
is_ms_shipped
is_published
is_schema_publi shed

CK_Table_1
309576141
NULL
1
277576027
C
CHECK_CONSTRAIN T
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_Extensi ons
245575913
NULL
1
229575856
PK
PRIMARY_KEY_CON STRAINT
2006-05-06 00:04:08.153
2006-05-06 00:04:08.153
0
0
0

PK_Data_SystemF eatures
213575799
NULL
1
197575742
PK
PRIMARY_KEY_CON STRAINT
2006-05-05 23:57:20.340
2006-05-05 23:57:20.340
0
0
0

PK_FeatureAttri butes
133575514
NULL
1
117575457
PK
PRIMARY_KEY_CON STRAINT
2006-05-05 23:42:12.687
2006-05-05 23:42:12.687
0
0
0

PK_MetaData_Fea tures
2137058649
NULL
1
2121058592
PK
PRIMARY_KEY_CON STRAINT
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_CON STRAINT
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

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

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

ServiceBrokerQu eue
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_SystemFeat ures
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_Featur eAttributes
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_Featur es
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_Extensi ons
261575970
NULL
1
229575856
UQ
UNIQUE_CONSTRAI NT
2006-05-06 00:04:08.153
2006-05-06 00:04:08.153
0
0
0

IX_FeatureAttri butes_Attribute Name
149575571
NULL
1
117575457
UQ
UNIQUE_CONSTRAI NT
2006-05-05 23:42:12.903
2006-05-05 23:42:12.903
0
0
0

IX_MetaData_Fea tures_FeatureNa me
5575058
NULL
1
2121058592
UQ
UNIQUE_CONSTRAI NT
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_const raints CC

INNER JOIN sys.objects O ON CC.Parent_Objec t_ID = O.Object_ID

LEFT OUTER JOIN sys.columns COL ON CC.Parent_Colum n_ID = COL.Column_ID





Table Name
Column Name
Constraint

Table_1
[Table Constraint]
([theConstraintCo l]<=len([theRefCol]))



(1 row(s) affected)



-- Some SQL that violates the constraint.

INSERT Table_1 (theconstraintc ol, 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 "EXPERIMENT AL", 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)

May 21 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2813
by: Doug Baroter | last post by:
Hi, DDL: -- create table #task (taskID int identity(1,1) primary key, taskName varchar(25) unique, taskCompleteDate dateTime, taskComplete bit default(0)); /* Business Rules: a) if taskCompleteDate is NULL (like default) then, taskComplete may not be set to 1 (attempt to update it to 1 would fail);
3
9052
by: RAD | last post by:
I am working with an evaluation copy of SQL Server 2000 for the first time; my DB experience lies with MS Access. I have a simple table in SQL Server (tblCompany) that has a field called "Ticker." When new company stock tickers (i.e., MSFT for Microsoft) are entered into the field, I'd like them in all caps--whether the user types msft,...
0
1827
by: Fabre Lambeau | last post by:
I've got a problem when adding a CONSTRAINT CHECK on a table by calling a function. It just seems not to work... Here is the table (simplified to only the relevant fields for this case): CREATE TABLE public.tb_contacts ( contact_id serial NOT NULL, actor_id varchar(50) NOT NULL, contacttype_id varchar(6) NOT NULL,
1
2847
by: Dmitry Martynov | last post by:
Hi I have a question whether XmlValidatingReader doesn't check keyref constrain (the same with key constraint) or I do smth wrong. I have the following schema <?xml version="1.0" encoding="utf-8"?> <xs:schema id="module" targetNamespace="http://tempuri.org/module.xsd"
5
16699
by: aj | last post by:
DB2 WSE 8.1 FP5 Red Hat AS 2.1 What is the difference between adding a unique constraint like: ALTER TABLE <SCHEMA>.<TABLE> ADD CONSTRAINT CC1131378283225 UNIQUE ( <COL1>) ; and adding a unique index like:
7
6653
by: Brian Keating | last post by:
Hi there, Is it possible to add a unique constraint on two columns in a table, so that the constraint is a composite of the two? i.e. these two columns together should be unique...? i.e. column1 column2 1 1 1 2
9
3409
by: Edmund Dengler | last post by:
Greetings! Just trying some tests out, and wanted to know about some optimizations. If I do a CHECK constraint on a table, is this used to optimize a SELECT or does Postgresql rely mostly on normal index search? For example, I want to create some tables to manage different data in a kind of <object, relationship, object2> manner, but...
4
1392
by: QC | last post by:
Hi Friends, I have one Application running on Client PC, i coded to store all Debug, Info, and Trace related information in Log file. This log file helps me to analyze the exception if any generated in my Application. Related to exception, i only stores information passed Exception (or related exception type class) class (ToString(), Message...
2
14645
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how can I pass null value as parameter to the database stored procedure programattically using C#? Although I can check for empty column (the...
0
8037
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8213
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7799
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8080
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5296
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3742
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2226
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1320
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1048
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.