Oracle - Data Definition Language

Hi Everyone,

Here are the basic Data definition Language statements.

Create Table

CREATE TABLE dept

(

deptid NUMBER,

deptname VARCHAR2(50),

CONSTRAINT pk_deptid PRIMARY KEY(deptid)

);

CREATE TABLE emp

(

empid NUMBER,

empname VARCHAR2(25),

deptid NUMBER,

salary NUMBER(7,2),

CONSTRAINT pk_empid PRIMARY KEY(empid),

CONSTRAINT fk_deptid FOREIGN KEY(deptid) REFERENCES dept(deptid)

);

Create Table from other Table Definition

CREATE TABLE <new table name> AS <SELECT Statement>

It creates the Table Structure and Copy the table data

Eg: CREATE TABLE sample AS SELECT * FROM emp;

Renaming Table

ALTER TABLE emp RENAME TO employee;

Adding Column to Table

ALTER TABLE employee ADD gender char(1);

Modify column in a Table

ALTER TABLE employee MODIFY empname VARCHAR2(100);

ALTER TABLE employee MODIFY empname VARCHAR2(50) NOT NULL;

ALTER TABLE employee MODIFY empname VARCHAR2(50) UNIQUE;

Renaming Column in Table

ALTER TABLE employee RENAME COLUMN empname TO ename;

Drop column from Table

ALTER TABLE employee DROP COLUMN gender;

Drop Constraint from Table

ALTER TABLE employee DROP CONSTRAINT pk_empid;

Add constraint to Table

ALTER TABLE employee ADD CONSTRAINT pk_empid PRIMARY KEY(empid);

ALTER TABLE employee MODIFY empid PRIMARY KEY;

Disable constraint in a Table

ALTER TABLE employee DISABLE CONSTRAINT pk_empid;

Enable constraint in a Table

ALTER TABLE employee ENABLE CONSTRAINT pk_empid;

Drop a Table

DROP TABLE employee;

Truncate Table

TRUNCATE emp;

Thanks for reading my Blog ! Happy learning :)