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 (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – White Paper: Security Overview for Database Administrators
Next Post
SQLAuthority News – Release Notes for SQL Server 2008 Release Candidate 0

Related Posts

12 Comments. Leave new

  • thanx man.

    Reply
  • how to add more than one value as a check constraint for column?

    thanks in advance

    Reply
  • ponnusamy, you can add multiple constraints on the column. Add a constraint for each value.

    Reply
  • 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

    Reply
    • u can take column by using =alter table table_name add(column_name constraint constraint_name references table_name(column_name))

      Reply
  • i forgot something
    it’s important to do that without using triggers
    with triggers it’s not hard

    Reply
  • @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.

    Reply
  • 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.

    Reply
    • 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

      Reply
  • Adarsh Mishra
    March 27, 2010 9:08 pm

    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

    Reply

Leave a Reply

Menu