An Explanation of the CREATE TABLE Syntax in Oracle
The CREATE TABLE statement in Oracle allows you to create new tables on the database. Learn what the syntax is in this article.
Join the DZone community and get the full member experience.
Join For FreeLet’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 NULL
keywords.
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.
Opinions expressed by DZone contributors are their own.
Trending
-
Software Development: Best Practices and Methods
-
Getting Started With Istio in AWS EKS for Multicluster Setup
-
Database Integration Tests With Spring Boot and Testcontainers
-
Introduction to API Gateway in Microservices Architecture
Comments