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
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
CREATE TABLE table_name ( col1 datatype [ NULL | NOT NULL ], col2 datatype [ NULL | NOT NULL ], ... col_n datatype [ NULL | NOT NULL ] ) tablespace <tablepace name>;
SYNTAX for oracle create Table primary key. It can be both defined at column level or table level
Table level CREATE TABLE table_name ( col1 datatype [ NULL | NOT NULL ], col2 datatype [ NULL | NOT NULL ], ... col_n datatype [ NULL | NOT NULL ] constraint <name> primary key (col1,col2) ) tablespace <tablepace name> Column Level CREATE TABLE table_name ( col1 datatype [ NULL | NOT NULL ] constraint <name> primary key , col2 datatype [ NULL | NOT NULL ], ... col_n datatype [ NULL | NOT NULL ] ) tablespace <tablepace name>
Example of oracle create Table primary key
CREATE TABLE SCOTT.TEST ( Created_by date, SOURCE CHAR(10), REQUEST_ID CHAR(64) NOT NULL CONSTRAINT TEST_PK PRIMARY KEY, COMMENTS VARCHAR(3000) ); CREATE TABLE TESTEXP ( INV_ID CHAR(7) NOT NULL, ITEM_ID CHAR(7) NOT NULL, CREATED date, WHO CHAR(7), CONSTRAINT TEST1_PK PRIMARY KEY (INV_ID,ITEM_ID) );
Oracle create index to enforce the primary key constraints
Related: Oracle interview questions
The explanation for table TEST1
| 1 | The first column is called inv_id which is created as a char datatype (maximum 7 digits in length) and cannot contain null values |
| 2 | The second column is called item_id which is created as a char datatype (maximum 7 digits in length) and cannot contain null values |
| 3 | The third column is called created which is a date datatype and also can contain null values. |
| 4 | The fourth column is called who which is a char datatype and also can contain null values. |
| 5 | Table level primary key constraint TEST1_PK is defined on the composite key (INV_ID, ITEM_ID) |
SYNTAX for oracle create Table statement FOREIGN Key. It can be both defined at column level or table level
CREATE TABLE table_name ( col1 datatype [ NULL | NOT NULL ], col2 datatype [ NULL | NOT NULL ], ... col_n datatype [ NULL | NOT NULL ] constraint <name> FOREIGN KEY (col1,col2) REFERENCES table(col1,col2) ) tablespace <tablepace name> CREATE TABLE table_name ( col1 datatype [ NULL | NOT NULL ] constraint <name> primary key , col2 datatype [ NULL | NOT NULL ], ... col_n datatype [ NULL | NOT NULL ] ) tablespace <tablepace name>; CREATE TABLE dept ( dept_id number(10) NOT NULL, dept_name varchar2(50) NOT NULL, CONSTRAINT dept_pk PRIMARY KEY (dept_id) ); CREATE TABLE emp ( emp_no number(10) NOT NULL, emp_name varchar2(50) NOT NULL, dept_id number(10), sal number(6), CONSTRAINT emp_pk PRIMARY KEY (emp_no), CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES dept(dept_id) );
Explanation for table EMP
| 1 | The first column is called EMP_NO which is created as a number and cannot contain null values |
| 2 | The second column is called emp_name which is created as varchar2(50) and cannot contain null values |
| 3 | The third column is called dept_id which is a number. |
| 4 | The fourth column is called sal which is a number datatype and also can contain null values. |
| 5 | Table level primary key constraint EMP_PK is defined on the key (EMP_NO) |
| 6 | Table level Foreign Key constraints dept_fk which references dept table dept_id |
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
Related:How to write sql queries
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
COMMENT ON TABLE <table_name> IS '<comment>';
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
CREATE TABLE EXP ( NAME VARCHAR2(20), SAL number(8,2) DEFAULT 800, hire_date DATE DEFAULT SYSDATE , birthdate DATE DEFAULT SYSDATE -10 )
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).
Example: create table test ( id number generated as identity, name varchar2(100), email varchar2(100), password varchar2(100), firstname varchar2(100), lastname varchar2(100) );
Sequence as Default Value With Oracle Database 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.It is a sort of auto-increment feature for a column in oracle just like MySQL
Example: create sequence tech_test_seq start with 1 increment by 1 nocycle;create table test ( id number default tech_test_seq.nextval primary key name varchar(30) );
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
How to alter table add column oracle
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
To list all tables owned by the current user, type: select tablespace_name, table_name from user_tables; To list all tables in a database: select tablespace_name, table_name from dba_tables; To list all tables accessible to the current user, type: select tablespace_name, table_name from all_tables To describe the table in SQLPLUS desc <table_name>
How to determine Table size
select owner as "Schema" , segment_name as "Object Name" , segment_type as "Object Type" , round(bytes/1024/1024,2) as "Object Size (Mb)" , tablespace_name as "Tablespace" from dba_segments where segment_name=’<table_name>’;
Tables with number of rows and comments
The below query can be used to find the count of rows and comments in SCOTT schema
select tab.owner as schema_name,
tab.table_name as table_name,
obj.created,
obj.last_ddl_time as last_modified,
tab.num_rows,
tab.last_analyzed,
comm.comments
from all_tables tab
inner join all_objects obj
on obj.owner = tab.owner
and obj.object_name = tab.table_name
left outer join all_tab_comments comm
on tab.table_name = comm.table_name
and tab.owner = comm.owner
where tab.owner = 'SCOTT'
order by tab.owner,
tab.table_name;
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
SQL>CREATE TABLE "DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
)
SQL>CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"),
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "DEPT" ("DEPTNO") ENABLE
);
SQL> desc emp
Name Null? Type
----------------------------------------- -------- -----------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>
SQL> desc dept
Name Null? Type
----------------------------------------- -------- -----------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL>
insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'RESEARCH', 'DELHI');
insert into dept values(40, 'RESEARCH', 'MUMBAI');
insert into emp values( 7698, 'Blake', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp values( 7782, 'Clark', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 7788, 'Scott', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 7789, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, null );
insert into emp values( 7560, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 7790, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, null );
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-07 2850 10
7782 CLARK MANAGER 7839 09-JUN-08 2450 10
7788 SCOTT ANALYST 7566 09-JUN-12 3000 20
7789 TPM ANALYST 7566 09-JUN-17 3000
7790 TOM ANALYST 7567 09-JUL-17 4000
7560 T1OM ANALYST 7567 09-JUL-17 4000 20
After the practice is over, you can drop the tables
SQL>drop table emp;
SQL>drop table dept;
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