Oracle Constraints

Constraint is a rule imposed on tables to restrict the values stored in. Oracle has five different type of constraints. We will examine each one with suitable examples.

1. NOT Null Constraint

NOT NULL constraint checks the columns to be populated with non-null values.

Example :-

Create table person
(
pno   integer,
pname varchar2(20) not null
);

pname column has a not null constraint.

SQL> desc person;
Name                                      Null?    Type
—————————————– ——– ————
PNO                                                NUMBER(38)
PNAME                    NOT NULL VARCHAR2(20)

Below insert statement will validate the above not null constraints

SQL> insert into person values(1,null);
insert into person values(1,null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“PERSON”.”PNAME”)

Where to Use not null constraint

Situations when column needs to populated always can use NOT NULL constraint.
For example for EMP table, empname column must be not null, but for salary it may not be.

2. UNIQUE Constraint

UNIQUE Constraint imposes each column will have distinct value within the table. Not that UNIQUE Constraint column can have null values. Null values are not considered for uniqueness. UNIQUE Constraint can be applied to composite(Two or more columns) keys also.

Example :-

Create table person
(
pno   integer,
pname varchar2(20) constraint uq_pname unique
);

SQL> insert into person values(1,’Bill’);

1 row created.

SQL> insert into person values(2,’Bill’);
insert into person values(2,’Bill’)
*
ERROR at line 1:
ORA-00001: unique constraint (UQ_PNAME) violated

When we are trying to insert PERSON table with duplicate pname it will raise an error. UNIQUE constraint column can accept null values. Not that Oracle creates a unique index automatically when UNIQUE constraint assigned to a column.

Where to use unique constraint

Unique constraints can be assigned to columns those have unique values or no values.

3. Primary Key Constraint

A primary key constraint combines a NOT NULL constraint and a UNIQUE constraint. A table can have only one PRIMARY KEY and it can be created for composite(Two or more columns) keys also.

Example :-

Create table person
(
pno   integer constraint pk_pno PRIMARY KEY,
pname varchar2(20)
);

Where to Use PRIMARY KEY constraint

Generally every table should have a PRIMARY KEY (If you are normalizing the design).

4. Foreign Key Constraint

Foreign Key Constraint used to relate two or more table and values in one table to match values in another table.

CREATE TABLE DEPT
(
DEPTNO   INTEGER PRIMARY KEY,
DEPTNAME VARCHAR2(20) NOT NULL,
LOCATION VARCHAR2(20)
);

CREATE TABLE EMP
(
EMPNO    INTEGER PRIMARY KEY,
EMPNAME  VARCHAR2(20) NOT NULL,
SALARY   NUMBER,
DEPTNO   INTEGER constraint fk_deptno references DEPT(deptno)
);

Where to Use FOREIGN KEY constraint

If you want to maintain a PARENT-CHILD relationship FOREIGN KEY constraints are ideal.

5. CHECK Constraint

CHECK Constraints are enforcing certain types of values in a column.

Create table person
(
pno    integer,
pname  varchar2(20),
status varchar2(20) constraint ch_status check( status in (‘ACTIVE’,’INACTIVE’))
);

Here status column have only two value, ‘ACTIVE’ or ‘INACTIVE’

Conclusion : – Oracle constraints are integral part of table. Constraints help us to maintain the integrity of the database. Also helps to reduce the redundant data.

Next :- Oracle Constraint Examples

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

3 thoughts on “Oracle Constraints”

  1. constraint is used to stop the invalid transaction….it enforces the buisness rule of database…
    constraints are:-entity integrity constraint
    domain integrity constraint
    referential integrity constraint
    user defined integrity constraint

  2. I have all constraints in 1 table i copied the table to another table.. is the constraints will be there on new table or not?

    Answer : only not null Constraint is there and rest of the Constraints are deleted/dropped.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s