Data Constraints in Oracle
What are data constraints:
Data constraints in Oracle or in SQL are the rules or restrictions that are applied on stored data in your database to ensure the data integrity, data accuracy and consistency, data validity. Overall data constraints plays very important role in maintaining data integrity, enforcing rules and relationships among data and improving database performance and security.
ALl the key constraints are applied on the table at the time of table creation by using DDL or you can alter table and add or modify table with primary key.
1. Primary Key Constraint:
The primary key constraint helps to identify each row unique. It specifies a column or group of columns that are uniquely identified. It ensures to maintain each column unique and Non null values. After applying the primary key on the table each row in a table will be uniquely identified.
For example,
In the employee table emp_id cannot be duplicated, it must be uniquely identified. So if you have applied the primary key on emp_id in the employee table then all rows of emp_id will be uniquely identified.
There are two ways to use key data constraints on your table
1. Column Level Primary Key Constraints:
The column level key constraint is nothing but you apply the primary key on your table for a particular column at the time of column definition directly.
Syntax to use Primary Key:
Create table table_name
( column1 datatype(size) Primary Key,
Column2 datatype(size),
…….
…….
Column datatype(size));
The above syntax demonstrates the primary key constraints at column level.
At the time of column definition the primary key is defined with the column.
For example,
SQL> create table student
2 (rollno number(4) primary key,
3 name varchar(20),
4 address varchar(30));
Table created.
SQL> desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(4)
NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
In above example,
The primary key is defined to rollno column immediately after defining rollno column. This is called column level constraint.
2. Table Level Primary KEy Constraint:
In this we first define all the columns of table and then define the primary key with its key constraint to the columns.
Syntax:
Create table table name
( columnname datatype(size),
Columnname datatype(size),
………………………….. ,
…………………………. ,
Constraint key_constraint Primary Key ( columnname, columnname));
In the above table create a table with defining its columns. Here the constraint keyword is used to define the constraint. Then define the constraint name for your constraint and then use the primary key keyword to define the primary key to your columns. Write all the columns in a simple pair of parentheses.
The constraint name is the name given by the user. You can give any name.
In oracle table can have only one primary key which is used to uniquely identify the columns and maintain non nullability. But if you want to define multiple columns as a primary key then you can create composite key by combining multiple columns into a single primary key.
In above table level syntax the composite key i.e. multiple columns are combined to apply a primary key. Hence table is uniquely identified.
Example,
SQL> create table student
2 (rollno varchar(8),
3 name varchar(20),
4 constraint pk_student primary key (rollno));
Table created.
SQL> desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL VARCHAR2(8)
NAME VARCHAR2(20)
Here in this example pk_student is a constraint defined to the table student.
Constraint keyword is used to define the constraint and the primary key is the name of the data constraint used to define the primary key to column rollno in the student table. This is table level constraint.
How to Add Key Constraint to Existing Table:
You can alter your table to add the primary key constraint to a particular
column using the alter modify query.
Syntax:
Alter table table_name
Modify columnname datatype(size) Primary Key;
In above syntax,
Alter table keywords are used to alter table. In place of table_name write
the actual table name that you want to alter with the primary key.
Use Modify keyword to make changes on your columns, replace column
name with your columnname. If you want to make changes in data type
and size you can make or you can go with the same data type and size and
then use Primary Key keyword to add primary key to your column.
For Example,
SQL>alter table students
modify rollno number(6) Primary Key;
Table altered.
SQL> desc students
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(6)
NAME VARCHAR2(30)
In the above example, the student's table is modified with the primary key.
Here data type and size of rollno column is not changed, only the primary
key is added to the rollno column. If you want to change or modify the
datatype and size of the column you can change it in the same query.
You can also add primary key to your existing table by using alter add query
as follows,
Syntax:
Alter table table_name
Add constraint constraint_name Primary Key ( columnname);
In above syntax the table is altered to add a primary key to one or more
columns in the table. Add keyword is used to add the primary key constraint
to a column or more columns. Constraint_name is the name of a constraint
defined by the programmer to create the constraint. Then Primary Key
keyword is used to add the primary key to a column or columns.
Columnname1, columnname2 are the names of columns you have to
replace with actual column names of your table to which you want to
add the primary key.
For example,
SQL> alter table employee
add constraint pk_employee primary key (emp_id);
Table altered.
SQL> desc employee
Name Null? Type
------------------------- -------- ----------------------------
EMP_ID NOT NULL VARCHAR2(6)
EMP_NAME VARCHAR2(20)
DESIGNATION VARCHAR2(15)
SALARY NUMBER(10)
DOJ VARCHAR2(12)
EMAIL VARCHAR2(15)
E_ADDRESS VARCHAR2(25)
DOB VARCHAR2(20)
DOR VARCHAR2(20)
CONTACT NUMBER(10)
O_ADDRESS VARCHAR2(25)
Drop Primary Key Constraint:
You can drop the primary key by altering your table.
Syntax:
Alter Table tablename
Drop primary key;
Example
SQL> alter table student
2 drop primary key;
Table altered.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO VARCHAR2(6)
NAME NOT NULL VARCHAR2(12)
CLASS VARCHAR2(10)
DIV VARCHAR2(10)
The primary key which was assigned to the rollno column is dropped with an
alter drop query.
How to Drop Primary Key data constraint which is created at table level:
Syntax:
Alter table tablename
Drop constraint constraint_name;
In above syntax the drop constraint keyword is used to drop the constraint.
Constraint_name is the name of a constraint created for the primary key.
Table level constraint can be given to composite primary keys. I.e. more
than one primary key combinely.
In the below example, the first primary key constraint is created at table level.
The constraint name is prollno and the primary key is applied to the rollno
column.Constraint name is user defined name. You can give any name for user
defined constraints.
For Example,
SQL> alter table student
2 add constraint prollno primary key(rollno);
Table altered.
SQL> desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL VARCHAR2(6)
NAME NOT NULL VARCHAR2(12)
CLASS VARCHAR2(10)
DIV VARCHAR2(10)
SQL> alter table student
2 drop constraint prollno;
Table altered.
SQL> desc student
Name Null? Type
----------------------------------------- - ------- ----------------------------
ROLLNO VARCHAR2(6)
NAME NOT NULL VARCHAR2(12)
CLASS VARCHAR2(10)
DIV VARCHAR2(10)
In the above table structure you can see that the RollNo column now does
not contain Not Null. Here the primary key is removed from the student table.
2. Not Null Constraint:
Not Null constraint is used to ensure that the column should contain the value
and cannot be null or empty. Not null constraint ensures that column cannot be
null. Identify the columns in a table that you cannot keep empty. When you
apply Not Null constraint database engine will not allow you to keep column
empty.
Syntax:
Create table tablename
(columnname datatype(size) not null,
Columnname datatype(size) not null,
………………………………….. ,
);
Create table with create table command define column with its data type and
then apply Not Null constraint to your column. As per requirement apply Not
Null constraint for one or more than one column in your table.
For example,
SQL> create table student
2 (rollno varchar(8) Primary Key,
3 name varchar(20) not null,
4 address varchar(30) not null);
Table created.
SQL> desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL VARCHAR2(8)
NAME NOT NULL VARCHAR2(20)
ADDRESS NOT NULL VARCHAR2(30)
How To Apply Not Null Constraint on Existing Table:
To apply Not Null constraint on your existing table alter your table using alter
table query by adding Not Null constraint.
Syntax:
Alter Table Tablename
Modify( columnname Not Null,
Columnname Not Null);
In the above syntax tablename you have to replace it with your actual table
name. Use modify keyword to modify your column with the not null constraint.
You can modify one or more than one column in your table with Not Null
constraint.
For Example,
SQL> alter table student
2 modify( contact Not Null,
3 email Not Null);
Table altered.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL VARCHAR2(8)
NAME NOT NULL VARCHAR2(20)
ADDRESS NOT NULL VARCHAR2(30)
CONTACT NOT NULL NUMBER(10)
EMAIL NOT NULL VARCHAR2(20)
Alter Table to add new column and Not Null constraint to one or
more columns.
SQL> alter table student
2 add (class varchar(20) Not Null,
3 div varchar(6) Not Null);
Table altered.
SQL> desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL VARCHAR2(8)
NAME NOT NULL VARCHAR2(20)
ADDRESS NOT NULL VARCHAR2(30)
CLASS NOT NULL VARCHAR2(20)
DIV NOT NULL VARCHAR2(6)
CLASS NOT NULL VARCHAR2(20)
DIV NOT NULL VARCHAR2(6)
In the above example the student table is altered with a Not Null constraint.
Table is altered with two new columns class and div and the Not Null
constraint is applied to these two columns. So here a new column added as
well as the Not Null constraint is applied to the table.
Remember Not Null constraint cannot be applied at table level. You can apply
a Not Null constraint at column level immediately after defining each column.
How to Drop Not Null Constraint:
To drop Not Null constraint in Oracle you can modify your table to keep it null.
Syntax:
Alter table tablename
Modify columnname Null;
For example,
SQL> alter table student
2 modify( s_rollno Null, S_name Null);
Table altered.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
S_ROLLNO VARCHAR2(5)
S_NAME VARCHAR2(15)
S_CLASS NOT NULL VARCHAR2(6)
S_DIV NOT NULL VARCHAR2(10)
PRN NUMBER(5)
3. Unique Constraint:
The unique constraint is used to enforce uniqueness to one or more columns
in a table.
Difference between Primary Key and Unique constraint:
Purpose:
Primary Key:
Primary key is used to uniquely identify each row in the table. It also ensures
that the values specified are unique and not null.
Unique Key:
The unique data constraint enforces the values specified to the columns are
unique but it does not enforce non nullability of values.
2. Number of Columns:
In a table primary can be applied for one or more columns. But in oracle more
than one column primary key constraint is defined at table level by
creating composite primary keys. A table can have only one primary key.
Unique constraints can be defined for more than one column in a table.
A table can have multiple unique constraints.
3. Null Values:
Primary key constraint ensures the non nullabilty, i.e. it does not allow to enter
null values to the primary key column.
Unique constraint allows the null values to be part of a unique constraint column.
It does not maintain non nullability.
Referential Integrity:
Primary key helps to maintain referential integrity with the help of foreign key.
Unique constraint does not maintain referential integrity. You can not maintain
relationships between more than one table using unique constraints.
Syntax:
Column Level Unique Constraint:
Create table tablename
(columnname datatype(size) unique,
Columnname datatype(size) unique,
…………………………………….. ,
……………………………………. );
In the above syntax unique constraint is defined immediately after defining the
columns of the table. Use a unique keyword to define a unique constraint to the
table.
For Example
SQL> Create table student
2 (s_rollno varchar(6) unique,
3 S_name varchar(15) Not Null,
4 S_class varchar(10) Not Null,
5 S_div varchar(10) Not Null );
Table created.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
S_ROLLNO VARCHAR2(6)
S_NAME NOT NULL VARCHAR2(15)
S_CLASS NOT NULL VARCHAR2(10)
S_DIV NOT NULL VARCHAR2(10)
In the above structure you can see the S_rollno column has null. Column is
defined with a unique constraint so it ensures to maintain uniqueness but can
not maintain non nullability.
Table Level Unique Constraint:
Syntax:
Create table tablename
( columnname datatype(size),
Columnname datatype(size),
……………………………… ,
………………………………
Constraint constraint_name unique(columnname, columnname));
In this syntax unique constraint is defined to columns in the table at table
level by creating constraint_name. Constraint_name is the user defined
constraint. By using a constraint keyword followed by the constraint_name
with the help of a unique keyword the unique constraint is defined with multiple
columns.
For example,
SQL> create table student
2 (s_rollno varchar(5) Not null,
3 s_name varchar(15) Not null,
4 s_class varchar(6) Not Null,
5 s_div varchar(10) Not Null,
6 constraint uk_student unique(s_rollno));
Table created.
In the above table student for all the columns the Not Null constraint is
defined and Unique constraint is applied to S_rollno. This is table level
constraint where a unique constraint is applied with the constraint name
uk_student. This is a user defined constraint name.
SQL> desc student;
Name Null? Type
----------------------------------------- -------- ----------------------------
S_ROLLNO NOT NULL VARCHAR2(5)
S_NAME NOT NULL VARCHAR2(15)
S_CLASS NOT NULL VARCHAR2(6)
S_DIV NOT NULL VARCHAR2(10)
How Add unique constraint in existing Table:
Syntax:
Alter table tablename
Modify columnname unique;
Example,
SQL> alter table student
2 modify s_rollno unique;
Table altered.
In this example assume that the s_rollno column has no unique constraint and
by using the above query you can modify your table to define a unique constraint
to the s_rollno column.
Syntax To add new column as well as unique constraint to your table,
SQL> alter table student
2 add prn number(5) unique;
Table altered.
Here the prn column is added to the student column and a unique constraint
is also defined to the student table.
Drop Unique Constraint:
Unique constraints cannot be dropped individually. It cannot be directly
dropped. So in future if you want to drop the unique constraint then create the
constraint at table level by creating user defined unique constraint. Now you will
be able to drop the unique constraint from your table.
Syntax:
Alter table tablename
Drop constraint constraint_name;
For example,
SQL> alter table student
2 drop constraint uk_student;
In the above table the unique constraint is dropped by using an alter table
and the user defined constraint name uk_student is dropped. So if you have
defined a unique constraint for more than one column then by using the above
query the unique constraint will be deleted for all the columns in your table.
4. Default Constraint:
Default constraint is used in cases where you do not specify the value for
your column. Default constraint ensures that when no value is inserted to a
column the default value is taken. The default constraint is used to specify the
default value to the column at the time of new row insertion when the value is
explicitly not inserted by the user.
Column Level Constraint:
Syntax:
Create table tablename
(columnname datatype(size) default default_value,
Columnname datatype(size) default default_value,
………………………………………………….. );
Create table with create command, define column name with its data type
and size then use Default keyword to define default constraint on your column,
default_value is the value you have to assign to take that value by default in
case where you do not specify the value to column.
For example,
SQL> create table result
2 (s_rollno number(4) primary key,
3 s_subject varchar(10) not null,
4 s_internal_marks char default 'N',
5 s_external_marks char default 'N',
6 s_total_marks number(4) not null);
Table created.
SQL> desc result;
Name Null? Type
----------------------------------------- -------- ----------------------------
S_ROLLNO NOT NULL NUMBER(4)
S_SUBJECT NOT NULL VARCHAR2(10)
S_INTERNAL_MARKS CHAR(1)
S_EXTERNAL_MARKS CHAR(1)
S_TOTAL_MARKS NOT NULL NUMBER(4)
Insert information to your table to take default values:
SQL> insert into result
2 values(1, 'DBMS',default, default, 200);
1 row created.
SQL> select * from result;
S_ROLLNO S_SUBJECT S S S_ TOTAL_MARKS
---------- ---------- - - -------------
1 DBMS N N 200
Table Level Constraint:
In Oracle you cannot define default constraints at table level at the time of
creation. At column level constraint you can give default constraints for more
than one column in your table.
In Your existing table if you want to add default constraint to a column or
multiple columns you can apply an altered query on your table.
Modify Table with Default Constraint:
SQL> alter table student
2 modify( class default 'BCA', div default 'A');
Table altered.
Insert one row to your table by using default value as follows,
SQL> insert into student
2 values('BCA01', 'Manisha',default,default);
1 row created.
SQL> select * from student;
ROLLNO NAME CLASS DIV
------ ------------ ---------- ----------
BCA01 Manisha BCA A
Here at the time of row insertion to student table class and div columns
are assigned with default value BCA and A respectively.
How to Drop Default Constraint:
Oracle Alter table query does not provide the drop option to drop default
constraint from your table. You can drop default constraint as follows,
Syntax:
Alter table tablename
Modify columnname Default Null;
In above syntax the tablename is the name of the table you want to drop
the default constraint. Modify keyword is used to modify the column and it is
followed by default keyword and now default value Null is assigned to column.
Null is the keyword. Now Whenever you don't fill a row for your column it will
take null means nothing.
For example,
SQL> ALTER TABLE student
2 modify class default null;
Table altered.
SQL> insert into student
2 values('BCA02','Seeta', default, default);
1 row created.
SQL> select * from student;
ROLLNO NAME CLASS DIV
------ ------------ ---------- ----------
BCA01 Manisha BCA A
BCA02 Seeta A
In the above example one row is inserted to the student table.
For class and div columns default value is assigned.
But we have modified our table for the class column and now the class
column contains Null. So it has not taken the previous value ‘BCA’ as a
default because we have modified it with Null means nothing.
In this way you can remove the default constraint.
5. Check Constraint:
Check constraint in Oracle is used to enforce the condition or rule on values in a column or more than one column. This is the condition you can apply on a column or columns in a table to enforce the value that is being inserted to table or updated from table.
Column Level Check Constraint:
Syntax:
Create Table Tablename
(columnname datatype(size) check (condition),
Columnname datatype(size) check(condition),
……………………………………………….. ,
……………………………………………….. );
Create table with Create table query. Replace tablename with your
tablename. Define columns with data type and size and use check
keyword and in simple bracket write condition or rule or value that
you want to enforce with column.
Example,
SQL> create table orderdetails
2 (order_id varchar(6) primary key,
3 order_date date default sysdate,
4 quantity number(4) check (quantity >=10),
5 amount number(4) check (amount>=0));
Table created.
SQL> desc orderdetails
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_ID NOT NULL VARCHAR2(6)
ORDER_DATE DATE
QUANTITY NUMBER(4)
AMOUNT NUMBER(4)
In the above query orderdetails table is created with order_id as primary key,
Order_date as date column and default system date value, for quantity and
amount columns check condition is applied. If quantity is less than or equals
to 10 then it is not allowed, if amount is less than 0 then it is not allowed.
In this way check constraint is applied on quantity and amount columns to
enforce the values.
Insert one row in orderdetails table to check how to check constraint work,
SQL> insert into orderdetails
2 values('001',default, 4, 0);
insert into orderdetails
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.SYS_C008366) violated
Here you can see that for quantity column 4 is inserted and the check
condition is greater than 10 so it is violated.
In Below query the violation is removed by using the quantity value greater
than 10.
SQL> insert into orderdetails 2 values('001',default, 12, 100); 1 row created. SQL> select * from orderdetails; ORDER_ ORDER_DAT QUANTITY AMOUNT ------ --------- ---------- ---------- 001 12-JUL-23 12 100
Another Row inserted
SQL> insert into orderdetails
2 values('05','13-july-2023',13,200);
1 row created.
SQL> insert into orderdetails
2 values('06','05-july-2023',13,200);
Display all rows in orderdetails table,
SQL> select * from orderdetails;
ORDER_ ORDER_DAT QUANTITY AMOUNT
------ --------- ---------- ----------
05 13-JUL-23 13 200
06 05-JUL-23 13 200
Foreign Key Data Constraint:
The Foreign Key data constraint in Oracle is used to maintain relationships between two tables.
It helps to maintain data integrity, data consistency by enforcing referential integrity.
The Foreign Key in one table refers to the Primary Key in another table and creates a parent child relationship between two tables. This data constraint helps in preventing data inconsistency.
The Foreign Key ensures that the data in one table i.e. child table(Foreign Key table) always corresponds to valid data in another table i.e. parent table(Primary Key table).
Syntax:
Table Level Foreign Key Constraint:
Create Table Tablename
(Columnname datatype(Size),
Columnname datatype(size),
……………………………… ,
Constraint constraint_name Foreign Key(Columnname)
References Parent_table(columnname));
Create a table with your table name, define all columns of your child table, then define the column from parent table which is a primary key column and through which you want to create the relationship between child table and parent table. Use constraint keyword to create user defined constraint name which will be followed by the Foreign KEy keyword and parentheses include the name of the primary key column from the parent table, then use References Keyword to to give the reference of the primary key table(parent table) followed by parent table name and primary key column.
In the following We want to make the relationship between student and result table. So the first student table is created and then this table is referred to in the result table with Foreign Key Constraint.
Example,
SQL> create table student
2 (rollno number(2) primary key,
3 name varchar(10) not null,
4 class varchar(10) not null,
5 div varchar(6) not null);
Table created.
SQL> desc student
Name Null? Type
----------------------------------------- -------- ----------------------------
ROLLNO NOT NULL NUMBER(2)
NAME NOT NULL VARCHAR2(10)
CLASS NOT NULL VARCHAR2(10)
DIV NOT NULL VARCHAR2(6)
Here first create Primary KEy table i.e. Parent table. It is also called a referenced table where the rollno column has assigned primary key. This Primary KEy column is used to create the relationship between students and another table is the Result table with the help of referential integrity constraint Foreign Key as follows,
SQL> create table result
2 (prn number primary key,
3 rollno number,
4 constraint fk_roll Foreign Key(rollno)
5 References student(rollno));
Table created.
In the above query result table is created by defining the column of result table and then rollno column from student table(parent table) is also defined. It is not defined with the primary key again. Constraint keyword is used to define the user defined constraint, here fk_roll is the user defined constraint name, which is followed by Foreign KEy keyword and the primary key column from the student table. The References Keyword is used to give the reference of the student table in the result table through primary key column rollno.
SQL> desc result
Name Null? Type
----------------------------------------- -------- ----------------------------
PRN NOT NULL NUMBER
ROLLNO NUMBER
Insert Data to Result Table:
SQL> insert into student
2 values(01,'abc','bca','A');
1 row created.
SQL> insert into result
2 values(0111,2);
insert into result
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.FK_ROLL) violated - parent key not found
While inserting rows to the child table when you have made a relationship between two tables, always remember that the data that you enter in the child table for the primary key column should match with that of the data in the parent table for the same column.
In the above insertion query the prn value is 0111 and rollno value is 2 but this rollno 2 is not present in the primary key table(parent table). So the system has generated a data violation error- parent key not found.
In the following insertion query we have avoided the data violation error.
SQL> insert into result
2 values(0111,01);
1 row created.
SQL> select * from result;
PRN ROLLNO
---------- ----------
111 1
SQL> select * from student
2 ;
ROLLNO NAME CLASS DIV
---------- ---------- ---------- ------
1 abc bca A
Column Level Foreign Key Data Constraint:
Syntax:
Create Table Tablename
(colimnname datatype(size),
……………………………….. ,
columnname Datatype References parent_table(columnname),
………………………………………………… ,
);
For Example,
First created Parent Table Orders.
SQL> create table orders
2 (order_no number primary key,
3 order_date date);
Table created.
SQL> desc orders
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_NO NOT NULL NUMBER
ORDER_DATE DATE
Then created Child Table Order_details
SQL> create table order_details
2 (order_no number References orders,
3 order_type varchar(15) not null,
4 order_price number(3));
Table created.
SQL> desc order_details;
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDER_NO NUMBER
ORDER_TYPE NOT NULL VARCHAR2(15)
ORDER_PRICE NUMBER(3)
Here Orders is Parent table in which Order_no is a Primary Key column. Through this column the relationship is created between Orders and Order_details table with the help of Foreign Key the Referential Integrity Data constraint.
Important Points To be Remember:
1. The Foreign Key data constraint helps to maintain referential integrity i.e. to maintain relationships between two or more than two tables.
2. While inserting data to the child table the referenced column data should match with the primary key column in the parent table or primary key table.
3. The Referential Integrity does not directly enforce the Non Nullability on the Foreign Key column. If you want to maintain Non Nullabilty you can use the Not Null data constraint at the time of Foreign KEy Column definition in the Child table.
0 टिप्पण्या
कृपया तुमच्या प्रियजनांना लेख शेअर करा आणि तुमचा अभिप्राय जरूर नोंदवा. 🙏 🙏