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');