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.
ALTER TABLE customers ADD address VARCHAR2(200);
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.
ALTER TABLE customers MODIFY name CONSTRAINT name_ck NOT NULL;
ALTER TABLE sales ADD CONSTRAINT sales_amount_ck CHECK (amount > 0);
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);
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) );
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.