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 diagram.

Basic syntax for Creating a Table

CREATE TABLE tablename (colname1 datatype, colname2 datatype, colname 3 datatype ...) 
CREATE TABLE tablename (colname1 datatype, 
colname2 datatype,
... colnameN datatype) 


CREATE TABLE tablename (colname1 datatype, colname2 datatype, colname 3 datatype ...)
  1. CREATE TABLE: The required CREATE TABLE keywords begin the DDL statement, which will create a table.
  2. tablename: The tablename must follow the rules for Oracle object names. It is followed by a list of column names and datatypes within parentheses.
  3. (colname1 datatype, colname2 datatype, colname3 datatype): Each colname must be followed by a datatype identifier. All column names must be unique within their 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.
The required CREATE TABLE keywords begin the DDL statement, which will create a table.


Relational tables: Using the Oracle-supplied datatypes you can create tables to store the rows inserted and manipulated by your applications. Tables have column definitions, and you can add or drop columns as the application requirements change. Tables are created via the create table command.
Here is the create table command for the NEWSPAPER table: 
create table NEWSPAPER (
Feature VARCHAR2(15) not null,
Section CHAR(1),
Page NUMBER
);

you can read it as:
Create a table called NEWSPAPER.

It will have three columns,
  1. named Feature (a varying-length character column),
  2. Section (a fixed-length character column), and
  3. Page (a numeric column).

The values in the Feature column can be up to 15 characters long, and every row must have a value for Feature. Section values will all be 1 character long. In later modules you will see how to extend this simple command to add constraints, indexes, and storage clauses. For now, the NEWSPAPER table will be kept simple so that the examples can focus on SQL.

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.

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. The next lesson teaches you how to explore basic Oracle datatypes.

[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.

SEMrush Software