SQL Constraints

SQL Constraints

Constraints are the rules that can be applied on the type of data in a table. i.e, it can be used to limit the type of data that can go into a table.

Constraints can be used either when the table is created, or after the table is created.

It can be either applied to a column in a table or to the whole table.

The constraints used in SQL are:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT
• CREATE INDEX

 

NOT NULL: if this constraint is used in any column, then it will not allow NULL values in that particular column anymore.

Eg:

CREATE TABLE StudentDetail (

    ID int NOT NULL,

    Name varchar(250) NOT NULL,

    Branch varchar(250) NOT NULL

);

In this command, all the columns are specified with NULL Constraint, so all the columns will allow NULL data in it.

 

UNIQUE: if this constraint is used in any column, then it does not allow to repeat any data or value inn that particular column anymore.

Eg:

CREATE TABLE StudentDetail (

    ID int NOT NULL UNIQUE,

    Name varchar(250) NOT NULL,

    Branch varchar(250) NOT NULL

);

In this command, all the ID column is specified with UNIQUE Constraint, so that the ID cannot be repeated with same data.

 

PRIMARY KEY: This constraint uniquely identifies each record in a table or column and also does  not allow NULL data in  it.

Eg:

CREATE TABLE StudentDetail (

    ID int,

    Name varchar(250) ,

    Branch varchar(250),

PRIMARY KEY (ID)

);

In this command, the PRIMARY KEY Constraint is specified for ID, so that the ID cannot be repeated with same data, also ID cannot be NULL data.

 

FOREIGN KEY: The FOREIGN KEY Constraint references a row in another table, here, the table with the foreign key Constraint is called as the child table, and the table with the primary key is called the parent table.

Eg:

Parent Table::

CREATE TABLE StudentDetail (

    ID int,

    Name varchar(250) ,

    Branch varchar(250),

PRIMARY KEY (ID)

);

Child Table::

CREATE TABLE Marks (

    Mark int NOT NULL,

    Subcode int NOT NULL,

    ID int,

    PRIMARY KEY (Mark),

    FOREIGN KEY (ID) REFERENCES StudentDetail(ID)

);

Here the “Mark” of a student in the “Table Marks” refers to the “ID”  of the student in the “Table StudentDetail”.

 

CHECK: The CHECK Constraint validates condition for new value.

Eg:

CREATE TABLE StudentDetail (

    ID int NOT NULL,

    Name varchar(250) NOT NULL,

    Branch varchar(250) NOT NULL,

CHECK (Branch=’CSE’)

);

In this command, the CHECK Constraints, allows only the CSE as Branch.

 

DEFAULT: This Constraint is used to set a default value for any particular column, if any value is not specified.

Eg:

CREATE TABLE StudentDetail (

    ID int NOT NULL,

    Name varchar(250) NOT NULL,

    Branch varchar(250) DEFAULT ‘CSE’,

);

In this command, the DEFAULT Constraints, fills the Branch as CSE in default when Branch is not specified.

 

CREATE INDEX: This Constraint is used to speed-up the process, i.e it can create and retrieve any data from the database more quickly.

Eg:

CREATE TABLE StudentDetail (

    ID int NOT NULL,

    Name varchar(250) NOT NULL,

    Branch varchar(250) NOT NULL

);

CREATE INDEX ix_Branch

ON StudentDetail (Branch);

Here, in the table StudentDetail, the CREATE INDEX command creates an index ix_Branch using the Branch column.

 

Social tagging: .net training in Chennai > SQL CONSTRAINTS > SQL Server Online Training