Constraints

Constraints are the rules enforced on data columns on a table which ensures the accuracy and reliability/maintain the integrity of the data in the database. Two types of constraints such are:

Table Level Constraints - constraint is checked when the value of the row changed which means is checked each time the row has been affected by any change.

CREATE TABLE [TableLevel]

(     [ProjectId] INT PRIMARY KEY,

      [StartDate] DATE NOT NULL,

      [EndDate] DATE NOT NULL,

      [VerifyDate] DATE NOT NULL,

      CONSTRAINT TableLevel_CONSTRIANT

     CHECK( [VerifyDate] BETWEEN [StartDate] AND [EndDate])

)

Column Level constraints - constraint is checked when the value of the column changed.

CREATE TABLE [ColumnLevel]

(     [ProjectId] INT PRIMARY KEY,

      [StartDate] DATE NOT NULL,

      [EndDate] DATE NOT NULL,

      [VerifyDate] DATE NOT NULL,

      CONSTRAINT ColumnLevel_CONSTRIANT CHECK([VerifyDate] <= getdate())

)

·       NOT NULL Constraint: Ensures that a column cannot have NULL value.

·       DEFAULT Constraint: Provides a default value for a column when none is specified.

·       UNIQUE Constraint: Ensures that all values in a column are different.

·       PRIMARY KEY: Uniquely identified each rows/records in a database table.

·       FOREIGN KEY: Uniquely identified a rows/records in any another database table.

·       CHECK Constraint: Ensures that all values in a column satisfy certain conditions.

·      INDEX: Use to create and retrieve data from the database very fast.

Example:

       -- Create Department table with necessary constraints

       CREATE TABLE [dbo].[Department](

       [DepartmentID] [int] NOT NULL IDENTITY(0,1),

       [DepartmentName] [varchar](50) NOT NULL,

       [IsActive] [bit] NOT NULL CONSTRAINT DF_Department_IsActive DEFAULT (1)

       CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED

       (

              [DepartmentID] ASC

       ) ON [PRIMARY]

     ) ON [PRIMARY]

    -- Create Employee table with necessary constraints

    CREATE TABLE [dbo].[Employee](

    [EmployeeID] int NOT NULL IDENTITY(1,1),

    [DepartmentID] int NOT NULL CONSTRAINT FK_Employee_DepartmentID

                              REFERENCES [Department](DepartmentID),

    [EmployeeName] varchar(50) NOT NULL,

    [Gender] char(1) NOT NULL CONSTRAINT CH_Employee_Gender

                     CHECK ([Gender] in('M','F','O')),

    [BirthDate] date NOT NULL,

    [Salary] decimal(10,2) NULL,

    [Detail] varchar(max) NULL,

    [IsActive] bit NOT NULL CONSTRAINT DF_Employee_IsActive DEFAULT (1),

    CONSTRAINT [UQ_Employee_EmployeeName] UNIQUE(EmployeeName),

    CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED

      (

          [EmployeeID] ASC

      ) ON [PRIMARY]

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Examples

-- Add PRIMARY KEY Constraints

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND name = N'PK_Employee_EmployeeID')

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [PK_Employee_EmployeeID]

GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED

(

      [EmployeeID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

-- Add FOREIGN KEY Constraints

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Employee_DepartmentID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Employee]'))

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [FK_Employee_DepartmentID]

GO

ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_DepartmentID] FOREIGN KEY([DepartmentID])

REFERENCES [dbo].[Department] ([DepartmentID])

GO

-- Add UNIQUE Constraints

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') AND name = N'UQ_Employee_EmployeeName')

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [UQ_Employee_EmployeeName]

GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [UQ_Employee_EmployeeName] UNIQUE NONCLUSTERED

(

      [EmployeeName] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

-- Add CHECK Constraints

--IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CH_Employee_Gender]') AND parent_object_id = OBJECT_ID(N'[dbo].[Employee]'))

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[CH_Employee_Gender]') AND type = 'C')

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [CH_Employee_Gender]

GO

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

GO

-- Add DEFAULT Constraints

IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Employee_IsActive]') AND type = 'D')

BEGIN

ALTER TABLE [dbo].[Employee] DROP CONSTRAINT [DF_Employee_IsActive]

END

GO

ALTER TABLE [dbo].[Employee] ADD  CONSTRAINT [DF_Employee_IsActive]  DEFAULT (1) FOR [IsActive]

GO