Skip to content

Oracle: Creating Tables

In Oracle, CREATE TABLE statement is used to create a new table in the database.

To create a table, you have to name that table and define its columns and datatype for each column.

Syntax:

CREATETABLE table_name  

(   

column1 datatype [ NULL | NOT NULL ],  

column2 datatype [ NULL | NOT NULL ],  

…  

column_n datatype [ NULL | NOT NULL ]  

);  

Parameters used in syntax

  • table_name: It specifies the name of the table which you want to create.
  • column1, column2, … column n: It specifies the columns which you want to add in the table. Every column must have a datatype. Every column should either be defined as “NULL” or “NOT NULL”. In the case, the value is left blank; it is treated as “NULL” as default.

Oracle CREATE TABLE Example

Here we are creating a table named customers. This table doesn’t have any primary key.

CREATETABLE customers

( customer_id number(10) NOT NULL,

customer_name varchar2(50) NOT NULL,

city varchar2(50)

);

This table contains three columns

  • customer_id: It is the first column created as a number datatype (maximum 10 digits in length) and cannot contain null values.
  • customer_name: it is the second column created as a varchar2 datatype (50 maximum characters in length) and cannot contain null values.
  • city: This is the third column created as a varchar2 datatype. It can contain null values.

Oracle CREATE TABLE Example with primary key

CREATETABLE customers

( customer_id number(10) NOT NULL,

customer_name varchar2(50) NOT NULL,

city varchar2(50),

CONSTRAINT customers_pk PRIMARY KEY (customer_id)

);

A primary key is a single field or combination of fields that contains a unique record. It must be filled. None of the field of primary key can contain a null value. A table can have only one primary key.

Advertisements
Advertisements
Advertisements
, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

2 Comments »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: