Constraints:-

●​ constraints are used to specify rules for data in a table.

●​ constraints are used to limit the type of data that can go into the table.
●​ Constraint can be specified when the table is created with a CREATE
TABLE statement or after the table is created with an ALTER TABLE
statement.

constraints main type

1) Domain Integrity constraint
a) Not null
b) Default
c) Check

2) Entity Integrity constraints

a) Unique
b) primary

3) Referential Integrity constraint

a) Foreign key

Where we can set constraints

1) column level
2) Table level
3) After table creation

=====================================================================
1) Not null :-
If you want to make sure that field gets some value for every row in the
table,apply the not null constraint to it.

Column level constraint:-

-​ Create a Student Table.

Syntax:
Create table Table_Name(
Column_Name1 DataType Constraint Type,
Column_Name2 DataType …);

create table student (

id number(10) not null,
name varchar2(10));

-​ Insert Data into Table

insert into student values (1,'Deep');
insert into student values (2,'Anil');
commit;

select constraint_name,search_condition,status,last_change
from all_constraints where table_name='STUDENT';
 -​ Constraint_Name=’SYS_C0007547’ (It's a system generated
constraint name because we didn’t specify constraint name while
table creation.
Please Note: - System generated constraint name always start with
SYS_C )

-​ Search Condition= ‘ID” is not null , Here we have set a not null
constraint on the ID column.

-​ Status = Enabled means constraint has been set.

-​ Last Change = Its a data of constraint creation or constraint

modification.

-​ Drop the Non null constraint:

Alter table student
Drop constraint SYS_C0012111;

-​ Drop student table

Drop table student;

Best method of column level constraint with user defined name

create table student (

id number(10) constraint id_notnull not null,
name varchar2(10));
Observation: Here we gave the user a defined name “ID_NOTNULL”
while creating a table.

Why can't we assign a not null constraint as table-level?

-​ NOTNULL is a column-level constraint to ensure that any value in that column is

not null; hence, it can’t be used as a table-level constraint.
-​ We can set a not null constraint on multiple columns as per the need. Also, it can
be applied on the table level using the ALTER command.

-​ Drop student table

Drop table student;

Set Constraint after Table Creation

create table student (
id number(10),
name varchar2(10));
If we don’t want to specify a constraint name then just provide the
constraint type.

Alter table student modify id not null;

We didn’t specify the constraint name then it was created system
generated constraint name SYS_C007549

-​ Drop the constraint

Alter table student
Drop constraint SYS_C007549;

After Drop the constraint you will not find any result from All_constraints
view.
If you want to specify user define constraint name after Table creation
Alter table student modify (id constraint id_nn1 not null );

Here if you see the constraint name ID_NN1 which we defined.

insert into student values (null,'Ram');

If you try to insert a null value into the id column it will populate error as
given above.
Note: Please Drop the Student Table.
=====================================================================

2) Default:-

A default constraint used to provide the default value for a column if we

didn’t specify any value in the column.

-​ Create a Table.

create table student (id number(10),

city varchar2(10) default 'Pune');

-​ Insert values in table

insert into student (id,city) values (1,'Goa');
insert into student (id,city) values (2,null);
insert into student (id,city) values (3,’null’);
insert into student (id,city) values (4,'default');
insert into student (id,city) values (5,default);
insert into student (id) values (6);
commit;

Kindly Note: Please observe the table display result.

select column_name,data_default from dba_tab_columns where
table_name='STUDENT';

If you observed we have created a default constraint on the city column, we

mentioned the default city is Pune.
Note Please : dba_tab_columns view is available in sys user only if you
check in normal user it will give error like ORA-00942: table or view does
not exist

How to drop default constraint;

ALTER TABLE student MODIFY city DEFAULT NULL;

How to add default constraint using alter command;

alter table student modify (city default 'Pune');

=====================================================================

3) Check constraint:

● Check constraint is used to limit the value range that can be placed in a
column.
● If you define a check constraint on a single column it allows only certain
values for this column. ​

-​ Create a table sal_check

create table sal_check(id number(10),
name varchar2(20),
sal number(10) constraint chk_salary check (sal in (1000,5000,10000)) );

select
constraint_name,constraint_type,search_condition,status,last_change
from all_constraints where table_name='SAL_CHECK';

Observation:
chk_salary is a user-defined constraint name.
Constrain Type ‘C’ means check constraint.
Search condition means the check constraint condition.

We will insert the salary as mentioned in the check constraint.

insert into sal_check values (1,'Dipak',1000);

insert into sal_check values (2,'Urnil',5000);
insert into sal_check values (3,'Chetna',10000);
commit;

select * from sal_check;

It allowed us to insert the salaries as per given Range by check constraint.

Lets try to insert the salary which is not mentioned in the check constraint
Range.

insert into sal_check values (4,'Dipak',2000);

Please note: It is not allowed to insert the salary 2000 because we
restricted insertion for other values except 1000,5000 and 10,000.
Please observe the above error.

– Create Table to set a Range of Insertion

create table emp_check(id number(10),ename varchar2(10),sal

number(10)
,constraint chk_sal check (sal>1000));
We’ve inserted the salary 2000 because specified condition on sal column
if salary is greater than 1000 it will allowed to insert and if salary will be less
than 1000 then it will give error.
- How to Drop a Constraint?
Alter table sal_check drop constraint chk_sal;

After dropped constraint

Select
constraint_name,constraint_type,search_condition,status,last_change
from all_constraints where table_name='EMP_CHECK';
– Set constraints after table creation.

Alter table emp_check

Add constraint chk_sal check(sal > 10000);

Insert into emp_check (id,ename,sal) values (1,'Dipak',15000);

Select * from emp_check;

Note: We have restricted the sal column for insertion, It will allow insertion
whenever sal will be greater than 10,000 else it will give constraint
violation error.
=====================================================================
4) Unique Constraint:
●​ Unique constraint ensures all the values in a column are different.
●​ This means no two rows can have the same non-NULL value(s) for the
column(s) included in the unique constraint.
●​ We can have many unique constraints per table
●​ Can be defined on a single column or multiple columns (composite
unique key):
●​ Single column: Ensures uniqueness for that specific column.
●​ Composite unique key: Ensures the combination of values in the
specified columns is unique across all rows.

Unique Constraint can be added at column level or table level:

●​ Column-level: Defined directly within the column definition during
table creation or alteration.
●​ Table-level: Defined as a separate clause within the CREATE TABLE
or ALTER TABLE statement, allowing for composite unique keys.

i) Column level constraint :-

create table unique_test (ID number(10) unique,

Name varchar2(10));

Drop Table unique_test;

create table unique_test (ID number(10) constraint unique_id
Unique,Name varchar2(10));

Please Note: Unique constraint allows multiple null values.

If we try to insert the ID which is already present in the table, it will give an
error like Unique constraint Violated.

ii) Table level constraint :-

Drop Table unique_test;
Create table unique_test (id number(10) ,name varchar2(10),
constraint unique_id unique (id));

Find Constraint Details:

select constraint_name,constraint_type,search_condition,last_change
from all_constraints
where table_name='UNIQUE_TEST';

Drop constraint:
Alter table unique_test Drop constraint unique_id;

iii) After Table creation (Using alter table method)

Alter table unique_test Add constraint unique_id unique (id);

How to Create Table-level composite unique constraint?

create table unique_test (ID number(10),Name varchar2(10),
constraint unique_id Unique(id,name));
 -​ Insertion in Table
insert into unique_test(id,name) values (1,'Dipak');
insert into unique_test(id,name) values (2,'Chetna');
insert into unique_test(id,name) values (2,'Kavya');
insert into unique_test(id,name) values (3,'Harshal');
insert into unique_test(id,name) values (1,'Dipak');

Please note Unique Key implicitly creates a unique index:​

When a unique constraint is defined, Oracle automatically creates a unique
index on the constrained column(s) to enforce the uniqueness and improve
data retrieval performance.
We will prove the claim for the above statement that when a unique key is
created, it automatically creates a unique index.

select Index_name,uniqueness
from all_indexes where table_name='UNIQUE_TEST';

=====================================================================
5) Primary Key:- It is the combination of Not null and Unique key.
The PRIMARY KEY constraint gives each table row a unique identity. It
upholds two important regulations:

Uniqueness:​
All values in the primary key column(s) must be unique. No two rows can
have the same primary key value.

Not Null:​
The primary key column(s) cannot contain NULL values. A value must
always be present.
​

IMP Please note 🙂: There's only one "primary key constraint" per
table,either it can be Primary key or Composite Primary key.

There are 2 types of Primary Keys

i) Single Column Primary Key :- Single column
ii) Composite Primary key :- (More than one column)

i) Single Column Primary Key :- Simple Primary Key.

A single column in the table is designated as the primary key. This column
must contain unique values for each row and cannot contain any null
values.

Column Level Primary key constraint with default name

create table emp_pk (id number(5) primary key,

name varchar2(10),
sal number(10));
Find the Constraint Details.

select * from USER_CONSTRAINTS WHERE table_name = 'EMP_PK';

