CHECK Constraint

In this blog, we’ll discuss about CHECK constraint.

  1. A CHECK constraint applies a rule to a column or table.
  2. It always includes a conditional expression or function that returns a Boolean value.
  3. It can be applied on column to restrict the entry to specific type of value or in-between the specified boundaries that are accepted by one or more columns.
  4. It can be defined on table or temp table (both local & global) or table variable.
  5. It can be defined with Primary key,Unique key,Foreign key (though defining with Foreign key doesn’t make any difference as it’ll always check against the referencing Primary key)
  6. Single CHECK constraints can be applied to a single column.
  7. Multiple CHECK constraints can be applied to a single column.
  8. Single CHECK constraint can be applied to multiple columns.
  9. When applied with function, the function must return a single value.
  10. To modify the CHECK constraint, we first need to delete the existing CHECK constraint and create a new CHECK constraint with new value.
  11. Column with CHECK constraint cannot be deleted or modified. The CHECK constraint must be deleted first to make the changes in the column.

CHECK constraint can applied for 3 properties in a table

  1. Check for existing data when it is created on existing column in a table with data.So that all the existing rows of column must satisfy the CHECK constraint Boolean condition.If it not, then SQL throws an error and it cannot be applied.
  2. Check for Insert / Update. So all the new inserts & updates will follow the its rule.
  3. Check for replication. Replication is a process in SQL where a copy of the table / database is created for backup. So any update made by replication engine must also follow its rule.

Limitation of the CHECK constraint

  1. It does not check against NULL value.Boolean value is always produce 3 types of values True or False or Unknown ( when comparing NULL values). In case of Unknown value the Check constraint will be overridden and insert / update will be successfully done. 
  2. It can’t contain sub queries, sequence or ROWNUM.
  3. It never provide any support for Delete statement.
  4. It can’t be defined on View.

Examples

–Example1 : CHECK constraint Yes for existing data + Yes for Insert/Update + Yes for replication with table definition
create table test
(
Id int,
Age int check ([Age]>5 AND [Age]<=10)
)

— OR with constraint name CHECK constraint Yes for existing data + Yes for Insert/Update + Yes for replication
create table test
(
Id int,
Age int constraint CK_ID check ([Age]>5 AND [Age]<=10)
)

–Example2 :CHECK constraint outside table definition
create table test
(
Id int,
Age int,
Name varchar(50)
)

–Example3 :CHECK constraint Yes for existing data + Yes for Insert/Update + Yes for replication
ALTER TABLE [dbo].[CheckTbl] add constraint CK_ID check(([Age]>(5) AND [Age]<=(10)))

–OR

ALTER TABLE [dbo].[CheckTbl] WITH CHECK ADD CONSTRAINT [CK_CheckTbl] CHECK (([Age]>(5) AND [Age]<=(10)))
–Example4 : CHECK constraint No for existing data + No for Insert/Update + No for replication
ALTER TABLE [dbo].[CheckTbl] WITH NOCHECK ADD CONSTRAINT [CK_CheckTbl] CHECK NOT FOR REPLICATION (([Age]>(5) AND [Age]<=(10)))

–Example5 : CHECK constraint Yes for existing data + No for Insert/Update + No for replication
ALTER TABLE [dbo].[CheckTbl] WITH NOCHECK ADD CONSTRAINT [CK_CheckTbl] CHECK NOT FOR REPLICATION (([Age]>(5) AND [Age]<=(10)))

–Example6 : CHECK constraint No for existing data + Yes for Insert/Update + No for replication
ALTER TABLE [dbo].[CheckTbl] WITH NOCHECK ADD CONSTRAINT [CK_CheckTbl] CHECK NOT FOR REPLICATION (([Age]>(5) AND [Age]<=(10)))

–Example7 : CHECK constraint No for existing data + Yes for Insert/Update + Yes for replication
ALTER TABLE [dbo].[CheckTbl] WITH NOCHECK ADD CONSTRAINT [CK_CheckTbl] CHECK (([Age]>(5) AND [Age]<=(10)))

–Example8 : Single CHECK constraint on Multiple column
ALTER TABLE [dbo].[CheckTbl]
ADD CONSTRAINT CK_ID_Age
CHECK ([Id] IS NOT NULL OR [Age] IS NOT NULL )

