Oracle - Adding a Constraint | Oracle Rename Constraint Name | Rename table Column | Purging Table

►Title:

Oracle - Adding a Constraint | Oracle Rename Constraint Name | Rename table Column | Purging Table

►Content:
--Adding a Constraint

--CREATE AND COPY A TABLE FROM employees
create table emp2
as (
select *
from employees
);

--drop table emp2

desc emp2

select *from emp2;

--NOW CREATING A CONSTRAINT

ALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(MANAGER_ID)
REFERENCES emp2(EMPLOYEE_ID);

ALTER TABLE emp2
MODIFY EMPLOYEE_ID PRIMARY KEY;

--Check available constraints by dictionary

select *--constraint_name
from user_constraints
where table_name='EMP2'

--Dropping a Constraint
ALTER TABLE emp2
DROP CONSTRAINT emp_mgr_fk;

--CONSTRAINT - ON DELETE Clause
create table a
(
a_id number primary key not null,
a_name varchar2(50)
);

create table b
(
b_id number primary key not null,
a_id number,
b_name varchar2(50)
);

--insert into a
insert into a (a_id,a_name)
values(1,'Kamal');

insert into a (a_id,a_name)
values(2,'Jamal');

insert into a (a_id,a_name)
values(3,'Sumon');

insert into b (b_id,a_id,b_name)
values(1,1,'Kamal');

insert into b (b_id,a_id,b_name)
values(2,1,'Jamal');

insert into b (b_id,a_id,b_name)
values(3,2,'Sumon');

insert into b (b_id,a_id,b_name)
values(4,3,'Sumon');

01. ON DELETE CASCADE

ALTER TABLE b
ADD CONSTRAINT b_a_id_fk
FOREIGN KEY(a_id)
REFERENCES a(a_id) ON DELETE CASCADE;

02. ON DELETE SET NULL
ALTER TABLE b
ADD CONSTRAINT b_a_id_fk
FOREIGN KEY(a_id)
REFERENCES a(a_id) ON DELETE SET NULL;

select *from a;
select *from b;

--drop table a;
--drop table b;

--DROP CONSTRAINT

ALTER TABLE b
DROP CONSTRAINT b_a_id_fk

delete from a
where a_id=1

--Drop table COLUMN
create table c
(
C_id number primary key not null,
a_id number,
y_id number,
z_id number,
c_name varchar2(50)
);

ALTER TABLE c
DROP COLUMN a_id CASCADE CONSTRAINTS;

desc c

ALTER TABLE c
DROP (y_id,z_id) CASCADE CONSTRAINTS;

desc c

--DISABLE/ENABLE Constraints
ALTER TABLE b
DISABLE CONSTRAINT NEW_B_A_ID_FK;

insert into b (b_id,a_id,b_name)
values(5,500,'Sumon');

ALTER TABLE b
ENABLE CONSTRAINT NEW_B_A_ID_FK;

delete
from b
where b_id=5

insert into b (b_id,a_id,b_name)
values(5,500,'Sumon');

--Renaming Table Constraints
ALTER TABLE b
RENAME CONSTRAINT b_a_id_fk TO new_b_a_id_fk;

select *
from user_constraints
where table_name='B'

--Renaming Table Columns
create table marketing
(
team_id number,
team_name varchar2(50)
);

desc marketing;

ALTER TABLE marketing RENAME COLUMN team_id
TO id;

desc marketing;

--DROP TABLE … PURGE
create table New_EMP
(
EMP_ID number,
EMP_Name varchar2(50)
);

SELECT ORIGINAL_NAME-- ,*
FROM USER_RECYCLEBIN
WHERE TYPE='TABLE'

DROP TABLE New_EMP

SELECT ORIGINAL_NAME-- ,*
FROM USER_RECYCLEBIN
WHERE TYPE='TABLE'

PURGE TABLE NEW_EMP;

SELECT ORIGINAL_NAME-- ,*
FROM USER_RECYCLEBIN
WHERE TYPE='TABLE'

PURGE USER_RECYCLEBIN

SELECT ORIGINAL_NAME-- ,*
FROM USER_RECYCLEBIN
WHERE TYPE='TABLE'

🔗LINKS🔗

►►► Find me on Social Media◄◄◄

✉ Contact with me

❤ Make sure you SUBSCRIBE and be the 1st one to view newly created videos!