Oracle Create Table Syntax: 19 Different Examples

advertisements

_____________________________________________________________________________________________________________________

1. Simple syntax for create table

SQL> CREATE TABLE CR_TABLE_EX

       (EMPNO NUMBER(4) ,

        ENAME VARCHAR2(10)); 

Table created.

2. Create table with primary key

SQL> CREATE TABLE CR_TABLE_EX

       (EMPNO NUMBER(4) primary key,

        ENAME VARCHAR2(10));  

Table created.

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

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

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

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

7. Create table with Not Null Constraint

CREATE TABLE CR_TABLE_EX

       (EMPNO NUMBER(4) not null,

        ENAME VARCHAR2(10)); 

Table created.

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

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

10. Create table with Unique Constraint

CREATE TABLE CR_TABLE_EX

       (EMPNO NUMBER(4) Unique ,

        ENAME VARCHAR2(10));

Table created.

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

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

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

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

15. Create Global Temporary tables

 CREATE global temporary TABLE CR_TABLE_EX

       (EMPNO NUMBER(4) ,

        ENAME VARCHAR2(10)) ; 

Table created.

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

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

18. Create table As Select - CTAS

SQL> create table CTAS_test as select * from emp;

Table created.

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

_____________________________________________________________________________________________________________________

Website Stats