Skip to content
Home » Oracle » Create Table SQL Statement

Create Table SQL Statement

What is the syntax of CREATE TABLE in Oracle?

Since the syntax of CREATE TABLE statement can be very flexible and complicated, so using text-based SQL to design a table's layout has become unpopular among developers.

Instead, I'd recommend that you should go for some GUI tools and use their visualized steps to facilitate your development and improve your productivity:

However, if you really need to use or learn text-based SQL to create a table, we have a simple example for you.

SQL> create table FRUITS (
  2    name varchar2(20) default 'FRUIT' constraint FRUITS_PK primary key not null,
  3    price number(6,2) constraint FRUITS_CHECK_PRICE check (price <= 10000),
  4    in_stock char(1) default 'Y' not null
  5  );

Table created.

Through this example, I explain every part of the statement to let you learn more deeply.

Column Definition

In the example, the most important part is the column list which contains the following items.

  • Column name

  • You need to name the column here. According to Database Object Naming Rules, you cannot use reserved words or begin with a digit to name a column.

    In this case, we have 3 columns, they are NAME, PRICE and IN_STOCK. In which, NAME is one of reserved words used by Oracle, but it's a conditional keyword, so we can use it as a column name.

  • Data type and size

  • The data type can be character, number or TYPE. In this case, we have VARCHAR2(20), NUMBER(6,2) and CHAR(1).

    Please note that, there's no Boolean data type in Oracle database, so we use CHAR(1) to store the logical value 'Y or 'N'.

  • Default value

  • When there's no value provided by INSERT statements for the column, the default value will be used. This item is optional.

    DEFAULT ON NULL can also be useful when the value evaluates as NULL in INSERT statements.

  • Constraint

  • There're 5 types of constraint can be used in Oracle database:

    • Primary key
    • Foreign key (Reference key)
    • Unique key
    • Check
    • Not null

    Constraint is optional for a column specification. In this case, we have 2 constraints, the first one is the primary key, the other is a check constraint.

    Constraints can be inline or out-of-line, in this case, we use inline constraint clause which means we put constraint specification within column specification.

  • Nullable?

  • NOT NULL is a kind of constraint, it explicitly confine every column value cannot be null, otherwise, it's nullable. That is to say, if you don't set NOT NULL for the column, it can be NULL.

    A column can have multiple constraints if you have some business logic to support them. This is called data integrity. Usually, NOT NULL is used and accompanied with other type of constraint.

Non-unique Index

Since a nonunique index is not a constraint, it can only be created alone.

SQL> create index FRUITS_IN_STOCK on FRUITS (in_stock);

Index created.

Comments

Comments are also added externally.

Table Comment

SQL> comment on table FRUITS is 'For storing fruit objects.';

Comment created.

Column Comment

SQL> comment on column FRUITS.name is 'The fruit name.';

Comment created.

SQL> comment on column FRUITS.price is 'The fruit price.';

Comment created.

SQL> comment on column FRUITS.in_stock is 'Is it in stock?';

Comment created.

The table has been created.

Create Table As Select

Famous CTAS, stands for Create Table As Select, can also be a faster way to create a new table from a template one, if the template and new tables have some common columns for reuse.

The trick is to use CTAS to create a empty table instantly, then tailor it, e.g. add columns or drop columns, to meet our requirements.

Leave a Reply

Your email address will not be published. Required fields are marked *