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.
- New a Table
- Primary Key
- Column Definition
- Column Order
- Constraint
- Index
- Table Comment
- Preview DDL
- 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.