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
Through this example, I explain every part of the statement to let you learn more deeply.
In the example, the most important part is the column list which contains the following items.
Data type and size
- Primary key
- Foreign key (Reference key)
- Unique key
- Not null
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.
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'.
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.
There're 5 types of constraint can be used in Oracle database:
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.
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.
Since a nonunique index is not a constraint, it can only be created alone.
SQL> create index FRUITS_IN_STOCK on FRUITS (in_stock);
Comments are also added externally.
SQL> comment on table FRUITS is 'For storing fruit objects.';
SQL> comment on column FRUITS.name is 'The fruit name.';
SQL> comment on column FRUITS.price is 'The fruit price.';
SQL> comment on column FRUITS.in_stock is 'Is it in stock?';
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.