DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones AWS Cloud
by AWS Developer Relations
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Partner Zones
AWS Cloud
by AWS Developer Relations
Securing Your Software Supply Chain with JFrog and Azure
Register Today

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

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
  1. DZone
  2. Data Engineering
  3. Databases
  4. An Explanation of the CREATE TABLE Syntax in Oracle

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.

Ben Brumm user avatar by
Ben Brumm
·
Apr. 24, 17 · Tutorial
Like (1)
Save
Tweet
Share
3.90K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database sql Syntax (programming languages)

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

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com

Let's be friends: