SQL Constraints

SQL Constraints


Constraints are defined when a table is created or table is modified.

Constraints are used to limit the type of data that can go into a table.

Various types of constraints are:

·         NOT NULL

·         UNIQUE

     ·         PRIMARY KEY
    
     ·         FOREIGN KEY

·         CHECK

     ·         DEFAULT

SQL NOT NULL Constraint

NOT NULL Constrains specifies column does not accept / contain NULL values.

The NOT NULL constraint enforces a field to must contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the "[EmployeeId]" column and the "EmployeeCode" column to not accept NULL values:

CREATE TABLE [dbo].[EmployeeMaster](

                [EmployeeId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,

                [EmployeeCode] [varchar](30) NOT NULL,

                [FirstName] [varchar](100)  NULL

                 

 CONSTRAINT [PK_EmployeeMaster] 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])

 

SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

You can create  many UNIQUE Key constraints per table, but only one PRIMARY KEY per table.

SQL PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:

DeptMapping

DeptID	EmpID	CreatedBy
1	1	sandeepm
2	1	sandeepm

EmployeeMaster

EmpID	Name	Age
1	sandeep	26
2	ABC	20

Here in above 2 tables “EmpID” is a primary key in EmployeeMaster Table, where as DeptID is a primary key in DeptMapping Table

 

Relation between these 2 tables can be created by foreign key.

Here EmpID column is referred as foreign key to DeptMapping Table

 So that the EmpID column in DeptMapping table can have only values from EmployeeMaster Table

 

SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[DeptMapping](

                [DepartmentID] [int] NOT NULL,

                [EmployeeID] [numeric](18, 0) NOT NULL,

                [CreatedBy] [varchar](50) NULL,

                [CreatedDate] [datetime] NULL,

 CONSTRAINT [PK_DeptMapping] PRIMARY KEY CLUSTERED

(

                [DepartmentID] ASC

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

) ON [PRIMARY]

 

 

ALTER TABLE [dbo].[DeptMapping] ADD  CONSTRAINT [DF_DeptMapping_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]

GO

Here column CreatedDate has been mapped with a default constrains, so when the values are not passed while inserting a data then default values are stored in column

In this case getdate() method is used.

So when new record inserted into table System date stored in column