Constraints in Oracle

Constraints:-

There are 5 types of constraint in oracle which helps us to define certain quality of requirement that the data in the database needs to meet. In other words it can be called as a rule which needs to be met while manipulating database data.

Scenario:-

In above EMPLOYEE table there are 6 columns named as EMP_NO, EMP_NAME, PHONE, SALARY, DEPT_NO and ADDRESS. My requirement is EMP_NO column should not be NULL and it should be UNIQUE. EMP_NAME column must have a name and hence it should not be null. I have table called PHONE_MASTER and PHONE_MASTER also contains PHONE column and I want to insert value in PHONE column of a PHONE_MASTER table if same phone number is present in the EMPLOYEE table in the PHONE column. DEPT_NO column should not contain duplicate values.  I need to check that while inserting data in the EMPLOYEE table, ADDRESS of the employee should not be null.

Considering above requirement I will have to create various constraint on the table and these are discuss in detail below:-

There are 5 types of constraints and are listed below:-

1)    Primary key

2)    Foreign key

3)    Unique key

4)    Not null

5)    Check

Let’s look at the example of each constraint type.

·         Primary key:-

The primary key of a table uniquely identifies each row and ensures that no duplicate rows exist. Therefore, a primary key value cannot be NULL.A table can have at most one primary key, but that key can have multiple columns (i.e., it can be a composite key). To designate a primary key, use the PRIMARY KEY constraint.

You can create primary key constraint while creating a table or you can alter table to add primary key:-

Primary key constraint while creating table:-

CREATE TABLE TABLE_NAME (COLUMN_NAME DATA_TYPE PRIMARY KEY, COLUMN_NAME DATA_TYPE);

Primary key constraint after creating table (composite primary key):-

ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME PRIMARY KEY (COLUMN NAME / COLUMN_LIST);

If you required to drop a constraint then you can drop it using below query:-

·         Foreign key:-

When two tables share one or more common columns, you can use a FOREIGN KEY constraint to enforce referential integrity that is, to ensure that the shared columns always have the same values in both tables. It is also called as referential integrity constraint.

Syntax to create foreign key is:-

You can create foreign key while creating a table or you can add it by altering the table.

CREATE TABLE TABLE_NAME (COLUMN_NAME DATA_TYPE…, CONSTRAINT CONSTRAINT_NAME FOREIGN KEY (COLUMN_NAME) REFERENCES REFERENCE_TABLE_NAME (COLUMN(S)) ;

Foreign key while creating table:-

CREATE TABLE EMPLOYEE (EMPNO NUMBER, DEPT_NO NUMBER PRIMARY KEY);

CREATE TABLE DEPT (DEPT_NO NUMBER, DEPT_NAME VARCHAR2 (10), CONSTRAINT FOR_DEPT_NO FOREIGN KEY (DEPT_NO) REFERENCES EMPLOYEE (DEPT_NO));

Note: - if you would try to create foreign key without having related primary key, will throw error.

Foreign key constraint after creating a table (composite foreign key):-

ALTER TABLE DEPT ADD CONSTRAINT FOR_DEPTNO_CON FOREIGN KEY(DEPT_NO)

REFERENCES EMPLOYEE (DEPT_NO);

You can drop foreign key constraint like:-

·         Unique key:-

Unique key constraint ensures that the column does not have duplicate values in the column. We can create unique constraint on one or multiple columns. The difference between unique key and primary key is that unique key constraint allows NULL values and primary key does not. In regards to create composite unique key constraints you must create it out of line (by altering a table).

Syntax to create unique constraint is:-

You can create unique constraint while creating a table or you can add it by altering table (out of line).

CREATE TABLE TABLE_NAME (COLUMN_NAME DATA_TYPE, COLUMN_NAME DATA_TYPE UNIQUE,);

Unique key while creating a table:-

CREATE TABLE EMPLOYEE (EMPNO NUMBER UNIQUE, EMPNAME VARCHAR2 (10));

Unique key after creating a table (composite unique key):-

ALTER TABLE EMPLOYEE ADD CONSTRAINT CON_UNIQUE (EMPNO, EMPNAME);

You can drop unique key constrain like:-

           

·         Not null:-

Not null constraint will create a rule on a table column which will not allow NULL values to be inserted.

Syntax to create not null constraint is:-

You can create not null constraint while creating table or by altering table:-

CREATE TABLE TABLE_NAME (COLUMN_LIST DATA_TYPE NOT NULL);

Not null constraint while creating a table:-

CREATE TABLE EMPLOYEE (EMPNO NUMBER NOT NULL, EMPNAME VARCHAR2 (10));

Not null constraint after creating a table:-

ALTER TABLE EMPLOYEE MODIFY EMPNO NOT NULL;

Note: - You can combine unique key and not null key to firm a primary key like constraint.

·     
    Check:-

Check constraint enforce a rule on a table column to check for the required value.  If you are required to check a value before inserting into the column you should create check constraint.

Syntax to create check constraint is:-

You can create check constraint while creating a table or by altering the table:-

CREATE TABLE TABLE_NAME (COLUMN_LIST, CONSTRAINT CONSTRAINT_NAME CHECK (SALARY >10000));

Check constraint while creating a table:-

CREATE TABLE EMPLOYEE (EMPNO NUMBER, EMPNAME VARCHAR2 (10), SALARY NUMBER, CONSTRAINT CON_CHECK CHECK SALARY>10000());

               

Check constraint after creating table:-

ALTER TABLE EMPLOYEE ADD CONSTRAINT CONSTRAINT_CHECK CHECK (SALARY <15000);

               

You can drop check constraint like:        

Above all are the examples of constraints in oracle.