–Example9 : Multiple CHECK constraint on Single column
ALTER TABLE [dbo].[CheckTbl1]
ADD CONSTRAINT CK_ID1
CHECK ([Id] IS NOT NULL )

GO
ALTER TABLE [dbo].[CheckTbl2]
ADD CONSTRAINT CK_ID2
CHECK ([Id]>5 AND [Id]<=10)

–Example10 : Name should be 5 chars long and must consists of and capital alphabets only
ALTER TABLE [dbo].[CheckTbl2]
ADD CONSTRAINT CK_Name
CHECK ([Name] like ‘[A-Z][A-Z][A-Z][A-Z][A-Z]’)

–Example11 : CHECK constraint with function
CREATE FUNCTION dbo.checkdata(@id int)
RETURNS bit
AS
BEGIN
IF EXISTS(SELECT * FROM tab1 WHERE id = @id)
RETURN 1

IF EXISTS(SELECT * FROM tab2 WHERE id = @id)
RETURN 1

RETURN 0
END

ALTER TABLE [dbo].[CheckTbl]
ADD CONSTRAINT CK_ID
CHECK (dbo.fn_check_p1p2(Id) = 1)

–Example12 : CHECK constraint with Primary key
create table t1
(
id int primary key check(id>5),
name varchar(50)
)

–Example13 : CHECK constraint with Unique key
create table t2
(
id int unique check(id>5),
name varchar(50)
)

–Example14 : CHECK constraint with Foreign key
create table t3
(
id int foreign key references t1(id) check (id>4),
name varchar(50)
)

Disable the existing CHECK constraint : After disable the check validation is removed from the column until it is enabled again.

ALTER TABLE [dbo].[CheckTbl] NOCHECK CONSTRAINT [CK_ID]

Enable the existing disabled CHECK constraint without validating the existing value in the column against the check validation.

ALTER TABLE [dbo].[CheckTbl] CHECK CONSTRAINT [CK_ID]

Enable the existing disabled CHECK constraint and also validate the existing value in the column against the check validation. If any rows fails to validate then SQL throws an error and the CHECK constraint will not be enabled.

ALTER TABLE [dbo].[CheckTbl] WITH CHECK CHECK CONSTRAINT [CK_ID]

Remove the CHECK constraint

ALTER TABLE [dbo].[CheckTbl] DROP CONSTRAINT [CK_ID]

To get all information about all the CHECK constraints defined in all the tables in the current database. (Note : CK= CHECK Constraint)

SELECT
@@Servername AS ServerName,
DB_NAME() AS DBName,
OBJECT_SCHEMA_NAME(ck.parent_object_id) AS SchemaName,
OBJECT_NAME(ck.parent_object_id) AS TableName,
c1.name as ColName,
t.name as ColDataType,
c1.max_length as ColMaxLength,
c1.precision as ColPrecision,
c1.scale as ColScale,
ck.Name AS CK_ConstraintName,
(CASE ck.is_disabled when 0 Then ‘YES’ Else ‘NO’ END) as CK_EnableStatus,
OBJECT_DEFINITION(ck.object_id) AS CK_Definition,
(Case c1.is_nullable When 0 Then ‘NO’ Else ‘Yes’ End) as CK_Col_IsNULL,
(CASE ck.is_not_for_replication when 0 Then ‘YES’ Else ‘NO’ END) as CK_EnforceForReplicattion,
(CASE ck.is_not_trusted when 0 Then ‘YES’ Else ‘NO’ END) as CK_ValidateExisitingData,
ck.create_date AS CK_CreatDate,
ck.modify_date AS CK_ModifyDate
FROM
sys.Check_constraints ck
INNER JOIN sys.columns c1                                — find the foreign key column name
ON ck.parent_object_id = c1.object_id
AND ck.parent_column_id = c1.column_id
INNER JOIN sys.types t                                       — Get the column datatypes
ON c1.user_type_id = t.user_type_id
ORDER BY 4,5

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

NOT NULL Constraint

In this blog, we’ll discuss about NOT NULL constraint.

