Create user and grant privileges

create user dba92 identified by dba92;
 revoke create session from dba92;
grant alter user to dba92; -- this user can alter any user;

 drop user dba92 cascade;

--Role is a bundle of related privileges;
create role rolename;
grant rolename to user;

alter user dba92
identified by employ;

--Check privileges
select role, privilege from role_sys_privs;

ON DELETE CASCADE

In last post we created tables and added constraints. In this post we shall insert data and delete data.

ALTER TABLE EMP
ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPTID)
REFERENCES DEPT(DEPTNO) ON DELETE CASCADE;


CREATE TABLE DEPT
(
    DEPTNO NUMBER(4) CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY,
    DNAME VARCHAR2(30) CONSTRAINT DEPT_DNAME_NN NOT NULL
);

DROP TABLE EMP;

CREATE TABLE EMP
(
    EMPNO NUMBER(4),
    ENAME VARCHAR2(50),
    DOB DATE,
    GENDER VARCHAR2(10) DEFAULT 'MALE',
    DEPTID NUMBER(4)
);

ALTER TABLE EMP
ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY(EMPNO);

ALTER TABLE EMP
ADD CONSTRAINT EMP_GENDER_CHK CHECK (GENDER IN ('MALE', 'FEMALE','TRANSVESTILE'));

ALTER TABLE EMP
ADD CONSTRAINT EMP_EMAIL_UK UNIQUE(DEPTID);

ALTER TABLE EMP
ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPTID)
REFERENCES DEPT(DEPTNO);

Create Tables, Alter Tables, and Constraints

CREATE TABLE DEPT
(
    DEPTNO NUMBER(4) CONSTRAINT DEPT_DEPTNO_PK PRIMARY KEY,
    DNAME VARCHAR2(30) CONSTRAINT DEPT_DNAME_NN NOT NULL
);

DROP TABLE EMP;

CREATE TABLE EMP
(
    EMPNO NUMBER(4),
    ENAME VARCHAR2(50),
    DOB DATE,
    GENDER VARCHAR2(10) DEFAULT 'MALE',
    DEPTID NUMBER(4)
);

ALTER TABLE EMP
ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY(EMPNO);

ALTER TABLE EMP
ADD CONSTRAINT EMP_GENDER_CHK CHECK (GENDER IN ('MALE', 'FEMALE','TRANSVESTILE'));

ALTER TABLE EMP
ADD CONSTRAINT EMP_EMAIL_UK UNIQUE(DEPTID);

ALTER TABLE EMP
ADD CONSTRAINT EMP_DEPTNO_FK FOREIGN KEY(DEPTID)
REFERENCES DEPT(DEPTNO);

/*See the constraints*/
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME IN ('EMP', 'DEPT');