Using create table statement table columns and constraints can be declared very easily. This tutorial will help you to learn different types of database table creation statements on SQL developer by using multiple examples.

Example-1: Creating a table with a primary key(s)

The following statement will create a table named ‘customers’ with four fields (id, name, email, and phone) where id will be the primary key.

CREATE TABLE customers
(
id NUMBER,
name VARCHAR2(50),
email VARCHAR2(20),
phone VARCHAR2(20),
CONSTRAINT cust_pk PRIMARY KEY(id)
);

When you commit the window and refresh the tables then you will get the following information about the table after clicking constraint tab.

The following statement will create a table named ‘customers2’ with multiple primary keys.

CREATE TABLE customers2
(
cust_id NUMBER,
cust_name VARCHAR2(50),
cust_email VARCHAR2(20),
cust_phone VARCHAR2(20),
cust_area VARCHAR2(30),
CONSTRAINT cust_pk2 PRIMARY KEY(cust_id,cust_area)
);

Example-2: Creating a table with a unique key

The email address of the individual customer is unique. The following statement will create a table with a unique email field.

CREATE TABLE customers3
(
cust_id NUMBER,
cust_name VARCHAR2(50),
cust_email VARCHAR2(20),
cust_phone VARCHAR2(20),
cust_area VARCHAR2(30),
CONSTRAINT cust_uk UNIQUE(cust_email),
CONSTRAINT cust_pk3 PRIMARY KEY(cust_id,cust_area)
);

If you click on the constraint tab then the following information will appear about primary key and unique key.

Example-3: Creating a table with a foreign key

The following statement will create a table named ‘sales’ where the column ‘customer_id’ will be declared as a foreign key. That means ‘customer_id’ column can contain the values from the customer table’s ‘id’ column only. Three fields, invoice_date, amount and customer_id will be declared as NOT NULL. to keep these fields non-empty.

CREATE TABLE sales
(
invoice_number VARCHAR2(20) PRIMARY KEY,
invoice_date DATE NOT NULL,
amount NUMBER NOT NULL,
customer_id NUMBER NOT NULL,
CONSTRAINT sales_cust_fk FOREIGN KEY (customer_id) REFERENCES customers (id)
)

If you click on the constraint tab then the following information will appear for primary key, unique key and NULL constraints.

Example-4: Modifying table structure

The new column can be added to a table or any existing column can be updated or deleted from the table by using the alter statement.

Adding column:

ALTER TABLE customers
ADD address VARCHAR2(200);

Deleting column:

ALTER TABLE customers
DROP COLUMN phone;

Updating column structure:

ALTER TABLE customers
MODIFY email varchar2(50);

Example-5: Modifying table constraint

How you can create constraints are shown in the previous example. How you can add, delete or modify constraint are shown in this example.

Modifying constraint:

ALTER TABLE customers
MODIFY name
CONSTRAINT name_ck NOT NULL;

Adding constraint:

ALTER TABLE sales
ADD CONSTRAINT sales_amount_ck CHECK (amount > 0);

Deleting constraint:

ALTER TABLE sales
DROP CONSTRAINT sales_amount_ck;

Example-6: Rename table

RENAME statement is used to change the name of an existing table. The following statement will change the table name from ‘customers3’ to ‘new_customer’.

RENAME customers3 to new_customer;

Example-7: Drop table

DROP statement permanently deletes any table from the database.

DROP TABLE products;

Example-8: Truncate table

TRUNCATE statement is used to make any table empty.

TRUNCATE TABLE employees;

Example-9: create and drop the index

INDEX column is used to set the column data in order for making searching faster. How you can organize column data in ascending or decending order or delete any existing index are shown in this example.

Creating an index in default order:

CREATE INDEX cust_name_ix on customers(name);

Creating an index in descending order:

CREATE INDEX amt_ix on sales(amount DESC);

Deleting index:

DROP INDEX cust_name_ix;

Example-10: create a sequence

You can create an auto-generated numeric column by creating a sequence in Oracle 12c. The following statement will create a sequence named book_sq that will start from 1.

CREATE SEQUENCE book_sq
START with 1;

Book_sq sequence is used in the following create table statement to make id field auto-generated.

create table books
(
id number(4) default book_seq.nextval primary key,
book_name varchar2(50),
author_name varchar2(50)
);

Conclusion

Hope, the tutorial will help the readers to learn the process for creating or modifying table on Oracle 12c.

Check the following video link for more details.