In MS SQL NULL means UNKNOWN / MISSING / UNDISCOVERED value.

  1. NOT NULL constraint once added with a column will not allow the column to store the NULL value.
  2. NOT NULL constraint can be added with column added to existing table with / without data / during table definition creation.
  3. NOT NULL constraint can be remove from the column at anytime.
  4. Column set to PRIMARY key is always NOT NULL.It never accepts NULL value.Primary key automatically makes the column NOT NULL even it was not declared while creation.
  5. Column set to UNIQUE key allows a single row with NULL value by default. Until we specify the NOT NULL constraint with the UNIQUE constraint.
  6. Column set to FOREIGN key never allows a NULL value by default.It accepts NULL value only if WITH SET NULL cascade property is set with FOREIGN key definition.
  7. Though SQL Server treated NOT NULL as a Domain constraint, but it never allocates any name for its definition. We cannot find either by procedure ‘sp_helpconstraint’ or under the Constraints group inside the table.

Examples

–Example1: NOT NULL constraint with table definition & NOT NULL constraint with DEFAULT constraint
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) not null DEFAULT ‘New Delhi’
)

–It’ll throw an error as name cannot be NULL
Insert into test values (1,NULL,DEFAULT)

–It’ll successfully insert a record with ‘New Delhi’ as city
Insert into test values (1,’XXX’,DEFAULT)

–Example2: Adding NOT NULL new column in the existing table
Alter table test Add Gender varchar(1) NOT NULL

–Example3: Alter existing column & adding NOT NULL constraint
Alter table test Add Country varchar(50)
Alter table test Alter column Country varchar(50) NOT NULL

–Example4: Removing NOT NULL constraint
Alter table test alter column Gender varchar(1)

To get all the detail information about all the column having NULL or not in all the tables in the current database

select
@@Servername AS ServerName,
DB_NAME() AS DBName,
t1.name as TableName,
c1.name as ColName,
c1.collation_name as SQLCollation,
t.name as ColDataType,
c1.max_length as ColMaxLength,
c1.precision as ColPrecision,
c1.scale as Scale,
(Case c1.is_nullable When 0 Then ‘No’ Else ‘Yes’ End) as Col_IsNULL
from sys.tables t1
inner join sys.columns c1
on t1.object_id=c1.object_id
INNER JOIN sys.types t                            — Get the column datatypes
ON c1.user_type_id = t.user_type_id
order by 3,8

NULL is also keyword in MS SQL Server. To get more details about NULL please click here.

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Default Constraint

In this blog, we’ll discuss about DEFAULT constraint.

Default constraint can be defined in 2 ways : Database level & Table level

Database level definition of the Default constraint

  1. It is older version of defining the constraint in Database level.
  2. It storage location is Database=>Progammability=>Defaults.
  3. It provides a way of code re-usability.
  4. A single constraint can be attached with multiple tables and columns in the table.
  5. It cannot be altered after defining. It need to be drop and re-create with the new definition.
  6. To apply the constraint, it need to be bind with the column.
  7. To drop the constraint, it need to unbind from all the column of the table.
  8. As per Microsoft, it is not a recommended practice to use it, as it’ll be removed from the future version of MS SQL Server.Click here for MSDN link for details.

Example

–Define the DEFAULT constraint
CREATE DEFAULT Default_Gender AS ‘Male’

create table test
(
Id int,
Gender varchar(10)
)

–Bind the DEFAULT constraint
EXEC sp_bindefault ‘Default_Gender’, ‘test.Gender’

insert into test values(1,default)
Select * from test

/* Output
Id Gender
1 Male
*/

–UnBind the DEFAULT constraint or remove the constraint from the column
EXEC sp_unbindefault ‘test.Gender’

–Delete the DEFAULT constraint
Drop DEFAULT Default_Gender

Table level definition of the Default constraint

  1. It is defined in the table level.
  2. It storage location is Database=>Table=>Constraint.
  3. DEFAULT constraints are applied to default values in column of a table which will take when an INSERT statement doesn’t explicitly assign a particular value or mention the word DEFAULT.
  4. DEFAULT constraint can be applied with table definition or in existing table.
  5. DEFAULT constraint never restrict the NULL value/ any other value, until the column is defined with NOT NULL constraint/ any other constraint.
  6. The default value must be compatible with the data type of the column to which the DEFAULT definition applies. For example, the default value for an int column must be an integer number, it cannot be a character string.
  7. Adding a new column with DEFAULT constraint to an existing table never fill the existing rows of the table with the specified Default value(By default). The DEFAULT constraint is applicable to all the future inserts after its declaration. Need to add the ‘with values‘ property with DEFAULT constraint to automatically insert the Default value in all the existing rows.
  8. Adding the DEFAULT constraint with ‘with values‘ property to existing column of a table never insert the Default values to existing rows. Nether it gives any error. It simple add the DEFAULT constraint and will be applicable for future data inserts.
  9. At any time to change the value of the DEFAULT constraint, we first need to drop the existing DEFAULT constraint and then add a DEFAULT constraint with new value.
  10. Any need to change the column’s datatype or need to drop the column (Column which is having the Default constraint) entirely will require first DROP of the DEFAULT constraint on the column prior to your action.

