Data Definition Language

This page summarizes how to:

  • Create a database table.
  • Modify the existing definition of the table.

Creating Database Tables

A table can be created by using CREATE TABLE statement. We need the CREATE TABLE system privilege to execute this command.

The general syntax of the CREATE TABLE statement is as follows:

CREATE TABLE [schema.]tablename (
column1 datatype ( size ) [ DEFAULT <expr> ][ CONSTRAINT constraint_name ] [ column_constraint ] [ enable / disable ],
column2 datatype ... ,
...
[ CONSTRAINT constraint_name ][table_constraint ) [ ENABLE / DISABLE ),
...
);

Example commands are as follows

With NOT NULL column constraint

Example :

CREATE TABLE emp (
empno NUMBER (4) NOT NULL,
ename VARCHAR2(1٥),
.......
);

with UNIQUE Constraint

Example:

CREATE TABLE emp (
empno NUMBER (4) NOT NULL UNIQUE,
ename VARCHAR2 (10),
.....
);

Using PRIMARY KEY Constraint

CREATE TABLE emp (
empno NUMBER (4) PRIMARY KEY,
ename VARCHAR2 (10),
.....
);

FOREIGN KEY Constraint, REFERENCES and ON DELETE CASCADE Options

Example :

CREATE TABLE emp (
empno NUMBER (4) PRIMARY KEY,
ename VARCHAR2 (10),
........
deptno NUMBER(2) REFERENCES dept(deptno));

Enabling and Disabling Constraints

CREATE TABLE emp (
empno NUMBER (4) PRIMARY KEY,
ename VARCHAR2 (10),
.....
sal NUMBER(7,2) CHECK ( sal < 6000) DISABLE,
.....
deptno NUMER(2) REFERENCES dept(deptno));

Table Constraints

A Table constraint is the constraint that is applied to one or more columns of a table. The table constraints are defined after the last column definition. For example.

CREATE TABLE emp (
empno NUMBER (4) PRIMARY KEY,
ename VARCHAR2 (10),
.....
sal NUMBER(7,2) CHECK ( sal < 6000) DISABLE,
.....
deptnp NUMBER(2),
......
CONSTRAINT emp_fk FOREIGN KEY ( deptno ) REFERENCES dept : deptno ) ) ;

Here a foreign key has been defined as a table constraint. Like this we can define primary key and other constraints as table constraints.

Creating a table using Subquery

The table can also be created from the existing table. The general syntax for this is

SQL> CREATE TABLE empnew
AS SELECT * FROM emp;

To create the new department table use the following query

SQL> CREATE TABLE dept_new
AS SELECT * FROM dept;

Modifying the Table structure

The general syntax of the ALTER TABLE statement is as follows:

ALTER TABLE tablename (
[ MODIFY | ADD ] (column definitions),
[ENABLE | DISABLE ] [ CONSTRAINT constraint_name ]
[ DROP COLUMN column_name ]
);

Adding columns

To add a new column in the table definition, we can use ALTER TABLE command with an ADD clause.

ALTER TABLE tablename
ADD ( column_definition );

Example: To add a column department head (dept.head) to the dept table, use the following query

SQL> ALTER TABLE dept
ADD ( dept.head VARCHAR2 (10) ) ;

Modifying Column Definitions

The ALTER TABLE command with MODIFY clause is used to modify a column definition. The syntax is

For example, to modify the size of the dept- head to 25, use the following quety

SQL> ALTER TABLE dept
MODIFY ( dept_head VARCHAR2(25));

The Drop Clause

This clause is used to remove the constraints from a table. The syntax is

ALTER TABLE tablename
DROP
[COLUMN column_name]
[CONSTRAINT constraint_name]
[PRIMARY KEY]
[UNIQUE (column, column,....)]
[CASCADE];

Using CASCADE option with DROP, we can remove all the dependent constraints.

Example

SQL> ALTER TABLE dept
DROP PRIMARY KEY
CASCADE;

This will drop the primary key definition in the dept table and also the foreign key definition from the emp table. This is because the foreign key definition in the emp table is dependent on ( i.e. references) the primary key definition from dept table.

Enabling /Disabling Column Constraints

To enable or disable constraints we can use ENABLE or DISABLE option of the ALTER TABLE command. For example, if we use the following query

SQL> ALTER TABLE emp
DISABLE PRIMARY KEY;

This removes all the rows from the emp table. The table definition still remains in the database.

Deleting a Table

To delete a table DROP command is used. The DROP command deletes the table definition and all its data.

Syntax:

DROP TABLE tablename
[ CASCADE CONSTRAINTS ];

Example: To delete the emp table we can use the following query..

SQL> DROP TABLE emp;

The CASCADE CONSTRAINTS option in DROP command drops all referential integrity constraints that refer to primary and unique keys in the dropped table.

For example, to delete the dept table and all the dependent constraint definitions, we can use the following query

SQL> DROP TABLE dept CASCADE CONSTRAINTS;

This will delete the dept table, all the constraints from dept table (primary key definition) and also the dependent constraints from the em p table (foreign key definition).

Edit this page