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:-
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.
Above all are the examples of constraints in oracle.
