CODEWITHSUNDEEP
sqlsql-serverdatabase
Tathagat Verma
Tathagat Verma

Reputation: 548

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:

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

Upvotes: 1

Views: 7623

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

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]')
)

Upvotes: 2

Phil
Phil

Reputation: 164766

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

Upvotes: 3

Related Questions