How can I put validations on a SQL table column?

Problem Assumption:

I have a table in SQL Server, with the structure as follows;

Column 1: Id | INT | NOT NULL | Auto-Identity
Column 2: Name | VARCHAR(20) | NOT NULL
Column 3: Number | SMALLINT | NOT NULL

Solution Scenario:

What I want is that whenever some value is entered in a column, then it should be verified or validated against certain checks, at database level itself?

Example:

The column 3, Number" should only allow:

• numeric 开发者_C百科values
• with length 10
• and value should start with the digit 4

Could anyone please suggest the best way to implement this at database level?

Use CHECK constraints - http://msdn.microsoft.com/en-us/library/ms188258.aspx

ALTER TABLE table
ADD CONSTRAINT tenDigitsStartsWithFour CHECK
(Col3 BETWEEN 4000000000 AND 4999999999) -- Col3 must be a BIGINT, thanks Mikael

I think your number column should be fixed-width text e.g.

CREATE TABLE MyTable
(
 Id INTEGER NOT NULL IDENTITY, 
 Name VARCHAR(20) NOT NULL UNIQUE, -- presumably a candidate key
 Number CHAR(10) NOT NULL
    CHECK (Number LIKE '4[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)