SQL Developer Create Table Example

How do I create a new table in Oracle?

To create a new table, we can choose to compose a hand-writing SQL statement which may be lengthy, complex, and error-prone. Or, we can also use handy GUI tools like SQL developer to reach the goal.

In this post, I'd like to illustrate how to create a table in SQL Developer step by step. For creating a partitioned table in SQL developer, we also have a tutorial for you, but I do recommend that you read this post first.

Let's see the procedure.

1. New a Table
2. Primary Key
3. Column Definition
4. Column Order
5. Constraint
6. Index
7. Table Comment
8. Preview DDL
9. Table Created

New a Table

Right click on "Tables". When the menu shows up, click on "New Table".

SQL Developer - Create Table - New Table

Check on "Advanced" to display more options.

SQL Developer - Create Table - Display Advanced Options

Expand the bar at the bottom to display attribute details.

SQL Developer - Create Table - Display Attribute Details

Primary Key

After we fill some data, we click on the header of the row to enable primary key on the column.

SQL Developer - Create Table - Enable Primary Key

The primary key is enabled.

SQL Developer - Create Table - Primary Key Enabled

Column Definition

We click on the "+" icon to add more columns.

SQL Developer - Create Table - Add Column

Columns have been added.

SQL Developer - Create Table - Column Added

Please note that, for columns with character data type, the default value must be enclosed by single quotes.

Column Order

To change the column order, we click on the "↑" or "↓" icon.

SQL Developer - Create Table - Change Column Order

We have changed the third column to the second place.

SQL Developer - Create Table - Column Order Changed

Constraint

To add more constraints, we can go for "Constraint" item.

SQL Developer - Create Table - Constraint

We click on the "+" icon to add more constraints.

SQL Developer - Create Table - Add Constraint

The new constraint has shown.

SQL Developer - Create Table - Constraint Added

Index

We click on the "+" icon to add more indexes.

SQL Developer - Create Table - Add Index

The new index has shown.

SQL Developer - Create Table - Index Added

Table Comment

Put a string to comment the table.

SQL Developer - Create Table - Table Comment

Preview DDL

We preview the final result by checking the DDL before actually creating it.

SQL Developer - Create Table - Display DDL

Table Created

We click on the table to see the table definition to verify the result.

SQL Developer - Create Table - Table Created

We have created a new table in SQL developer.

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.