CREATE TABLE Command in Oracle

The complete syntax of Oracle's CREATE TABLE command is about two pages long. So, to simplify the learning process, I have divided the command's parameters across several lessons. Let's begin with the basic command in which you create a relational table and the columns. The basic syntax of the CREATE TABLE command is shown in the MouseOver below followed by an example from the House-O-Pets course project.

The CREATE TABLE command in Oracle Database is a fundamental SQL statement used to create relational database tables. It consists of a set of SQL clauses that define the table and its columns, along with their associated datatypes and constraints. The basic syntax for the CREATE TABLE command in Oracle is as follows:

CREATE TABLE schema_name.table_name (
    column1 datatype1 [DEFAULT expr1] [constraint1],
    column2 datatype2 [DEFAULT expr2] [constraint2],
    column3 datatype3 [DEFAULT expr3] [constraint3],
    ...
    [table_constraints]
);

The segments of this syntax can be broken down as:

1. schema_name.table_name: The name of the table. The schema_name is optional and it refers to the name of the schema where the table will be created.
2. column1, column2, column3: These are the names of the columns that will be created in the table.
3. datatype1, datatype2, datatype3: Each column must have a specified datatype, which determines the kind of data it can store. This could be NUMBER, VARCHAR2, DATE, BLOB, among others.
4. DEFAULT expr1, expr2, expr3: These are optional. A default value can be provided for a column using the DEFAULT clause. This value will be used if no value is explicitly provided when inserting data into the table.
5. constraint1, constraint2, constraint3: These are optional. Constraints are rules that apply to the data in the table to ensure its integrity. Examples include NOT NULL, UNIQUE, PRIMARY KEY, CHECK, and FOREIGN KEY.
6. table_constraints: These are constraints that are applied to the table at a level above individual columns. They can include PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints that span multiple columns.

Here is an example of a CREATE TABLE statement in Oracle:

CREATE TABLE hr.employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(25) UNIQUE,
    hire_date DATE DEFAULT SYSDATE,
    salary NUMBER(8,2),
    manager_id NUMBER(6),
    department_id NUMBER(4),
    CONSTRAINT fk_department_id FOREIGN KEY (department_id)
        REFERENCES hr.departments(department_id)
);

This command creates a new table named employees in the hr schema. It has several columns with various datatypes and constraints, and a foreign key constraint fk_department_id that refers to the department_id in the departments table. Remember, the effectiveness of using CREATE TABLE commands in Oracle depends on understanding the structure of the data you're working with, and how you expect that data to interact. Each decision about datatypes and constraints should be purposeful, tailored to the specific needs of your data and applications.

If you have more than one column in the table, place a comma in front of the second column definition and all the following columns. Enclose the entire set of columns with parentheses. While you need to know how to create SQL for the Oracle Database Administrator Certification exam, you do have the option (on the job) of using a Enterprise Manager's Schema Manager creates tables tool to create the SQL for you, which is described below.

If you, as a database administrator or developer, have access to the Enterprise Manager's Database Administration tools, by all means take advantage of them. The Schema Manager has a Windows-like format that makes it very easy and intuitive to use. In addition, you can use the tool and tell it to display the SQL commands that it generates. By looking at the code it creates, you can learn how to code commands yourself whenever needed. The following graphic shows the window that creates a new table and the SQL that is generated.

The following table describes most of the Oracle datatypes that you can use.

Datatype	Description
VARCHAR2(n)	A text string with variable length. Specify the maximum length (1 - 4,000 characters). Oracle version 7 only supports a maximum of 2,000 characters.
NUMBER(p,s)	Number. Specify the total number of digits (p), up to 38. Specify the number of digits to the right of the decimal place(s), from -87 to 127.
DATE	Date and time. Valid date range from January 1, 4712 BC to December 31, 4712 AD.
CHAR(n)	A text string with a fixed length. Specify the exact length (1 - 255).
LONG	Long binary file. Good for storing images, documents, audio, and so on. Maximum size is two gigabytes.
RAW(n)	Raw binary data with variable length of up to 2,000 characters. Good for small graphics and documents.
BLOB, CLOB, NCLOB	These are large objects. Good for audio, video, and other high-volume data items. Each can store up to four gigabytes in a single row or column.
BFILE	A large object that is stored in a file system outside of the database itself, accessible from both inside outside the database.

The next lesson adds more details to the CREATE TABLE command.