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
- Table Comment
- Preview DDL
- Table Created
New a Table
Right click on "Tables". When the menu shows up, click on "New Table".
Check on "Advanced" to display more options.
Expand the bar at the bottom to display attribute details.
After we fill some data, we click on the header of the row to enable primary key on the column.
The primary key is enabled.
We click on the "+" icon to add more columns.
Columns have been added.
Please note that, for columns with character data type, the default value must be enclosed by single quotes.
To change the column order, we click on the "↑" or "↓" icon.
We have changed the third column to the second place.
To add more constraints, we can go for "Constraint" item.
We click on the "+" icon to add more constraints.
The new constraint has shown.
We click on the "+" icon to add more indexes.
The new index has shown.
Put a string to comment the table.
We preview the final result by checking the DDL before actually creating it.
We click on the table to see the table definition to verify the result.
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.