Managing Tables   «Prev  Next»

Lesson 2Creating a table
ObjectiveProcess involved with creating a table.
Before you learn to manage a table, you must first create a table.
As with all parts of an Oracle database, you create a table using a type of SQL called Data Definition Language[1]. Although you can create a table using other interfaces, such as the Schema Manager discussed later in this module, every interface generates the appropriate SQL statement to send to the Oracle database.
The basic syntax for creating a table with SQL is described in the following MouseOver.
Oracle SQL Table
  1. The required CREATE TABLE keywords begin the DDL statement, which will create a table.
  2. The tablename must follow the rules for Oracle object names. It is followed by a list of column names and datatypes within parentheses.
  3. Each colname must be followed by a datatype identifier. All column names must be unique within their table.
  4. Each colname must be followed by a datatype identifier. All column names must be unique within their table.
  5. Each colname must be followed by a datatype identifier. All column names must be unique within their table.
  6. Each colname must be followed by a datatype identifier. All column names must be unique within their table.
CREATE TABLE tablename (colname1 datatype, 
colname2 datatype,
... colnameN datatype) 
The required CREATE TABLE keywords begin the DDL statement, which will create a table.

Basic Syntax Creating Table
There are many options you can use when creating a table. You can assign a storage option for the table, as described later in this course. You can also add constraints to the table, logical operations that place conditions on the values used in the table. You will learn more about constraints later in this course.
The basic syntax for creating tables includes the CREATE TABLE keywords, a unique table name, and a list of columns that make up the table and their associated datatypes.
The next lesson teaches you how to explore basic Oracle datatypes.

CREATE TABLE Purpose

Use the CREATE TABLE statement to create one of the following types of tables:
  1. A relational table, which is the basic structure to hold user data.
  2. An object table, which is a table that uses an object type for a column definition. An object table is explicitly defined to hold object instances of a particular type.

You can also create an object type and then use it in a column when creating a relational table. Tables are created with no data unless a subquery is specified. You can add rows to a table with the INSERT statement. After creating a table, you can define additional columns, partitions, and integrity constraints with the ADD clause of the ALTER TABLE statement. You can change the definition of an existing column or partition with the MODIFY clause of the ALTER TABLE statement.

[1] Data Definition Language: The portion of the SQL language that is used to create and alter the structures that hold the data in the database.