Check constraint is used to limit the value that you can put into one column or multiple columns. This will specify a logical expression which will be evaluated every time a row is inserted or corresponding column is modified.
check constraints help us to enforce data integrity and, in some cases, lead to better execution plans. It is a good idea to use them as long as you can live with the overhead that they introduce during data modification.
You can get information about check constraints from the sys.check_constraints catalog view .
Example:
It is always best practice to use the constraint name during the creation.
create table dbo.Accounts
(
AccountId
int not null identity(1,1),
AccountType
varchar(32) not null,
Age
int,
constraint CHK_Accounts_AccountType
check
(AccountType in ('Checking','Saving','Credit Card')),
constraint CHK_Accounts_Age
check
(Age> 18)
)
insert into Accounts values
('Saving',20)
Drop the Check Constraint
To drop a check constraint use the following SQL script.
Alter table Accounts
drop constraint CHK_Accounts_Age;
Add the Check Constraint
ALTER TABLE Accounts
ADD CONSTRAINT CHK_PersonAge CHECK (Age>18)
Different types of SQL Server Constraints:
Well Described article.
ReplyDelete