w3resource logo


SQL constraints

SQL CONSTRAINTS

Secondary Nav

Introduction

The SQL CONSTRAINTS are an integrity which defines some conditions that restrict the column to remain true while inserting or updating or deleting data in the column. Constraints can be specified when the table created first with CREATE TABLE statement or at the time of modification of the structure of an existing table with ALTER TABLE statement.

The SQL CONSTRAINTS are used to implement the rules of the table. If there is any violation of the constraints caused some action not performing properly on the table the action is aborted by the constraint.

Some CONSTRAINTS can be used along with the SQL CREATE TABLE statement.

The general structure of the SQL CONSTRAINT is defined as :

The CONSTRAINT keyword is followed by a constraint name followed by a column or a list of columns.

Types of SQL CONSTRAINTS

The SQL provides following types of CONSTRAINTS :

Constraint Description
NOT NULL This constraint confirms that a column cannot store NULL value.
UNIQUE This constraint ensures that each row for a column must have a different value.
PRIMARY KEY This constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. This constraint ensures that the specific column or combination of two or more columns for a table have a unique identity which helps to find a particular record in a table more easily and quickly.
CHECK A check constraint ensures that the value stored in a column meets a specific condition.
DEFAULT This constraint provides a default value when specified none for this column.
FOREIGN KEY A foreign key constraint is used to ensure the referential integrity of the data. in one table to match values in another table.

Syntax

CREATE TABLE <table_name>(
column1    data_type[(size)]   constraint,
column2    data_type[(size)]   constraint,
...);

Parameters

Name Description
table_name Name of the table where data is stored.
column1,column2 Name of the columns of a table.
data_type Char, varchar, integer, decimal, date and more.
size Maximum length of the column of a table.
constraint Constraint for the column or table.

Contents:

SQL CREATE TABLE with constraint to exclude NULL value

The following topic will describe how the NOT NULL CONSTRAINT confirms that a column can not have NULL value in CREATE TABLE statement.

Example

The following example creates a table. Here is the field name and data types :

Field Name Data Type Size Decimal Places NULL
agent_code char 6   No
agent_name char 25   No
working_area char 25   No

the following SQL statement can be used :

CREATE TABLE mytest(
agent_code char(6) NOT NULL,
agent_name char(25) NOT NULL,
working_area char(25) NOT NULL) ;

To see the structure of the created table :

DESCRIBE mytest;

Output

Sql create table with constraint to exclude NULL value

SQL CREATE TABLE to check unique value

In the following topic, we are going to discuss, how the SQL UNIQUE CONSTRAINT ensures that each row for a column has different values in CREATE TABLE statement.

Example

The following example creates a table. Here is the field name and data types :

Field Name Data Type Size Decimal Places NULL Constraint
ord_num decimal 6   No UNIQUE
ord_amount decimal 12 2 Yes  
ord_date date     No  
cust_code char 6   No  
agent_code char 6   No  

the following SQL statement can be used :

CREATE TABLE  mytest(
ord_num decimal(6) NOT NULL UNIQUE,
ord_amount decimal(12,2),
ord_date date NOT NULL,
cust_code char(6) NOT NULL,
agent_code char(6) NOT NULL);
   

To see the structure of the created table :

DESCRIBE mytest; 

Output

Sql create table check unique value

SQL CREATE TABLE to check unique value on more columns

The following example creates a table. Here is the field name and data types :

Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6   No UNIQUE
agent_name char 25   No UNIQUE
working_area char 25   No  
commission decimal 5 2 Yes  

the following SQL statement can be used :

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE,
agent_name char(25) NOT NULL UNIQUE,
working_area char(25) NOT NULL,
commission decimal(5,2));
   

To see the structure of the created table :

DESCRIBE mytest; 
Output :

Sql create table check unique value on more columns

SQL CREATE TABLE with UNIQUE CONSTRAINT

The SQL UNIQUE constraint is used to ensure that the each row for a column have a different value. In this page we are going to discuss, how the SQL UNIQUE CONSTRAINT works if it is used at the end of the CREATE TABLE statement instead of using the UNIQUE CONSTRAINT in the specific columns.

Example :

The following example creates a table. Here is the field name and data types :

Field Name Data Type Size Decimal Places NULL Constraint
cust_code char 6   No UNIQUE
cust_name char 25   No  
cust_city char 25   No  
grade integer     Yes  
agent_code char 6   No UNIQUE

the following SQL statement can be used :

CREATE TABLE mytest(
cust_code char(6) NOT NULL,
cust_name char(25) NOT NULL,
cust_city char(25) NOT NULL,
grade integer,
agent_code char(6) NOT NULL,
UNIQUE(cust_code,agent_code));
   

To see the structure of the created table :

DESCRIBE mytest; 
Output :

Sql create table with unique constraint

SQL CREATE TABLE with CHECK CONSTRAINT

The SQL CHECK CONSTRAINT ensures that a value for a specific column or columns has satisfied a specified condition.

The job of CHECK constraint is, to limit the values for a column of a table.

Note :
  • The SQL CHECK CONSTRAINT can not be used on a VIEW.
  • The SQL CHECK CONSTRAINT can not be used in a subquery.
  • The SQL CHECK CONSTRAINT can also be used in ALTER TABLE and DROP TABLE statement.

The following example creates a table. The table contains a CHECK CONSTRAINT on commission column.The constraint ensures that the 'commission' must be less than 1. Here is the field name and data types :

Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6   No UNIQUE
agent_name char 25   No UNIQUE
working_area char 25   No  
commission integer       CHECK

the following SQL statement can be used :

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) NOT NULL ,
commission decimal CHECK( commission<1));
   

To see the structure of the created table :

DESCRIBE mytest; 
Output :

SQL CREATE TABLE using DEFAULT CONSTRAINT

The SQL DEFAULT CONSTRAINT provides a default value when specified none for a column.

Example :

To include a DEFAULT CONSTRAINT on 'working_area' column which ensures that -

1. The 'working_area' should be 'Mumbai' when specified none for this column,

at the time of creating a table whose field names and data types are -

Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6   No UNIQUE
agent_name char 25   No UNIQUE
working_area char 25   Yes DEFAULT
commission decimal 8 2 Yes  

the following SQL statement can be used :

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) DEFAULT 'Mumbai',
commission decimal(8,2)); 
          

To see the structure of the created table :

DESCRIBE mytest; 
          
Output :

Sql create table using check and default constraint

SQL CREATE TABLE using default values and CHECK CONSTRAINT

In the following topic, we have discussed the usage of SQL DEFAULT CONSTRAINT and usage of SQL CHECK CONSTRAINT while creating a table.

Example :

To include a CHECK CONSTRAINT on 'commission' and a DEFAULT CONSTRAINT on 'working_area' column which ensures that -

1. The 'commission' must be more than .1 and less than .3,

2. The 'working_area' should be 'Mumbai' when specified none for this column,

at the time of creating a table which contains the following field names and data types -

Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6   No UNIQUE
agent_name char 25   No UNIQUE
working_area char 25   Yes DEFAULT
commission decimal 8 2 Yes CHECK

the following SQL statement can be used :

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) DEFAULT 'Mumbai',
commission decimal(8,2) 
CHECK(commission>.1 AND commission<.3));
          

To see the structure of the created table :

DESCRIBE mytest; 
Output :

Sql create table insert default values in a column

SQL CREATE TABLE with CHECK CONSTRAINT and IN operator

In the following topic, we have discussed how to use SQL IN operator along with SQL CHECK CONSTRAINT.

The condition for CHECK CONSTRAINT can be defined using any of the basic comparison operator, such as (>, <, =,>=,<=,<>) as well as BETWEEN, IN, LIKE, and NULL operator.

Example :

To include two CHECK CONSTRAINT which are -

1. The first one is on 'working_area' column which ensures that the working_area should be either 'London' or 'Brisban' or 'Chennai' or 'Mumbai',

2. The second one is on 'commission' column which ensures that commission must be less than 1,

in the following table which field name and data types are -

Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6   No UNIQUE
agent_name char 25   No UNIQUE
working_area char 25   No CHECK
commission integer       CHECK

the following SQL statement can be used :

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) CHECK
( working_area IN('London','Brisban','Chennai','Mumbai')) ,
commission decimal CHECK(commission<1)); 
          

To see the structure of the created table :

DESCRIBE mytest; 
Output :

Sql create table with check constraint and IN operator

SQL CREATE TABLE with CHECK CONSTRAINT and LIKE operator

In the following topic, we are going to discuss, how the SQL LIKE operator can be used with CHECK CONSTRAINT.

