Oracle как создать таблицу

Oracle / PLSQL: CREATE TABLE Statement

This Oracle tutorial explains how to use the Oracle CREATE TABLE statement with syntax, examples, and practice exercises.

Description

The Oracle CREATE TABLE statement allows you to create and define a table.

Syntax

The syntax for the CREATE TABLE statement in Oracle/PLSQL is:

Parameters or Arguments

Example

Let’s look at an Oracle CREATE TABLE example.

This Oracle CREATE TABLE example creates a table called customers which has 3 columns.

• The first column is called customer_id which is created as a number datatype (maximum 10 digits in length) and can not contain null values.
• The second column is called customer_name which is a varchar2 datatype (50 maximum characters in length) and also can not contain null values.
• The third column is called city which is a varchar2 datatype but can contain null values.

Now the only problem with this Oracle CREATE TABLE statement is that you have not defined a primary key for the table. We could modify this CREATE TABLE statement and define the customer_id as the primary key as follows:

Practice Exercise #1:

Create an Oracle table called suppliers that stores supplier ID, name, and address information.

Solution for Practice Exercise #1:

The Oracle CREATE TABLE statement for the suppliers table is:

Practice Exercise #2:

Create an Oracle table called customers that stores customer ID, name, and address information.

But this time, the customer ID should be the primary key for the table.

Solution for Practice Exercise #2:

The Oracle CREATE TABLE statement for the customers table is:

Practice Exercise #3:

Based on the departments table below, create an Oracle table called employees that stores employee number, employee name, department, and salary information. The primary key for the employees table should be the employee number. Create a foreign key on the employees table that references the departments table based on the department_id field.

Oracle CREATE TABLE

Summary: in this tutorial, you will learn how to use the Oracle CREATE TABLE statement to create a new table in the Oracle database.

Introduction to Oracle CREATE TABLE statement

To create a new table in Oracle Database, you use the CREATE TABLE statement. The following illustrates the basic syntax of the CREATE TABLE statement:

• 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.

Note that you must have the CREATE TABLE system privilege to create a new table in your schema and CREATE ANY TABLE system privilege to create a new table in another user’s schema. On top of this, the owner of the new table must have the quota for the tablespace that contains the new table or UNLIMITED TABLESPACE system privilege.

Oracle CREATE TABLE statement example

The following example shows how to create a new table named persons in the ot schema:

In this example, the persons table has three columns: person_id , first_name , and last_name .

The person_id is the identity column that identifies unique rows in the table. The data type of the person_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. It means that you cannot insert a first name whose length is greater than 50 into the first_name column. Besides, the NOT NULL column constraint prevents the first_name column to have NULL values.

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

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

In this tutorial, you have learned how to use the Oracle CREATE TABLE statement to create a new table.

how to create table in oracle

This article talks about how to create table in Oracle, primary key, Foreign keys, create table syntax in oracle with Examples. This will be very useful for Oracle DBAs and Developer both. They play with it many times in the day and good knowledge can definitely help them expedite the task. They often get confused about the datatype and what to use in what circumstances. Here I am trying to give an overview of all the useful stuff for Oracle database tables

Please enable JavaScript

Table of Contents

What is Oracle database Table?

-Tables are the basic unit of data storage in an Oracle Database. Data is stored in rows and columns.

-A table holds all the necessary about something in the real world

-A table contains a set of columns. A column represents one kind of data in the table, For example, the salary column in the EMP table will have the salaries

• A row is a collection of column information corresponding to a single record.

Next, we will be talking in detail about Oracle create table statement

How to create table in oracle

To create a table in the database, we must have the following information

• The table name
• Table type
• Constraints
• table storage parameter

Let’s look at each of these in details

Table Naming Conventions for Oracle create table

• The name you choose for a table must follow these standard rules:
• The name must begin with a letter A-Z or a-z
• Can contain numbers and underscores
• Can be in UPPER of lower case
• Can be up to 30 characters in length. With 12.2, it has been extended to 128 characters
• Cannot use the same name of another existing object in your schema
• Must not be a Oracle server and SQL reserved word
• Column name, column data types, and column sizes.

Column Naming Conventions –

• The name you choose for a column must follow these standard rules:
• The name must begin with a letter A-Z or a-z
• Can contain numbers and underscores
• Can be in UPPER of lower case
• Can be up to 30 characters in length.With 12.2, it has been extended to 128 characters
• Cannot use the same name of another existing object in your schema
• Must not be a Oracle server and SQL reserved word

Type of table

Ordinary (heap-organized) table

-This is the basic, general purpose type of table.

-Its data is stored as an un-ordered collection (heap)

Clustered table

-A clustered table is a table that is part of a cluster.

-A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.

Index-organized table

-Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner.

-Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the non-key column values as well.

Partitioned table

–Oracle Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even sub-partitions.

• Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.

External Table

Oracle External tables allow Oracle to query data that is stored outside the database in flat files.

Global temporary table

The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction

Constraints and Rules

