top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

How to catch check constraint violation error in Database.

+3 votes
557 views

How to catch check constraint violation error in Database.

posted Apr 21, 2014 by Merry

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote
 
Best answer

Smart try-catch mechanism of C# is enough to handle the situation.

Now I will create one table with one check constraint like,

create table TestTable(
   id int not null primary key identity(1,1),
   sal int not null CHECK(sal >0)
)

Here you can see my sal column will not take any negative value as I have set one check constraint in this column.

When I will try to fire insert into TestTable(sal) values(-100) query to this table, It will throw error.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;

namespace myProject
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=test;Integrated Security=True";
            con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "insert into TestTable(sal) values(-100)";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;

            try
            {
                int Value = (Int32)cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.Write("Error Occur in Database" +"\n" +"Error Code"+  ex.Number +"\n" + "Error Message" + ex.Message);
            }
            Console.ReadLine();
        }
    }
}
answer Apr 22, 2014 by Meenal Mishra
Similar Questions
+1 vote

I have kept all the create table SQL querys in a text file. Using readLines i am trying to execute the sql commands as per the code mentioned.

file=open("TABLES.txt","r")
for sql in file.readlines():
self.cursor.execute(sql)

But I am getting Error 1065 ' Query was empty'. More Importantly the tables are being created in the database. The text file is like this.

CREATE TABLE TUserDetails (FirstName VarChar(50) NOT NULL, LastName VarChar(50) NOT NULL, Email_Id VarChar(50) NOT NULL,Type VarChar(50) NOT NULL,Department VarChar(50) NOT NULL,NoOfIncorrectAttempt Integer NOT NULL,Deleted Bit NOT NULL,UserID VarChar(50) NOT NULL,CONSTRAINT TUserDetails_PK PRIMARY KEY CLUSTERED ( UserID ))

CREATE TABLE TRequests(RequestID VarChar(50) NOT NULL,UserID VarChar(50) NOT NULL,Status SmallInt NOT NULL,TimeOfRequest Timestamp NOT NULL,Deleted Bit NOT NULL,Priority Integer NOT NULL,CONSTRAINT TRequests_PK PRIMARY KEY CLUSTERED ( RequestID ))

CREATE TABLE TUserDetailUSERs(UserID VarChar(50) NOT NULL, Type VarChar(50) NOT NULL,Deleted Bit NOT NULL,TimeOfCreation Timestamp NOT NULL,TimeLastUpdated Timestamp NOT NULL,Active Bit NOT NULL,PWDID VarChar(50) NOT NULL,RoleID VarChar(50) NOT NULL,OrganisationID Integer NOT NULL,CONSTRAINT TUserDetailUSERs_PK PRIMARY KEY CLUSTERED ( UserID ))

CREATE TABLE TOrganisations(OrganisationID Integer NOT NULL,OrganisationName VarChar(50) NOT NULL,TimeOfCreation Timestamp NOT NULL,TimeLastUpdated Timestamp NOT NULL, Deleted Bit NOT NULL, CONSTRAINT TOrganisations_PK PRIMARY KEY CLUSTERED ( OrganisationID ))

CREATE TABLE TPWDs(PWDID VarChar(50) NOT NULL,Code VarChar(50) NOT NULL,DateOfCreation Timestamp NOT NULL,DateLastUpdated Timestamp NOT NULL,TimeOfDeletion Timestamp NOT NULL, CONSTRAINT TPWDs_PK PRIMARY KEY CLUSTERED ( PWDID ))

CREATE TABLE TRoles(RoleID VarChar(50) NOT NULL,RoleName VarChar(50) NOT NULL,Description VarChar(50) NOT NULL,TimeOfCreation Timestamp NOT NULL, RoleLastUpdated VarChar(50) NOT NULL, Deleted Bit NOT NULL, CONSTRAINT TRoles_PK PRIMARY KEY CLUSTERED ( RoleID ))

I checked running each sql query individually, and it is qorking file. Now although the tables are being created in the database but i am getting error 1065 as mentioned above

...