How to Create Insert Alter Drop Table in Oracle

Author:

In this post, we will learn how to create, insert, modify, and drop tables in Oracle.

1. Create a Table

Let's use the Oracle CREATE TABLE statement to create a new table in the Oracle database.

Syntax

The following illustrates the basic syntax of the CREATE TABLE statement:

CREATE TABLE schema_name.table_name (
    column_1 data_type column_constraint,
    column_2 data_type column_constraint,
    ...
    table_constraint
 );

In this syntax:

  • First, specify the table name and schema name to which the new table belongs on the CREATE TABLE clause. 
  • Second, list all columns of the table within the parentheses. In case a table has multiple columns, you need to separate them by commas (,). A column definition includes the column name followed by its data type e.g., NUMBER, VARCHAR2, and a column constraint such as NOT NULL, primary key, check. 
  • Third, add table constraints if applicable e.g., primary key, foreign key, check.

Create Table in Oracle Database Example

The following example shows how to create a new table named users in the schema demo:

CREATE TABLE demo.users(
    user_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(user_id)
);

In this example, the users table has three columns: user_id, first_name, and last_name.

The user_id is the identity column that identifies unique rows in the table. The data type of the user_id column is NUMBER. The clause GENERATED BY DEFAULT AS IDENTITY instructs Oracle to generate a new integer for the column whenever a new row is inserted into the table.

The first_name column has data type VARCHAR2 with the maximum length is 50. 

The last_name column has the same characteristics as the first_name column.

The PRIMARY KEY clause specifies the user_id column as the primary key column which is used for identifying the unique row in the users table.

2. Insert Records into Table

Let's use the Oracle INSERT statement to insert data into a table.

Syntax

To insert a new row into a table, you use the Oracle INSERT statement as follows:

  INSERT INTO table_name (column_list)
 VALUES ( value_list);

If the value list has the same order as the table columns, you can skip the column list although this is not considered as a good practice:

INSERT INTO table_name
VALUES (value_list);

If you exclude one or more columns from the Oracle INSERT statement, then you must specify the column list because Oracle needs it to match with values in the value list.

Insert Data into Table in Oracle 


Let’s create a new table named users for inserting data:

CREATE TABLE demo.users(
    user_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(user_id)
);

In the users table, the user_id column is an identity column whose default value is automatically generated by the system, therefore, you don’t have to specify the user_id column in the INSERT statement.

The other columns first_name and last_name the NOT NULL columns, so you must supply the values for them.

The following statement inserts a new row into the discounts table:

INSERT INTO demo.users(first_name , last_name) VALUES('Ramesh', 'Fadatare');

3. Oracle ALTER TABLE

Let's use the Oracle ALTER TABLE statement to modify the table structure.

Syntax

The following illustrates the syntax:

ALTER TABLE table_name action;

In this statement:

First, specify the table name which you want to modify. 

Second, indicate the action that you want to perform after the table name. 

The ALTER TABLE statement allows you to:

  • Add one or more columns
  • Modify column definition
  • Drop one or more columns
  • Rename columns
  • Rename table

Add new Column to Existing Table in Oracle Example

To add a new column to a table, you use the following syntax:

ALTER TABLE table_name
ADD column_name type constraint;

For example, the following statement adds a new column named age to the users table:

ALTER TABLE users 
ADD age number NOT NULL;

To add multiple columns to a table at the same time, you place the new columns inside the parenthesis as follows:

ALTER TABLE table_name
ADD (
    column_name type constraint,
    column_name type constraint,
    ...
);

See the following example:

ALTER TABLE users 
ADD (
    phone VARCHAR(20),
    email VARCHAR(100)
);

Remove Column from Existing Table Example

To remove an existing column from a table, you use the following syntax:

ALTER TABLE table_name
DROP COLUMN column_name;

This statement deletes the column from the table structure and also the data stored in that column.

The following example removes the age column from the users table:

ALTER TABLE users
DROP
  COLUMN age;

To drop multiple columns at the same time, you use the syntax below:

ALTER TABLE table_name 
DROP (column_1,column_2,...);

For example, the following statement removes the phone and email columns from the users table:

ALTER TABLE users
DROP
  ( email, phone );

Rename Existing Table in Oracle Example

To give a table a new name, you use the following syntax:

ALTER TABLE table_name
RENAME TO new_table_name;

For example, the statement below renames the users table to users table:

ALTER TABLE users RENAME TO persons;

4. Drop/Delete Table in Oracle

Let's use the Oracle DROP TABLE statement to remove an existing table.

Syntax


DROP TABLE schema_name.table_name
[CASCADE CONSTRAINTS | PURGE];

Drop Existing Table in Oracle Database Example

Let's create a simple users table to demonstrate the usage of the DROP TABLE statement:

CREATE TABLE users (
    user_id NUMBER,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50) NOT NULL,
    PRIMARY KEY(user_id)
);

The following example drops the users table from the database:

DROP TABLE users;

Comments

Post a Comment

Leave Comment