You can specify rules for each column of a table. These rules are called integrity constraints. One such example is a not null integrity constraint. This constraint forces the column to contain a value in every row. These rules are enforced placed for each column or set of columns. Whenever the table participates in data action, these rules are validated and raise exceptions upon violation.

A constraint can be one of the following:

• a column-level constraint

Column-level constraints refer to a single column in the table and do not specify a column name (except check constraints). They refer to the column that they follow.

• a table-level constraint

Table-level constraints refer to one or more columns in the table. Table-level constraints specify the names of the columns to which they apply. Table-level CHECK constraints can refer to 0 or more columns in the table.

The available constraint types are NOT NULL, Primary Key, Unique, Check, and Foreign Key.

Primary Key

A primary key in Oracle is a column in a table whose values uniquely identify the row in the table. A primary key value:

a) Must uniquely identify the row;

b) cannot have NULL values;

Oracle internally creates a unique oracle index to prevent duplication in the column values. It can be defined at the column or table level

A primary key can be multiple columns also

Unique Key

Unique key in Oracle means uniqueness for the column. Oracle server will not allow duplicate values in the column having unique constraints. Oracle internally creates a unique index to prevent duplication in the column values. But it allows some values to be null. It can be defined at the column or table level

Foreign Key

-A foreign key in Oracle is a referential constraint between two tables.

-A foreign key constraint validates the values of an INSERT or UPDATE against the values in another column, either in a different table or another column in the same

-A foreign key always defines a parent/child relationship. The “parent” is the column that is referenced in the foreign key and the “child” is the column or columns that contain the foreign key constraint.

-Generally, though, a foreign key is a field (or fields) that points to the primary key of another table.
-It can be defined at the column or table level

Check constraint

An oracle check constraint requires a value in the database to comply with a specified condition. Check constraint allows imposing a conditional rule on a column, which must be validated before data is inserted into the column. The condition must not contain a subquery or pseudo column CURRVAL NEXTVAL, LEVEL, ROWNUM, or SYSDATE.

Oracle allows a single column to have more than one CHECK constraint. In fact, there is no practical limit to the number of CHECK constraints that can be defined for a column.

It can be defined at the column or table level

Not Null

It means that a data row must have a value for the column specified as NOT NULL. The Oracle server will not allow rows to be stored that violate this constraint. It can only be defined at the column level, and not at the table level.

Table storage parameter

Tables are stored in Oracle Tablespace in the database. If no Tablespace is specified, the table goes in user default Tablespace.

So in nutshell

1)You need to choose the appropriate column name and table name as per the standard given above. It is recommended to give column name and table name such that you can identify the purpose by checking the names

2) Secondly you have to choose the right Oracle data type for the table.
If it is a character string, we should be choosing VARCHAR2 (10). This is a variable string, we should choose the value appropriately. This means it can stores characters to 10 bytes. The space utilized would be depending on the values in the column and there will be no wastage of space.

I would stress here one thing that when we give VARCHAR2(10) here 10 is the number of the bytes, not the character.
Number of character and bytes are similar when ASCII character is involved,but the equation get changed we start using character other than ASCII

Right now VARCHAR2 supports 4000 bytes, if your column is bigger than that, you can use LOB datatype, which can stores text for GIGA bytes.

If it is a number, we use the datatype Number. Again we can choose the value appropriately. Example number(6,2) This can contain a maximum from 999999.99

If you want to store date, Oracle has provided date datatype for storage.

3) Identify the constraint on the table. Decide what will be the primary key and what all columns would be not null.

Once we have all the required information, we can move forward with table creation

Oracle create table Syntax

SYNTAX for oracle create Table primary key. It can be both defined at column level or table level

Example of oracle create Table primary key

Oracle create index to enforce the primary key constraints

The explanation for table TEST1

SYNTAX for oracle create Table statement FOREIGN Key. It can be both defined at column level or table level

Explanation for table EMP

Privilege required to oracle create table

-You must have the create table system privilege in order to create a new table in your schema,

• You must have the create any table system privilege in order to create a table in another user’s schema, additionally, the owner of the table must have a quota for the tablespace that contains the table, or the UNLIMITED TABLESPACE system privilege

Other characteristics associated with oracle database table

Cache/no-cache

Use the CACHE clauses to indicate how Oracle Database should store blocks in the buffer cache. If you don’t specify anything in create table command,it is by default no-cache

DEFAULT

The value inserted into the column if the insert or update would leave the column value NULL.

<DEFAULT <value> | NULL>

PARALLEL

Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.

Compress

This is used by the oracle to store the table in compressed format. This is available from 11g onwards

Comment a table or column

You can comment on the table using the command

Oracle create table with default value

Sometimes you want to assign a default value to the column if it is not specified in the insert statement, then you can create the table with the below syntax. DEFAULT is the keyword used

Oracle create table default value changes in 12c

IDENTITY Columns
In Oracle Database 12c, We can define Table columns with SQL keyword IDENTITY which is an American National Standards Institute (ANSI) SQL keyword. Which are auto-incremented at the time of insertion (like in MySQL).

Metadata-Only DEFAULT Values

