Creating Tables
9
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder9Ć2
Schedule: Timing Topic
45 minutes Lecture
25 minutes Practice
70 minutes Total
Class Management Note:
Files required for this lesson are:
Demonstration: None
Practice: p9q2.sql, p9q3.sql
Creating Tables 9Ć3
Objectives
In this lesson, you will create tables. You will also build integrity constraints,
which are rules governing what can and cannot be done with the data.
At the end of this lesson, you should be able to
D
Create a table containing integrity constraints.
D
Identify table naming conventions.
D
Describe the datatypes that can be used when specifying column definitions.
D
Recognize the indexes that are created automatically by constraints.
D
Create a table by populating it with rows from another table.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder9Ć4
Creating Tables 9Ć5
Overview
An Oracle7 database can contain multiple data structures. Each structure should be
outlined in the database design so that it can be created during the build stage of

Create tables to store data by executing the SQL CREATE TABLE command. This
command is one of the data definition language (DDL) commands, which you will
cover in the next several lessons. DDL commands are a subset of SQL commands
used to create, modify, or remove Oracle7 database structures. These commands have
an immediate effect on the database, and they also record information in the data
dictionary.
In order to create a table, a user must have the CREATE TABLE privilege and a
storage area in which to create objects. The database administrator uses data control
language (DCL) commands, which are covered in a later lesson, to grant privileges to
users.
Abridged Syntax
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr][column_constraint],
...
[table_constraint]);
where: schema is the same as the owner’s name.
table is the name of the table.
DEFAULT expr specifies a default value if a value is omitted in
the INSERT statement.
column is the name of the column.
datatype is the column’s datatype and length.
column_constraint is an integrity constraint as part of the column
definition.
table_constraint is an integrity constraint as part of the table
definition.
For more information, see
Oracle7 Server SQL Reference, Release 7.3, “CREATE TABLE.”
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder9Ć8
Creating Tables 9Ć9
Creating Tables

Server user.
D
Names must not be an Oracle7 Server reserved words.
Naming Guidelines
D
Use descriptive names for tables and other database objects.
D
Name the same entity consistently in different tables. For example, the
department number column is called DEPT_ID in both the S_EMP table and the
S_REGION table.
Note: Names are case-insensitive. For example, EMP is treated as the same name as
eMP or eMp.
For more information, see
Oracle7 Server SQL Reference, Release 7.3, “Object Names and Qualifiers.”
Technical Note:
You can override naming rules by using double quotation marks, for
example, CREATE TABLE “emp”.... To reference that table, use
double quotation marks, for example SELECT * FROM “emp”. Double
quotation marks when creating table names are not recommended.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder9Ć12
Creating Tables 9Ć13
Oracle7 Datatypes
There are many different column types. The Oracle7 Server can treat values of one
datatype differently from values of other datatypes. The basic datatypes are character,
number, date, and RAW.
Sample Oracle7 Datatypes
Datatype
Description
VARCHAR2(size) Variable length character values up to maximum length
size. Minimum length is 1, maximum length is 2000.

constraints are enforced at the database level.
You can use constraints to
D
Enforce rules at the table level whenever a row is inserted, updated, or deleted
from that table. The constraint must be satisfied for the operation to succeed.
D
Prevent the deletion of a table if there are dependencies from other tables.
D
Provide rules for Oracle tools, such as Developer/2000.
Data Integrity Constraints
Constraint
Description
NOT NULL Specifies that this column may not contain a null value.
UNIQUE Specifies a column or combination of columns whose
values must be unique for all rows in the table.
PRIMARY KEY Uniquely identifies each row of the table.
FOREIGN KEY Establishes and enforces a foreign key relationship
between the column and a column of the referenced
table.
CHECK Specifies a condition that must be true.
For more information, see
Oracle7 Server SQL Reference, Release 7.3, “CONSTRAINT clause.”
Technical Note:
Oracle Version 6 used constraints purely as a documentation device.
NOT NULL constraints are the only constraints displayed by the SQL*Plus
DESCRIBE command.
NOT NULL and UNIQUE constraints are not allowed if the PRIMARY
KEY constraint is specified.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder9Ć16