select * from USER_CONS_COLUMNS WHERE table_name = 'EMP_PK';
select * from ALL_CONSTRAINTS WHERE table_name = 'EMP_PK';

How to get the column name ,constraint name, and type of constraint.
select a.column_name,b.constraint_name,b.constraint_type
from USER_CONS_COLUMNS a,ALL_CONSTRAINTS b
where a.Table_name=b.Table_name and b.Table_name='EMP_PK';

Drop the Table.

Drop table emp_pk;

Column Level Primary key constraint with user defined name.

create table emp_pk (id number(5) constraint pk_eid primary key,

name varchar2(10),
sal number(10));

Drop the Table

Drop table emp_pk;

Table level Primary key constraint with user defined name.

create table emp_pk (id number(5) ,

name varchar2(10),
sal number(10),
constraint pk_eid primary key (id));
How to drop constraints ?
Alter Table emp_pk drop constraint pk_eid;

Add constraints using the Alter table statement after table creation.
Alter Table emp_pk add constraint pk_eid primary key(id);

-​ Insert Data into table

insert into emp_pk (id,name,sal) values (1,'Dipak',10000);

insert into emp_pk (id,name,sal) values (2,'Jatin',20000);
insert into emp_pk (id,name,sal) values (3,'Harshal',30000);
insert into emp_pk (id,name,sal) values (4,'Sachin',40000);
commit;

select * from emp_pk;

Note: We have created a Primary key on the ID column and we inserted

unique values.
Trying to insert a value which is already present in table.

Here we are saying error because we are trying to insert ID 1 which is

already present in the table.

Composite(Compound) primary key.

When no single column can uniquely identify a row, a primary key can be
formed by combining two or more columns. The combination of values
across these columns must be unique for each row, and none of the
columns participating in the composite key can contain null values.

Table level Composite Primary key.

create table emp_pk (id number(5) ,

name varchar2(10),
sal number(10),
constraint pk_eid primary key(id,name));
select a.column_name,b.constraint_name,b.constraint_type
from USER_CONS_COLUMNS a,ALL_CONSTRAINTS b
where a.Table_name=b.Table_name and b.Table_name='EMP_PK';

-​ Insert the data into Table

insert into emp_pk (id,name,sal) values (1,'Dipak',1000);

insert into emp_pk (id,name,sal) values (1,'Jatin',1000);
insert into emp_pk (id,name,sal) values (2,'Harshal',1000);

select * from emp_pk;

Here we will be able to insert value 1 in the ID column because we have

created the composite primary key in the ID and name column.
 -​ If we try to insert the same combination which is already present in
the table so that will trigger an error like constraint violated.

Drop the constraint

Alter Table emp_pk drop constraint pk_eid;

After table creation create composite primary key

Alter table emp_pk add constraint pk_eid primary key(id,name);

select a.column_name,b.constraint_name,b.constraint_type
from USER_CONS_COLUMNS a,ALL_CONSTRAINTS b
where a.Table_name=b.Table_name and b.Table_name='EMP_PK';

=====================================================================
6. Foreign key :-
A foreign key is a column or a set of columns in one table (the "child" table)
that refers to the primary key or a unique key in another table (the "parent"
table). It acts as a link or a bridge between these two tables.

Why is the foreign key needed?

The main reason for using foreign keys is to enforce referential integrity. In
simple terms, this means ensuring that the relationships between your
tables remain consistent and valid.

Where can we set the Foreign key?

i) At Column level
ii) At Table level
a) Create table statements.
b) After Table creation using an Alter table statement.

i) Column level foreign key.

create table dept_fk (

id number(5) constraint id_fk references emp_pk(id),
dept_name varchar2(10),
dept_loc varchar2(10));

Drop table dept_fk;

ii) Table level foreign key.

a)​
create table dept_fk (id number(5) ,
dept_name varchar2(10),
dept_loc varchar2(10),
constraint id_fk foreign key(id) references emp_pk(id));

Drop constraint
Alter table dept_fk drop constraint id_fk;

b)​Using Alter table statement

Alter table dept_fk add constraint id_fk foreign key (id) references
emp_pk(id);

select constraint_name,constraint_type from all_constraints where

table_name='EMP_PK';
select constraint_name,constraint_type from all_constraints where
table_name='DEPT_FK';

insert into emp_pk (id,name,sal) values (1,'A',10000);

insert into emp_pk (id,name,sal) values (2,'B',20000);
insert into emp_pk (id,name,sal) values (3,'C',30000);
insert into emp_pk (id,name,sal) values (4,'D',40000);
insert into emp_pk (id,name,sal) values (5,'E',50000);
insert into emp_pk (id,name,sal) values (6,'F',60000);
commit;
select * from emp_pk;

insert into dept_fk (id,dept_name,Dept_loc) values (1,'IT','Pune');

insert into dept_fk (id,dept_name,Dept_loc) values (2,'CO','GOA');
insert into dept_fk (id,dept_name,Dept_loc) values (2,'ME','Indore');
insert into dept_fk (id,dept_name,Dept_loc) values (3,'ETC','Nashik');
insert into dept_fk (id,dept_name,Dept_loc) values (4,'Automobile','Mumbai');
commit;

select * from dept_fk;

Que1) What will happen if I attempt to add a record to the child table
(where the foreign key is set) but it is not in the parent table, which is the
primary key table?
Answer: Adding data to a foreign key table will not be permitted.
So we need to insert that record in the parent table (Primary key table then
only it will allow us to insert the records in the child table (foreign key
table)).

Example:
insert into dept_fk (id,dept_name,Dept_loc) values (8,'Aerospace','Mumbai');
Que2) Can we insert a row in the parent table which is not present in
foreign key?
Ans:- Yes we can insert the record which is not present in the child table.

Here see the example where we tried to insert ID 9 in Primary Key/Parent

Table (Table_name emp_pk) and it allowed to insert in the table.
Que 3): Is it possible to remove a record from the Primary key table (Parent
Table) that isn't in the foreign key table?
Ans: Yes, records that don't refer to foreign keys can be deleted.

Please refer to the practical. If you see the ID 9 is present in parent key
table(emp_pk) but not present in child_table(dept_fk)

delete from emp_pk where id=9;

Check out the outcome The deletion of ID 9 from the parent table
demonstrates that we can remove records from the parent table that don't
have a reference record in the child table.

Que 4) If a record is present in a foreign key table, can we remove it from

the parent table?
Ans: No, we can’t remove the record since it contains an error.

Let's attempt to remove the record from the parent table. Since ID 1
appears in both tables in this case, attempting to remove it from the parent
table will result in an error message stating that a child record was
identified. To fix this, we must first remove the item from the child
table(dept_fk) before deleting it from the parent table(emp_pk).
Que5) Is there a way to forcefully remove the record from the parent table
without removing it from the child table?
Answer: Using the on delete cascade or on delete set null methods, we
can forcefully remove the records.

Let's drop the table and create the child table(foreign key table) again.
Create a child table (foreign key table with ON DELETE CASCADE)
method.

create table dept_fk (

id number(5) ,
dept_name varchar2(10),
dept_loc varchar2(10),
CONSTRAINT fk_parent FOREIGN KEY (id)
REFERENCES emp_pk(id) ON DELETE CASCADE);
Here we will be able to delete a record from the parent table without
deleting from the child table due to the ON DELETE CASCADE method.
IMP Please Note: If we delete a record from the parent table it will be
deleted from the child table as well.

Find the constraint details.

select constraint_name,constraint_type from all_constraints where
table_name=’DEPT_FK’;

Drop the constraint.

Alter table dept_fk drop constraint id_fk;

Add constraint with on delete cascade method.

Alter table dept_fk add constraint id_fk foreign key (id) references
emp_pk(id) on delete cascade;

Que 6) How to disable constraints?

Alter table table_name disable constraint constraint_name.

Que 7) Can we drop the primary key table if its referencing records are
present in foreign key?
Ans:- No we can't drop first we need to drop foreign key constraint.

Que8) Can we drop foreign key table if its parent records are present in the
parent table?
Ans: Yes we can drop.
Interview Questions.

1.​ Why do we create composite primary keys?

2.​ How many columns can we use in a composite primary key?
Ans → 32 columns
3.​ Can we set the primary key and composite primary key in a single
table?
4.​ What is the difference between Primary key and Unique Key.
5.​ Is it possible to add null values to a unique key constraint? If so, is
there a limit on the number of null values we may include in a unique
key constraint?
6.​ Does the table have any limits on the number of not-null, unique,
check, and default constraints that can be set on a single table?
7.​ Write a syntax to Drop the Not-null,Unique,check,Primary,Foreign
key constraints.
8.​ Write a syntax to add the Not-null,Unique,check,Primary,Foreign key
constraints.
9.​ How many primary keys can we create on a single Table?
10.​ Can we create Single column Primary Key and composite Primary
key on a single Table?
11.​ Can we have duplicate records in Foreign key table?
12.​ Can we have records in the Primary key table which are not
present in foreign key table?
13.​ How to Rename the constraint?
14.​ Can we insert null values in the child table where the foreign key
has been set.