SQL Constraints

SQL Constraints


SQL Constraints are used to give some rules for the columns of a table. we can create and define constraints on one or more columns of the table. This maintains the data integrity of the table.

Constraints can be divided into two parts:-

1. Column Level Constraints:- It is applied to the only column.

2. Table Level Constraints:- It is applied to the whole table.

Syntax: -
CREATE TABLE table_name
(
column1 datatype constraints,
column1 datatype constraints,
- - - - - - - - - - - - - - - - - - - - - 
- - - - - - - - - - - - - - - - - - - - - 
columnN datatype constraints
);


Types of Constraints in SQL 

  • NOT NULL:  It cannot have any NULL value in the column.

  • PRIMARY KEY:  Primary Key must contain a UNIQUE value.

  • FOREIGN KEY: It is used to uniquely identifies a records row/record in another table.

  • UNIQUE: Unique constraint is used to uniquely identify each row in the table.

  • DEFAULT: Default constraint is used to add a default value.

  • CHECK: Check constraint is used to restrict the value of a column b/w a range.

  • INDEX: Index constraint is used to create indexes in tables.


1. NOT NULL

If no value is given in each column of the table of SQL, then by default is 'NULL' (no value). If you do want a NULL set, then it is necessary to give constraints for that column.

Example: Let's see, how to create NOT NULL Constraints. we create a table called my_tb and Id set the NOT NULL constraint.

Query : 
CREATE TABLE my_tab
(
Id int(10) NOT NULL,
Name varchar(30),
Address varchar(50)
);
Output :


Use DESC Command, Now you can see 'Id' contain NOT NULL value.

Query :
DESC my_tb;


2. Primary Key

The PRIMARY KEY Constraint used to uniquely identifies each record in a table. 

The primary Key must contain a UNIQUE value. It cannot contain NULL values and the rest of the table data must be unique and each table can have only ONE primary key.
PRIMARY KEY is also given on the Column level (Single column) and also on table column (Multiple columns).

Syntax: For Column Level 
CREATE TABLE table_name
(
column_name datatype PRIMARY_KEY,
column_name datatype,
- - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - -
);
Example: Let's see How to create PRIMARY KEY in the table. we create a table, the id on this table is set PRIMARY KEY for this column.

Query:
CREATE TABLE my_tab
(
Id int(10) NOT NULL PRIMARY KEY,
Name varchar(30),
Address varchar(50)
);

Output : 


The Primary key in Table is created, Now you can use DESC command and see the table.

Query:
DESC my_tab;

Output:



3. Foreign Key

Foreign Key is one table point to a PRIMARY KEY in another table. 
Foreign Key is used to describe the column of a given table on the column of another table.

A Foreign key can be used to make confirm that the row in one table has the corresponding row in another table.

Example: Let's see how to create a Foreign key in the table, we have two tables- employee and department.

Query:
CREATE TABLE department
(
did int(10) NOT NULL PRIMARY KEY,
dname varchar(50),
empid int(10) NOT NULL,
CONSTRAINT employee_eid_fk
FOREIGN KEY(empid) REFERENCES employee(eid)
);

Output:


In this output, we have employee table eid is the primary key which is set as a foreign key in the department.

Add Foreign Key Constraint

For example, if a FOREIGN key does not go to the customer and wants to give it separately then it is used with the ALTER TABLE statement.

Query:
ALTER TABLE department
(
ADD COLUMN empid int(10) NOT NULL PRIMARY KEY,
ADD CONSTRAINT employee_eid_fk
FOREIGN KEY(empid) REFERENCES employee(eid)
);

Output:


Now, you can use the SQL query and see the  FOREIGN KEY in Table.

Query :
SELECT * from INFORMATION_SCHEMA. TABLE_CONSTRAINTS 
WHERE TABLE_NAME = 'department';

Output :



4. UNIQUE

In SQL, the UNIQUE constraint uniquely identifies each record in a database table.
There can be many UNIQUE constraints per table. A Unique key column contains NULL values in the table.
PRIMARY KEY can also be used instead of UNIQUE constraint. But the UNIQUE constraint can be used more than once in a table. But PRIMARY KEY can be used only once in a table.

Syntax:-
column_name data_type UNIQUE KEY
Example: The following SQL statement creates a table named students and specifies the roll no column as unique. That means this field does not allow duplicate values.

Query:
CREATE TABLE students (
stu_id int(5),
name varchar(50),
roll int(5) UNIQUE KEY,
city varchar(50)
);

Output:


Now, You can see in students table roll is a UNIQUE key.


5. Default

Default Constraints are used to specify the constant value for a column. and the user does not need to insert the value for such a column.
If one wants to set the DEFAULT value to the value of the column then DEFAULT constraints are used.

Example: Let's see how to create a Default constraint in the table.

Query:
CREATE TABLE students
(
Id int(5) NOT NULL,
Name varchar(50) NOT NULL,
Address varchar(50),
City varchar(50) DEFAULT 'Meerut'
);

Output: 

The following SQL creates a new table called students and four columns. here the city column is set to 'Meerut' by DEFAULT. so in case, the insert into statement does not provide a value for this column, then by default, this column would be set to 'Meerut'.

6. CHECK 

The CHECK constraint is used to give the limit for the values given on the column.
CHECK is constraints column- Level (Single column) and table- Level (Multiple columns).

Syntax:-
column_name data_type CHECK (condition)

Example: Let's see how to create CHECK constraint in a table.

Query:
CREATE TABLE student
(
id INT(2) NOT NULL,
name VARCHAR(50),
marks INT(2) CHECK (marks > 40)
);
Output: 

The following creates a new table called the student and add three columns here. we add a CHECK with marks column so that you cannot have any student who is below 40 marks.


7. INDEX

Create an INDEX  that is used to create indexes in tables.
Indexes are used to rapidly create and retrieve data in a table.

Syntax:-

CREATE INDEX index_name
ON table_name (column1, column2,.................,columnN);

Example: let's how to create index statements in the table.

Query:
CREATE INDEX ind_id
ON student (name);
Output: 

Now, you can see the  INDEX created in the student table.


Drop  an INDEX Constraints 

To drop  INDEX constraints, use the following syntax in SQL :

Query:-
ALTER TABLE student
DROP ind_id;
Example: Let's see how to drop the index in the table.

Output: 




Post a Comment

0 Comments