locked
How to test CHECK constraint

    Question

  • Good day,

    I've tried to search this forum, google, but i could not find any solution.

     

    I have a table (the simplified structure):

     

    CREATE TABLE [dbo].[Pacient](
     [PacientID] [int] IDENTITY(1,1) NOT NULL,
     [Name] [nvarchar](20) NOT NULL,
     CONSTRAINT [PK_Pacient] PRIMARY KEY CLUSTERED

    And a constraint was set on this table (which checks if there are white spaces in Name column):
    ALTER TABLE [dbo].[Pacient]  WITH CHECK ADD  CONSTRAINT [CK_Pacient_Name_NoSpaces] CHECK  ((charindex(' ',[Name])=(0)))

    After that, i've created a test with sql code:
    --I'am trying to insert a name with a white space in it
    INSERT INTO [sqlSyntaxTest].[dbo].[Pacientai]
               ([Name])          
         VALUES ('DA HLIA')
           
    --Then i select to see if that line was inserted:
    SELECT * FROM [dbo].[Pacient]
     WHERE
      [Name] = 'DA HLIA'

     

    Test passes if it returns empty Result Set (the line was not inserted)

     

    After running a test it fails with error:

    ...ColumnTesting.TestCheckConstrints.CK_Pacientai_Vardai_NoSpaces threw exception:  System.Data.SqlClient.SqlException: The INSERT statement conflicted with the CHECK constraint "CK_Pacientai_Vardas_NoSpaces"...

     

    I understand that in this situation it is better to use insert trigger, but it is an example situation and i would like to know how to test the CHECK constraints.

     

    Thank you for your answers/suggestions.

    Monday, April 02, 2007 7:41 PM

Answers

  • You can get this test to work by diving a bit into the C# code behind your test method.

     

    If you go to your test method, then select 'View Code' in the Solution Explorer, you'll be able to find the C# code for your unit test.  Once you do, add something like the following attribute to the test method:

     

    [ExpectedException(typeof(System.Data.SqlClient.SqlException), "Received expected exception")]

     

    The test will now pass.  Of course, this isn't a panacea since this sample will mean that any SqlException will not cause the test to fail; however, for the test you have in mind this should do the trick.

     

    There are a couple of other approaches you could take:

    1. If you are using SQL Server 2005 you could add a try-catch to your TSQL for the test, catch the SQL Exception, and check the error code of the exception to determine pass/fail.  This would be the most SQL-centric way of going about the test, but you'd need 2005
    2. You could use a try-catch in the C# (or VB) code of the test method, then use the Team Test Assert class to check the SqlException code.  The Team Test docs have HowTos on using test assertions.  This would allow you to check for the specific SqlException, which is more specific than using ExpectedException (though it requires a bit more coding)
    Monday, April 02, 2007 10:04 PM
    Moderator
  • Thank you Jeff for the answer.
    I've added INSERT Code in "BEGIN TRY - CATCH" block and it worked fine for me. It's always a good feeling when problems get solved Smile
    Tuesday, April 03, 2007 7:17 AM

All replies

  • You can get this test to work by diving a bit into the C# code behind your test method.

     

    If you go to your test method, then select 'View Code' in the Solution Explorer, you'll be able to find the C# code for your unit test.  Once you do, add something like the following attribute to the test method:

     

    [ExpectedException(typeof(System.Data.SqlClient.SqlException), "Received expected exception")]

     

    The test will now pass.  Of course, this isn't a panacea since this sample will mean that any SqlException will not cause the test to fail; however, for the test you have in mind this should do the trick.

     

    There are a couple of other approaches you could take:

    1. If you are using SQL Server 2005 you could add a try-catch to your TSQL for the test, catch the SQL Exception, and check the error code of the exception to determine pass/fail.  This would be the most SQL-centric way of going about the test, but you'd need 2005
    2. You could use a try-catch in the C# (or VB) code of the test method, then use the Team Test Assert class to check the SqlException code.  The Team Test docs have HowTos on using test assertions.  This would allow you to check for the specific SqlException, which is more specific than using ExpectedException (though it requires a bit more coding)
    Monday, April 02, 2007 10:04 PM
    Moderator
  • Thank you Jeff for the answer.
    I've added INSERT Code in "BEGIN TRY - CATCH" block and it worked fine for me. It's always a good feeling when problems get solved Smile
    Tuesday, April 03, 2007 7:17 AM