How can I create a CHECK constraint on a VARCHAR column in SQL Server specifying a minimum data length?

sql sql-server tsql check-constraints
Jake Petroules picture Jake Petroules · Dec 13, 2010 · Viewed 27k times · Source

I have a VARCHAR(30) column in a Microsoft SQL Server database. I'd like to add a CHECK constraint that does not allow a value in the column to be less than 3 characters. What is the expression I must use?

Answer

OMG Ponies picture OMG Ponies · Dec 13, 2010

use:

ALTER TABLE [dbo].[YOUR_TABLE]
ADD CONSTRAINT [MinLengthConstraint] CHECK (DATALENGTH([your_column]) > 2)

Reference:

Related questions

WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT

I'm looking at the AdventureWorks sample database for SQL Server 2008, and I see in their creation scripts that they tend to use the following: ALTER TABLE [Production].[ProductCostHistory] WITH CHECK ADD CONSTRAINT [FK_ProductCostHistory_Product_ProductID] FOREIGN KEY([ProductID]) REFERENCES […

Read More
How do I have a check constraint that refers to another table?

I have the following tables in a SQL Server 2008 db: tblItem, which has an ItemID field; tblGoodItem, which also has an ItemID field, and has a foreign key pointing to tblItem; tblBadItem, which also has an ItemID field, and also …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More