Before pre, Oracle 11g, adding a new column to an existing table required all rows in that table to be modified to add the new column. So if the table is very large, alter table add column took a substantial time and locking time as also more

With Oracle, 11g introduced the concept of metadata-only default values. Adding a NOT NULL column with a DEFAULT clause to an existing table involved just a metadata change, rather than a change to all the rows in the table. Queries of the new column were rewritten by the optimizer to make sure the result was consistent with the default definition.

Read more about that in below article

Oracle 12c takes this a step further, allowing metadata-only default values of both mandatory and optional columns. As a result, adding a new column with a DEFAULT clause to an existing table will be handled as a metadata-only change, regardless of whether that column is defined as NOT NULL or not.

This is a huge benefit for all.

Data Dictionary Tables and Views

All the table and column information is stored in SYS.TAB$ and SYS.COL$ tables. Oracle has provided data dictionary views to get information about tables and columns

There are three categories of views

How to List All Tables in Oracle

How to determine Table size

Tables with number of rows and comments

The below query can be used to find the count of rows and comments in SCOTT schema

Frequently asked questions on Oracle create table

Oracle does not have create or replace table oracle command. Create or replace is just valid for views, PLSQL: procedures only. You need to do the alteration using ALTER Table command only
Or you can explicitly drop the table and create it again

we don’t have any direct, but you can use PLSQL to emulate that
SET SERVEROUTPUT ON
DECLARE c_emp int:=0;
BEGIN SELECT count(*) into c_emp FROM dba_tables where table_name = ‘EMP’;
if c_emp<=0
EXECUTE IMMEDIATE ‘create table EMP ( ID NUMBER(3), NAME VARCHAR2(30) NOT NULL,SAL NUMBER*,2))’;
end if;
END;
/

Sample Schema to Practice

Hope you like this article on oracle create table statement, command, syntax, and tips.

Related Articles
alter table add column oracle : Useful insight into How to alter table add column oracle. Details about fast add column feature introduced in oracle 11g also given
DROP TABLE ORACLE : Learn about drop table in Oracle, Drop table if exists in Oracle, drop multiple tables in one command, drop table cascade constraints
alter table move : Check out this post for the step by step method on How to rebuild the table in oracle using alter table move, How to rebuild table having lobs, long column
Truncate TABLE Oracle : Truncate TABLE in Oracle is faster than delete from the table in oracle. It is DDL statement and it does not fire the on delete triggers
Alter Table in Oracle : Alter table in oracle is used to modify a column, drop and add constraints, change the data type of the table column, change the table storage parameters
oracle list all tables: we can get the List All Tables in Oracle by either querying all_tables or user_tables or dba_tables. we can select the column and where clause as per the need
Supplemental Logging in Oracle
oracle sql date functions
https://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm
Creating a Global Temporary Table in Oracle

report this ad

CREATE TABLE in Oracle

CREATE TABLE
To create a new table in the database, Oracle provides the Oracle CREATE TABLE statement.

Syntax:

Parameters:

schema_name: It is used to specify the name of the schema to which the new table belongs.
table_name: It is used to specify the table name.
Column Definition: Column1, column2, … column n is used to specify the name of the multiple columns which you want to add in the table. The Oracle database does not allow a total number of columns more than 32. A datatype is a must for each column. The data type of a column can be NUMBER, VARCHAR2, etc. The column constraint mainly defines each column as “NULL” or “NOT NULL”, with the value of “NULL” as default. Some other common column constraints are Primary Key, Check and Foreign Key.

Example 1: Creating a table with NULL and NOT NULL table constraint.

CREATE TABLE students ( id number(10) NOT NULL, name varchar2(40) NOT NULL, class varchar2(10) );

Explanation:
Column_1:
id: It is the name of the first column.
number: It is the datatype of the first column which also specifies a maximum limit of 10 digits in length for the “id”.
NOT NULL: It defines the column constraint of the first column and thus Column 1 cannot contain null values.
Column_2:
name: It is the name of the second column.
varchar: It is the datatype of the second column which also specifies a maximum limit of 40 characters in length for the “name”.
NOT NULL: It defines the column constraint of the second column and thus Column 2 cannot contain null values.
Column_3:
class: It is the name of the third column.
varchar: It is the datatype of the third column which also specifies a maximum limit of 10 characters in length for the “class”.
NULL: There is no column constraint defined for the third column, thus it is NULL by default and thus Column 3 can contain null values.

Example 2: Creating a table with a PRIMARY KEY table constraint.

CREATE TABLE students ( id number(10) NOT NULL, name varchar2(40) NOT NULL, class varchar2(10) PRIMARY KEY(id) );

Explanation:
All the three columns and their definitions are the same as that in example 1. The difference is in the existence of Primary Key in the second example.
The PRIMARY KEY clause is a field or combination of fields which specifies a column as the primary key column. The primary key column is used for distinguishing a unique row in a table. In Oracle, a table can hold only one primary key, and every field of the primary key must contain NOT NULL values. In the above example, “id” is defined as the Primary Key Column.