SQL Constraints

Filed Under: SQL

In a real-world scenario, there are cases when we would like to restrict the type of data that is getting stored in the DB table. In order to achieve the restriction on the database, SQL provides a set of constraints.

Introduction

SQL constraints are a set of rules that are used for restricting the data that will be inserted in the database table. There are two types of constraints.

  1. Table-level constraints.
  2. Column level constraints.

The column based constrains is applicable only to one column whereas the table level constraint is applicable for the complete table.

Please find below the list of constraints.

  1. NOT NULL
  2. UNIQUE
  3. PRIMARY KEY
  4. FOREIGN KEY
  5. CHECK
  6. DEFAULT

Let’s discuss all the above-mentioned constraints in detail.

1. NOT NULL Constraint

SQL NOT NULL constraint specifies that the column can not contains any null value for the column. The constraint can be specified during table creation or alteration.

Syntax: –

CREATE TABLE <table_name>(<column_name> <data_type> NOT NULL);

In the syntax above, a NOT NULL keyword is used to specify that the column can not accept a null value.

Let’s create a customer table to understand this better.

CREATE TABLE customer
(cust_id varchar(8) NOT NULL UNIQUE, 
cust_name varchar(50) NOT NULL, 
state varchar(25), 
country varchar(25) );

Post-execution of the above query at MySQL workbench. The following screen will appear.

Not Null Example

Not Null Example

On performing a table inspection following screen will appear.

Not Null Column

Not Null Column

The highlighted value shows that “No” values mean that the column will not accept a null value.

2. UNIQUE Constraint

UNIQUE specifies that the column can not duplicate value for the column. The constraint can be specified during table creation or alteration.

Syntax: –

CREATE TABLE <table_name>(<column_name> <data_type> UNIQUE);

In the syntax above, a UNIQUE keyword is used to specify that the column can not accept a duplicate value.

Let’s create a customer table to understand this better.

CREATE TABLE customer
(cust_id varchar(8) NOT NULL UNIQUE, 
cust_name varchar(50) NOT NULL, 
state varchar(25), 
country varchar(25) );

Post-execution of the above query at MySQL workbench. The following screen will appear.

On performing a table inspection following screen will appear.

Unique Column

Unique Column

The value for Unique column is “YES” which mean that the column will accept a unique value.

3. PRIMARY KEY Constraint

In order to uniquely identify a row in a table, during table creation we specify a column or combination of columns as a primary key. The primary key should always be not null. Only one primary key can be specified for a table.

Syntax: –

CREATE TABLE <table_name>(<column_name1> <data_type> NOT NULL,
<column_name2> <data_type> NOT NULL,
<column_name3> <data_type> NOT NULL,
PRIMARY KEY (column_name1));

In the syntax above, a PRIMARY KEY keyword is used to specify that the column will be used a the primary key for the table.

Let’s create a customer table to understand this better.

CREATE TABLE customer
(cust_id varchar(8) NOT NULL UNIQUE, 
cust_name varchar(50) NOT NULL, 
state varchar(25), 
country varchar(25),
PRIMARY KEY (cust_id) );

Post-execution of the above query at MySQL workbench. The following screen will appear.

On performing a table inspection following screen will appear.

Primary Key Column

Primary Key Column

The value for Unique column is “YES” which mean that the column will accept a unique value. Also, the key column has row value “PRIMARY” and the row value for columns column specifies the column that will be the primary key.

4. FOREIGN KEY Constraint

In order to have a connection of a row in a table with another table, during table creation, we specify a column or combination of columns as a foreign key. The foreign key should always be not null.

Syntax: –

FOREIGN KEY [column list] REFERENCES [primary key table] ([column list]);

In the syntax above, a FOREIGN KEY keyword is used to specify the column list on which foreign key needs to be set. The primary key table is the table from which the foreign key will be used.

Let’s create a customer table to understand this better.

CREATE TABLE customer
(cust_id varchar(8) NOT NULL UNIQUE, 
cust_name varchar(50) NOT NULL, 
state varchar(25), 
country varchar(25),PRIMARY KEY (cust_id) );

CREATE TABLE new_customer
(cust_id varchar(8) NOT NULL UNIQUE,
old_id varchar(8),
cust_name varchar(50) NOT NULL, 
state varchar(25), 
country varchar(25),FOREIGN KEY (old_id) references customer(cust_id));

Post-execution of the above query at MySQL workbench. The following screen will appear.

On performing a table inspection following screen will appear.

Foreign Key Column

Foreign Key Column

The foreign key tab provides the details of the foreign keys.

5. CHECK Constraint

There are cases when you would like to restrict the value that will be stored in the column.

Syntax: –

CREATE TABLE <table_name>(<column_name> <data_type> CHECK (range for check));

In the syntax above, the keyword CHECK is used for specifying a range.

Let’s create a table customer to understand this better.

CREATE TABLE customer
(cust_id varchar(8) NOT NULL UNIQUE, 
cust_name varchar(50) NOT NULL, 
phone decimal (10) check (phone=10),
state varchar(25), 
country varchar(25),PRIMARY KEY (cust_id) );

Post-execution of the above query at MySQL workbench. When data insertion is done the constraint is checked for the phone column.

6. DEFAULT Constraint

Using the DEFAULT keyword we can specify a default value for the column.

Syntax: –

CREATE TABLE <table_name>(<column_name> <data_type> DEFAULT 'value');

In the syntax above, the keyword DEFAULT is used for providing a default value.

Let’s create a table customer to understand this better.

CREATE TABLE customer
(cust_id varchar(8) NOT NULL UNIQUE, 
cust_name varchar(50) NOT NULL, 
phone decimal (10) check (phone=10),
state varchar(25), 
country varchar(25) DEFAULT 'India',PRIMARY KEY (cust_id) );

Post-execution of the above query at MySQL workbench. When data insertion is done, if any value is not provided it will consider ‘India’ as the value.

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages