Types of Constraints

In the Previous post we studied about ‘ What are Constraints ? ‘. Now we will read about different Types of Constraints.

What are the Different Types of Constraints :

constraint1

contraints

1. NOT NULL Constraint :

  • NOT NULL Constraint sets a rule on the column of the table not to accept any Null values. In other words, it mandates the column to have some value always.
  • A column having a Not Null Constraint set on it will not allow the null values but can accept duplicate values.
  • It is a Column level constraint and cannot be defined at table level.

Syntax for Defining Not Null Constraint :

Syntax :

CREATE TABLE  <TABLENAME>
(
<COL 1> <datatype>(size)  NOT NULL ,
<COL N> <datatype>(size)
);
E.g. Create a Table ‘Customer’ having six columns. LOC Column having Not null constraint.
CREATE TABLE CUSTOMER
(
CUST_ID NUMBER(5) ,
PAN  NUMBER(12),
ACCT_NO NUMBER(15),
LOC VARCHAR2(10) NOT NULL,
GENDER CHAR(1),
ADDRESS VARCHAR2(25)
);


2. Primary Key Constraint :

  • Primary Key Constraint is a combination of Unique Constraint and Not Null Constraint. In other words , a primary key has automatically unique and not null constraint defined on it.
  • It sets a rule for a single or a group of columns of the table not to accept any duplicate and  Null values. In other words, it mandates the column or group of columns to have some unique value always.
  • It is both a Column level as well as table level constraint.
  • Primary Key Constraint gives a table its Primary Key which can be a single column or group of column.
  • The same column or group of columns cannot be designated as both Primary Key and Unique Key simultaneously.
  • Each table can have only one primary key which in turn can be composed of single column or group of columns (Composite Primary Key).
  • When Primary Key column is specified for  column or groups of columns. The database engine automatically creates a Unique Index for the Primary Key columns and thus speeds up the accessibility .

Syntax for Defining Primary Key Constraint :

Syntax for Defining Primary Key Constraint on Single Column :

CREATE TABLE  <TABLENAME>
(
<COL 1> <datatype>(size)  PRIMARY KEY ,
<COL N> <datatype>(size)
);
E.g. Create a Table ‘Customer’ having six columns . CUST_ID Column having Primary Key Constraint.
CREATE TABLE CUSTOMER
(
CUST_ID NUMBER(5) PRIMARY KEY ,
PAN  NUMBER(12),
ACCT_NO NUMBER(15),
LOC VARCHAR2(10),
GENDER CHAR(1),
ADDRESS VARCHAR2(25)
);

Syntax for Defining Composite Primary Key i.e. Primary Key Constraint on Multiple Column :

CREATE TABLE  <TABLENAME>
(
<COL 1> <datatype>(size),
<COL2> <datatype>(size),
<COL N> <datatype>(size),
PRIMARY KEY(COL1,COL2)
);
E.g. Create a Table ‘Customer’ having six columns. CUST_ID  and PAN Columns having Primary Key constraint Forming Composite Primary Key.
CREATE TABLE CUSTOMER
(
CUST_ID NUMBER(5) NOT NULL ,
PAN  NUMBER(12),
ACCT_NO NUMBER(15),
LOC VARCHAR2(10),
GENDER CHAR(1),
ADDRESS VARCHAR2(25),
PRIMARY KEY(CUST_ID,PAN)
);


3. Unique Constraint :

  • It sets a rule for a single or a group of columns of the table not to accept any duplicate values.
  • It is both a Column level as well as table level constraint.
  • Unique Key Constraint gives a table its Unique Key which can be a single column or group of column called Composite Unique Key.
  • Unique Key Constraint does not allow duplicate values but can accept Null values. An important point to note is, it can allow multiple null values as one null value is different from the other.

Syntax for Defining Unique Key Constraint :

Syntax for Defining Unique Constraint on Single Column :

Syntax :

CREATE TABLE  <TABLENAME>
(
<COL 1> <datatype>(size)  UNIQUE ,
<COL N> <datatype>(size)
);
E.g. Create a Table ‘Customer’ having six columns . ACCT_NO Column having Unique Key Constraint.
CREATE TABLE CUSTOMER
(
CUST_ID NUMBER(5) ,
PAN  NUMBER(12),
ACCT_NO NUMBER(15) UNIQUE,
LOC VARCHAR2(10),
GENDER CHAR(1),
ADDRESS VARCHAR2(25),
);

Syntax for Defining Unique Constraint on Single Column :

Syntax for Defining Composite Unique Key i.e.Unique Constraint on Multiple Column :

CREATE TABLE  <TABLENAME>
(
<COL 1> <datatype>(size) ,
<COL N> <datatype>(size),
<COL 3> <datatype>(size),
<COL N> <datatype>(size),
UNIQUE (<COL1>,<COL2>)
);
E.g. Create a Table ‘Customer’ having six columns . ACCT_NO Column having Unique Key Constraint.
CREATE TABLE CUSTOMER
(
CUST_ID NUMBER(5) ,
NAME VARCHAR2(30
PAN  NUMBER(12),
ACCT_NO NUMBER(15) UNIQUE,
LOC VARCHAR2(10),
GENDER CHAR(1),
ADDRESS VARCHAR2(25),
UNIQUE (NAME,ACCT_NO

);


 

Syntax for Defining Check Constraint :

Syntax :

CREATE TABLE  <TABLENAME>
(
<COL 1> <datatype>(size)  UNIQUE ,
<COL N> <datatype>(size)
);
E.g. Create a Table ‘Customer’ having six columns . GENDER Column having Check Constraint.
CREATE TABLE CUSTOMER
(
CUST_ID NUMBER(5) ,
PAN  NUMBER(12),
ACCT_NO NUMBER(15) UNIQUE,
LOC VARCHAR2(10),
GENDER CHAR(1) CHECK(GENDER = ‘M’ or GENDER = ‘F’)
ADDRESS VARCHAR2(25),
);

 

Constraints in SQL
Internal Working of Query Execution
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...