Creating Tables in Oracle SQL

Basic Syntax

CREATE TABLE tablename (
    col1 data_type col_constraint,
    col2 data_type col_constraint,
    ...
    table_constraint
);

For example:

CREATE TABLE authors (
    author_id NUMBER GENERATED BY DEFAULT AS IDENTITY (CACHE 500),
    firstname VARCHAR2(50) NOT NULL,
    lastname VARCHAR2(50) NOT NULL,
    age NUMBER,
    PRIMARY KEY (author_id)
);

Types of Identity Columns

Identity columns are similar to the AUTO_INCREMENT column in MySQL. Here are the different options that can be used.

GENERATED ALWAYS AS IDENTITY: value is automatically generated, and no other value can be specified.
GENERATED BY DEFAULT AS IDENTITY: value is automatically generated if no column value is specified. NULL value cannot be assigned.
GENERATED BY DEFAULT ON NULL AS IDENTITY: value is automatically generated if a no value, including NULL, is set.

Sequence Generator Attributes

• CYCLE: when the max value is reached, it will restart, starting with the min value
• CACHE [num]: stores a certain number of values in the cache for fast retrieval
• START WITH [num]: specify the starting value (1 by default)
• INCREMENT BY [num]: specify the increment value between each identity (1 by default)

The full list of attributes can be found here.

Tables vs Views

Views are basically virtual tables that are composed of subset data, i.e. results from queries, obtained from one or more tables.

When to use views instead of tables?

• We can use views to get a more consolidated view of the data by combining rows and columns from multiple tables.
• It can also be used for security reasons by giving users access to the data without direct access to the base tables.
• It can also be a way to provide backward compatibility to apps that are using the database