DEFAULT constraint definitions cannot be created on columns defined with the following

  1. Column with IDENTITY or ROWGUIDCOL
  2. Column datatype is timestamp / rowversion
  3. Sparse Column
  4. An existing DEFAULT definition or DEFAULT object.

Examples

–Example1: DEFAULT constraint with table defination
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) not null DEFAULT ‘New Delhi’
)

–Example2: DEFAULT constraint with table defination with constraint name
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) not null constraint Default_test_city DEFAULT ‘New Delhi’
)

–Example3: DEFAULT constraint outside table defination with constraint name
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) not null
)

ALTER TABLE [dbo].[test]
ADD CONSTRAINT [Default_test_city] DEFAULT (‘New Delhi’) FOR city

Insert result for all the above examples[1/2/3] have same result as ‘New Delhi’ assigned value for Id=1 with DEFAULT Keyword 
insert into test values (1,’ABC’,DEFAULT),(2,’XYZ’,’PUNJAB’)
–Example4: DEFAULT constraint with table defination without NOT NULL
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) DEFAULT ‘New Delhi’
)

–Insert result for the above example4 have NULL value assigned value for Id=1 ). As DEFAULT does not restrict NULL value
insert into test values (1,’ABC’,NULL),(2,’XYZ’,’PUNJAB’)

–Example5: Add a new column in existing table with DEFAULT constraint name
create table test
(
Id int primary key,
name varchar(50) not null
)

ALTER TABLE test  ADD city varchar(50) NOT NULL
    CONSTRAINT Default_test_city ‘New Delhi’

–Example6: Default constraint with NULL value.Though this example does not make any practical meaning, but to show that NULL can also be use as Default value, we are showing this example
create table test
(
Id int primary key,
name varchar(50) DEFAULT (NULL)
)

–Example7: Adding a new column in existing table with DEFAULT constraint name & also insert the Default value to the existing rows
alter table test add gender varchar(1) default(‘M’) with values

–Example8: Adding a new NOT NULL column in existing table with DEFAULT constraint name & also insert the Default value to the existing rows
alter table test add gender varchar(1) not null default(‘M’) with values

Note : DEFAULT constraint & DEFAULT keyword are different. To know more about DEFAULT keyword click here.

To display all the DEFAULT constraint information on all the tables in current database (Note : DK=Default key)

SELECT
@@Servername AS ServerName,
DB_NAME() AS DB_Name,
OBJECT_SCHEMA_NAME(dk.parent_object_id) AS SchemaName,
OBJECT_NAME(dk.parent_object_id) AS TableName,
c1.name as ColName,
t.name as DK_ColDataType,
c1.max_length as DK_ColMaxLength,
c1.precision as DK_ColPrecision,
c1.scale as DK_Scale,
(Case c1.is_nullable When 0 Then ‘No’ Else ‘Yes’ End) as DK_ColIsNULL,
dk.Name AS DK_Const_Name ,
OBJECT_DEFINITION(dk.object_id) AS DK_Const_Defination,
dk.create_date AS DK_CreateDate,
dk.modify_date AS DK_ModifyDate
FROM
sys.default_constraints dk
INNER JOIN sys.columns c1                                — find the foreign key column name
ON dk.parent_object_id = c1.object_id
AND dk.parent_column_id = c1.column_id
INNER JOIN sys.types t                                          — Get the column datatypes
ON c1.user_type_id = t.user_type_id
ORDER BY 4,5

To drop or remove the Default constraint , we need to provide the constraint name. If the constraint name is auto generated then get it GUI from the left side in SSMS under the table => Goto constraint => click on the constraint and drag it over the command window.

ALTER TABLE [dbo].[test] DROP CONSTRAINT [Default_test_city]

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.