SQL SERVER – Create Check Constraint on Column

I found one of the Jr. Developer writing trigger for the requirement where he wanted to make sure invalidate data does not enter in table column. I suggested him to write Check Constraint. Check Constraints are very handy to make sure all the data in the table is validated before it enters in the database.

Let us check constraint on over one of the table on postalcode table in database AdventureWorks database. Constraint will suggest that value which is larger than 11 character can not be inserted into the column. Once constraint is created, it can be tested by tring to insert value which is larger than 11 character. It will throw an error saying it can not be done as it violates constraint.
USE AdventureWorks
GO
ALTER TABLE Person.Address
ADD CONSTRAINT CK_Address_PostalCode
CHECK (LEN(PostalCode) < 11)
GO
UPDATE Person.Address
SET PostalCode = '12345-12345-123'
WHERE AddressID = 1
GO

Result Set:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint “CK_Address_PostalCode”.
The conflict occurred in database “AdventureWorks”, table “Person.Address”,
column ‘PostalCode’.
The statement has been terminated.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

12 thoughts on “SQL SERVER – Create Check Constraint on Column

  1. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 4 Journey to SQL Authority with Pinal Dave

  2. i have one column on table1,
    how can i add constraint to that column,
    depending on a column on another table

    i have ‘salary’ for a worker on workers table,
    and i want it to be more than ‘salarybasis’ on jobs table

    thanks

    Like

  3. @Nir

    If there is a FK to the jobs TABLE, add the salary basis column to the FK, then you have it locally and a normal CHECK CONSTRAINT could be used.

    Another options would be instead of salary to have salarybasis_modifier, which means that the salary is always at least the salarybasis but it can also be added to in this COLUMN.

    Like

  4. What will happen If add a check constraint on a prefilled table i.e table whihc already has data.
    Will it allow me to create the check constraint.

    Like

    • You can check this yourself:

      — Delete any old test table
      IF NOT (SELECT OBJECT_ID(‘dbo.MyTestTable’)) IS NULL DROP TABLE MyTestTable
      GO
      — Create test table
      CREATE TABLE dbo.MyTestTable(i INT)
      GO
      — Insert row
      INSERT INTO dbo.MyTestTable SELECT 1
      GO
      — Following fails because MyTestTable already has a row
      — which doesn’t satisfy the check
      ALTER TABLE dbo.MyTestTable ADD CONSTRAINT MyTestTable_chk CHECK (i > 1)
      GO
      — Insert row
      — This succeeds because the check above wasn’t
      — actually commited because of the error
      INSERT INTO dbo.MyTestTable SELECT 1
      GO
      — Following works because we are saying that
      — “don’t check old values’
      ALTER TABLE dbo.MyTestTable WITH NOCHECK ADD CONSTRAINT MyTestTable_chk CHECK (i > 1)
      GO
      — Try to insert row
      — This fails because of the check above
      INSERT INTO dbo.MyTestTable SELECT 1
      GO
      — Returns 2 rows
      SELECT * FROM dbo.MyTestTable
      GO
      — Drop test table
      DROP dbo.MyTestTable
      GO

      Like

  5. Is this possible to fix the start value of a column??

    For example there is a column called BookingId (varchar)in a table, and it should start with the value HB100.

    Please let me know if it can be done using any check constraint on the column..

    Thanks

    Like

  6. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 9 of 31 Journey to SQLAuthority

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s