Oracle Create Table Syntax: 19 Different Examples


  1. Simple syntax for create table
SQL> CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10)); 

Table created.

  1. Create table with primary key

SQL> CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) primary key,
        ENAME VARCHAR2(10));  

Table created.

  1. Create table with Named Primary Key

CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10),
  constraint Pk_table_ex Primary Key (empno) );

Table created.

  1. Create table with Composite Primary Key - Multiple columns in primary key

SQL> CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10),
  constraint Pk_table_ex Primary Key (empno, ename) );  

Table created.


  1. Create table with Foreign Key Constraint
SQL> CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10),
        DEPTNO NUMBER(2) REFERENCES DEPT(DEPTNO)); 

Table created.

  1. Create table with Named Foreign Key Constraint
SQL> CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10),
        DEPTNO NUMBER(2),
CONSTRAINT FK_CR_TABLE_EX FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)); 

Table created.

  1. Create table with Not Null Constraint
CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) not null,
        ENAME VARCHAR2(10)); 

Table created.

  1. Create table with Named Not Null Constraint

CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) CONSTRAINT NN_CR_TABLE_EX NOT NULL,
        ENAME VARCHAR2(10));

Table created.

  1. Create Table with Check constraint
CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10),
CONSTRAINT CHK_CR_TABLE_EX CHECK (EMPNO BETWEEN 10 AND 1000))

Table created.


  1. Create table with Unique Constraint

CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) Unique ,
        ENAME VARCHAR2(10));

Table created.

  1. Create table with Named Unique Key Constraint
CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10),
CONSTRAINT UK_CR_TABLE_EX UNIQUE (empno));

Table created.

  1. Create table with Multiple Constraints
CREATE TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) not null,
        ENAME VARCHAR2(10),
     DEPTNO NUMBER(2),
CONSTRAINT UK_CR_TABLE_EX UNIQUE (empno),
CONSTRAINT CHK_CR_TABLE_EX CHECK (EMPNO BETWEEN 10 AND 1000),
CONSTRAINT FK_CR_TABLE_EX FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));

Table created.


  1. Create Table With Storage, Pctfree, Pctincrease, Tablespace, Multiple Constraints

  CREATE TABLE STHOMAS.CR_TABLE_EX
   (    EMPNO NUMBER(4,0) NOT NULL ENABLE,
        ENAME VARCHAR2(10),
        DEPTNO NUMBER(2,0),
         CONSTRAINT CHK_CR_TABLE_EX CHECK (EMPNO BETWEEN 10 AND 1000) ENABLE,
         CONSTRAINT UK_CR_TABLE_EX UNIQUE (EMPNO)
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE USERS  ENABLE,
         CONSTRAINT FK_CR_TABLE_EX FOREIGN KEY (DEPTNO)
          REFERENCES STHOMAS.DEPT (DEPTNO) ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE USERS

Table created.

  1. Create Index Organized Table (IOT) table

CREATE TABLE CR_IOT_TABLE_EX
       (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10),
CONSTRAINT PK_iot_Key primary key (empno, ename))
organization index;

Table created.

  1. Create Global Temporary tables

 CREATE global temporary TABLE CR_TABLE_EX
       (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10)) ; 

Table created.


  1. Create External Table

create table CR_EXT_TABLE (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10))
organization external (
  type oracle_loader
  default directory exp_dir
  access parameters (
    records delimited  by newline
    fields  terminated by ','
    missing field are values null
  )
  location ('datafile.csv')
)
reject limit unlimited;

Table created.


  1. Create Nested Tables

SQL> create or replace type emp as object (EMPNO NUMBER(4) ,
        ENAME VARCHAR2(10)); 
 /

Type created.

SQL> create table Organization(
 org_id number (2),
 org_name varchar2(30),
 org_emp emp);

Table created.



  1. Create table As Select - CTAS


SQL> create table CTAS_test as select * from emp;

Table created.


  1. Create Table Structure Only From Existing Table With CTAS

SQL> create table CTAS_test as select * from emp where 1=2;

Table created.

SQL> select count(*) from CTAS_test;

  COUNT(*)
----------
         0

SQL> select count(*) from emp;

  COUNT(*)
----------

        14
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google