An Explanation of the CREATE TABLE Syntax in Oracle

Let’s take a look at the syntax for the CREATE TABLE statement in Oracle.

CREATE schema_name.table_name (
  column_name data_type [NULL | NOT NULL] [inline_constraint] [DEFAULT default_value],
  ...
  out_of_line_constraints
)
TABLESPACE tablespace_name;

This statement contains a lot of parameters and options. I’ve written a guide to the CREATE TABLE statement here, but this article focuses on the syntax of the statement. Let’s take a look at each of them.

CREATE

The first line of the CREATE TABLE statement starts with the word CREATE. This denotes that we are creating an object on the database.

We can then specify the schema name and then put a period. This is optional. It allows us to store the new table in a specific schema. If it is not mentioned, the table is created in the schema of the currently logged in user.

Then, we specify the table name. This is the name of the new table to create. It can be up to 30 characters long and must start with a letter. It’s a good idea not to use spaces in the table name.

We then open the brackets to start defining our columns.

Columns

After the first line and the opening bracket, we can start defining our columns.

We can define as many columns as we like here, which are separated by a comma, and are usually on separate lines.

To define a column, we start with the column name. This can also be up to 30 characters.

Then, we specify the data type. This can be any of Oracle’s data types, such as NUMBER or VARCHAR2.

Optionally, we can specify if the column is allowed NULL values or not allowed NULL values by using the NULL or NOT NULLkeywords.

Next, we can specify constraints for the column, such as Primary Key, Check, or Unique. Defining the constraints here means that they are “inline constraints,” as they are defined next to the column that they refer to.

Defining constraints is optional at this point, but they are useful if you want to improve your data quality.

Finally, we can specify default values. This is the value that the column is set to if no value is specified, such as part of the INSERT statement. By default, the value is NULL, but you can specify a different value if you like.

This set of parameters can be repeated for every column you want in your table. The definitions of the columns need to be separate by a comma, and it’s good practice to put them on separate lines to improve readability.

Constraints

After all of the columns have been defined, we can define constraints.

We could have defined constraints earlier, against each column, which are inline constraints, as I mentioned earlier in this article.

We can also define constraints here after all of the columns are defined. These are called out of line constraints. They can refer to any column in your table, and often refer to multiple columns.

The constraints we can define here are:

• Primary key.
• Foreign key.
• Check.
• Unique.

We can’t define a NOT NULL constraint here, as that needs to be written as an inline constraint.

The syntax for defining a constraint is different for each type, but generally you specify the keywords for the constraint type, the name of the constraint, the columns it refers to, and any rules that need to be applied.

After your constraints, the closing bracket can be entered here.

Tablespace

After the closing brackets, we can specify a range of parameters. One of the most common is the tablespace.

Tables need to be stored in an area on the server called tablespaces. By default, the table is created in the tablespace that belongs to the currently logged in user.

However, you can specify a different tablespace at this point. It’s not a parameter I have used very often, but it’s there if you need to use it.

Finally, end the command with a semicolon. It’s now ready to be run. Run the command and your new table is created! The table usually doesn’t take very long to create — often it's just a couple of seconds.

So, that’s how you can create a table in Oracle SQL and what each of the syntax items means.