Example :

To include a CHECK CONSTRAINT on 'ord_date' column which ensures that the format of the 'ord_date' must be like '--/--/----', for example, ('18/05/1998') at the time of creating a table with following field names and data types -

Field Name Data Type Size Decimal Places NULL Constraint
ord_num decimal 6   No UNIQUE
ord_amount decimal 12 2 Yes  
ord_date char 10   No CHECK
cust_code char 6   No  
agent_code char 6   No  

the following SQL statement can be used :

CREATE TABLE mytest(
ord_num decimal(6) NOT NULL UNIQUE ,
ord_amount decimal(12,2) ,
ord_date char(10) NOT NULL CHECK
(ord_date LIKE '--/--/----' ),
cust_code char(6) NOT NULL ,
agent_code char(6) NOT NULL
);    
          

To see the structure of the created table :

DESCRIBE mytest; 
          
Output :

SQL CREATE TABLE with CHECK CONSTRAINT and OR operator

In the following topic, we are discussing about the usage of OR operator along with the CHECK CONSTRAINT.

Example :

To include a CHECK CONSTRAINT on 'commission' and 'working_area' column which ensures that the 'commission' must be less than .20 and 'working_area' must be 'London' at the time of creating the following table which consists the field names and data types -

Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6   No UNIQUE
agent_name char 25   No UNIQUE
working_area char 25   Yes CHECK
commission decimal 8 2 Yes CHECK

the following SQL statement can be used :

CREATE TABLE mytest(
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) ,
commission decimal(8,2) ,
CHECK(commission<.20 OR working_area='London')); 
          

To see the structure of the created table :

DESCRIBE mytest; 
Output :

Sql create table with check constraint and OR operator

SQL CREATE TABLE using CHECK CONSTRAINT and AND, OR operator

In the following topic, we are going to discuss the usage of OR and AND operator along with the CHECK CONSTRAINT. The condition will start to work at the time of inserting the records in the table.

Example :

To include a CHECK CONSTRAINT on 'commission' and 'working_area' column which ensures that -

1. The 'commission' must be less than .14 and 'working_area' must be 'London',

2. or the 'commission' must be less than .15 and 'working_area' must be 'Mumbai',

3. or the 'commission' must be less than .13 and 'working_area' must be 'New York'

at the time of creating the table which fields name and data types are-

Field Name Data Type Size Decimal Places NULL Constraint
agent_code char 6   No UNIQUE
agent_name char 25   No UNIQUE
working_area char 25   Yes CHECK
commission decimal 8 2 Yes CHECK

the following SQL statement can be used :

CREATE TABLE mytest (
agent_code char(6) NOT NULL UNIQUE ,
agent_name char(25) NOT NULL UNIQUE ,
working_area char(25) ,
commission decimal(8,2) ,
CHECK((commission<.14 AND working_area='London')
OR (commission<.15 AND working_area='Mumbai')
OR (commission<.13 AND working_area='New York'))); 
              

To see the structure of the created table :

DESCRIBE mytest; 
Output :

SQL CREATE TABLE using CASCADE

The CASCADE option with ON DELETE allows deleting rows from the child table when the corresponding rows are deleted from the parent table.

The DELETE CASCADE works across a foreign key link and removes the child records associated with the parent records.

Example :

To create a table which contains the following field name and data types -

Field Name Data Type Size Decimal Places NULL Constraint
tranno decimal     No  
company_id varchar 6   Yes FOREIGN KEY
itemcode varchar 10   Yes PRIMARY KEY
coname varchar 35   Yes  
itemname varchar 35   Yes  
iqty integer     Yes  

The table contains a PRIMARY KEY on 'itemcode' and a FOREIGN KEY on 'company_id' column which references to the 'company_id' column of 'company' table.

the following SQL statement can be used :

CREATE TABLE mytest(
tranno decimal NOT NULL,
company_id varchar(6),
itemcode varchar(10),
coname varchar(35),
itemname varchar(35),
iqty integer,
PRIMARY KEY(itemcode),
FOREIGN KEY(company_id)
REFERENCES company (company_id)
ON DELETE CASCADE);

To see the structure of the created table :

DESCRIBE mytest; 
Output :

Sql create table using cascade

Outputs of the said SQL statement shown here is taken by using Oracle Database 10g Express Edition.



Join our Question Answer community to learn and share your programming knowledge.