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 :
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 :
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 :
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)
);
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 :
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 :
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 :
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),
);
