SQL with Manoj

In my previous posts I discussed about [Entity Integrity] constraint which deals with Primary Keys & Unique Keys, and [Referential Integrity] constraint, which deals with Foreign Keys, please check the above links to know about them.
 

Here in this post I’ll discuss about Domain Integrity which validates the entries for a given column in a particular table. The Domain integrity can be enforced by:

1. Applying appropriate DATATYPES
 

2. NOT NULL constraint: enforces a column to NOT accept NULL values. Which means the columns must always contain a value and you cannot insert or update a row without specifying a value for that column.

For example: Employee name is mandatory while inserting an employee’s record, thus you can have [EmployeeName] column with NOT NULL constraint, like: [EmployeeName] nvarchar(100) NOT NULL
 

3. DEFAULT constraint: can be used to Insert a default value into a column, if no other value is provided while Inserting a row. This constraint works only with INSERT statement.

For example: To track the rows insert timestamp you can add this constraint to that column, like: [CreatedOn] datetime DEFAULT (getdate())
 

4. CHECK constraint: enforces a column to have only limited and pre-defined values. Means you cannot insert or update a row with values that are not defined with this constraint.

For example: An Employee gender could only be Male or Female, so you can have [Gender] column with CHECK constraint, like: [Gender] nchar(1) CHECK ([Gender] IN (‘M’, ‘F’))
 

–> Check the video on how to work with these three constraints (NOT NULL, CHECK and DEFAULT):

–> SQL Code used in above video:

--// Domain Integrity Constraints:


-- 1. NOT NULL Constraint:

CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100) NOT NULL
	,[Gender]		nchar(1)
)
GO

insert into [Employee] ([EmployeeName], [Gender])
values (NULL, NULL)

/*
Msg 515, Level 16, State 2, Line 13
Cannot insert the value NULL into column 'EmployeeName', table 'TestManDB.dbo.Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.
*/

select * from [Employee]
GO



-- 2. DEFAULT Constraint:

DROP TABLE [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100) NOT NULL
	,[Gender]		nchar(1)
	,[isActive]		bit				DEFAULT (1)
	,[CreatedOn]	datetime		DEFAULT (getdate())
	,[CreatedBy]	varchar(100)	DEFAULT (SYSTEM_USER)
)
GO

insert into [Employee] ([EmployeeName], [Gender])
values ('Manoj Pandey', 'M')

insert into [Employee] ([EmployeeName], [Gender])
values ('Kanchan Pandey', 'F')

insert into [Employee] ([EmployeeName], [Gender], [isActive])
values ('Maria Y', 'F', 0)

insert into [Employee] ([EmployeeName], [Gender], [CreatedOn], [CreatedBy])
values ('Brock H', 'M', '2015-01-01 21:32:07.153', 'scott')

select * from [Employee]
GO



-- 3. CHECK Constraint:

DROP TABLE [dbo].[Employee]
GO

CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100) NOT NULL
	,[Gender]		nchar(1)		CONSTRAINT ck_emp_gender CHECK ([Gender] IN ('M', 'F'))
	,[isActive]		bit				CONSTRAINT ck_emp_isActive DEFAULT (1)
	,[CreatedOn]	datetime		CONSTRAINT ck_emp_CreatedOn DEFAULT (getdate())
	,[CreatedBy]	varchar(100)	CONSTRAINT ck_emp_CreatedBy DEFAULT (SYSTEM_USER)
)
GO

insert into [Employee] ([EmployeeName], [Gender])
values ('Manoj Pandey', 'X')

/*
Msg 547, Level 16, State 0, Line 67
The INSERT statement conflicted with the CHECK constraint "CK__Employee__Gender__4D5F7D71". The conflict occurred in database "TestManDB", table "dbo.Employee", column 'Gender'.
The statement has been terminated.
*/

insert into [Employee] ([EmployeeName], [Gender])
values ('Manoj Pandey', 'M')

select * from [Employee]
GO




--// Table Generated from SSMS - Object Explorer

USE [TestManDB]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_gender]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_CreatedBy]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_CreatedOn]
GO

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [ck_emp_isActive]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 3/30/2016 9:45:17 PM ******/
DROP TABLE [dbo].[Employee]
GO

/****** Object:  Table [dbo].[Employee]    Script Date: 3/30/2016 9:45:17 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Employee](
	[EmployeeID] [int] IDENTITY(100,1) NOT NULL,
	[EmployeeName] [nvarchar](100) NOT NULL,
	[Gender] [nchar](1) NULL,
	[isActive] [bit] NULL,
	[CreatedOn] [datetime] NULL,
	[CreatedBy] [varchar](100) NULL,
PRIMARY KEY CLUSTERED 
(
	[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [ck_emp_isActive]  DEFAULT ((1)) FOR [isActive]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [ck_emp_CreatedOn]  DEFAULT (getdate()) FOR [CreatedOn]
GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [ck_emp_CreatedBy]  DEFAULT (suser_sname()) FOR [CreatedBy]
GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [ck_emp_gender] CHECK  (([Gender]='F' OR [Gender]='M'))
GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [ck_emp_gender]
GO



-- Final Cleanup:

DROP TABLE [dbo].[Employee]
GO

To know about more on Constraints and their types check this blog post.