The next post will describe how to use the Oracle ALTER TABLE statement.

Oracle CREATE TABLE Statement

Oracle CREATE TABLE statement is used to create new tables in the database.

Data Types

Column Type

Description

Example

varchar2 (size)

String column. The value within the brackets indicates the maximum size of each field in the column (in characters)

varchar2(3) → ‘ABC’

varchar2(3) → ‘AB’

NUMBER (p,s)

Numeric column. Precision – number of digits, Scale – how many of the digits are located after the number point

number(5,2) → 476.29

number(5,2) → 6.29

DATE

Date format column

‘DD-MON-YYYY’

’15-MAY-2015′

Oracle Default Value

A column can be given a default value using the DEFAULT keyword. The DEFAULT keyword provides a default value to a column when the Oracle INSERT INTO statement does not provide a specific value. The default value can be literal value, an expression, or a SQL Function, such as SYSDATE.

Creating Oracle Constraints

Constraints enforce rules on the data in a table whenever a row is inserted, deleted, or updated. Constraints can be defined at the column or table level.

Defining Constraints at the Column Level

Constraint enforced at the column level:

Is created as part of the column definition

Always refers to a single column

A constraint at the column level has the following structure:

CONSTRAINT constraint_name constraint_type

Constraint_type – the type of the constraint to be enforced on the column (for example, Unique or Not Null)

Constraint_name – although not mandatory, it is always advisable to give the constraint a name, thereby allowing you to easily identify it.

The following naming convention is commonly used by many database developers :

<table name>_<column_name>_<constraint abbreviation>

For example:

Primary Key (PK)

In Oracle, the Primary Key constraint is a column (or a set of columns) that uniquely identifies each row in the table, this constraint enforces uniqueness and ensures that no column that is part of the Primary Key can hold a NULL value. Only one Primary Key can be created for each table.

FOREIGN KEY (FK)

In Oracle, the Foreign Key constraint designates a column (or a set of columns) as a Foreign Key and establishes a relationship between a Primary Key (or Unique) in different table (or in the same table) The syntax used for defining a Check Constraint is as follows: