What is Constraints in SQL

Let’s understand, What is Constraints in SQL. SQL Constraints are used to enforce the rules on the table.

Constraints have also prevented the deletion of a table if there are dependencies.

Constraints can be specified when the table is created or after the table is created.

 

SQL Syntax :

SQL > CREATE TABLE table_name (
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name );

 

The following constraints types are valid –

  • NOT NULL

  • PRIMARY KEY

  • FOREIGN KEY

  • CHECK

  • UNIQUE

 

NOT NULL

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.

SQL > CREATE TABLE employee (

            emp_id number(5) NOT NULL,
name char(20), dept char(10), age number(2),
salary number(10), location char(10)  );

 

UNIQUE

The UNIQUE constraint uniquely identifies each record in a database table. This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.

SQL > CREATE TABLE employee (

            emp_id number(5) NOT NULL,
name varchar2(20), 

             email varchar2(30), 

             age number(2),
CONSTRAINT emp_email UNIQUE (email)  );

 

PRIMARY KEY

This constraint defines a column or combination of columns which uniquely identifies each row in the table.

SQL > CREATE TABLE employee (

            emp_id number(5) NOT NULL,
name varchar2(20), 

             email varchar2(30), 

             age number(2),

             dept_Name varchar2(40),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)  );

 

FOREIGN KEY

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as the Foreign key.

SQL > CREATE TABLE Department (

            Dept_id number(5),
Dept_name varchar2(20), 

            Location varchar2(30), 

            CONSTRAINT emp_id_fk FOREIGN KEY(Dept_id)

            REFERENCES Employees(dept_name)  );

 

CHECK

Check defines the condition that each row must satisfy. The constraint can be applied to a single column or a group of columns.

SQL > CREATE TABLE employee (

            Emp_id number(5) PRIMARY KEY,
name varchar(20),
dept varchar(10),
gender char(1) CHECK (gender in (‘M’,’F’)),
salary number(10) );

 

0 Comment

Leave a Reply