1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle CREATE TABLE, ALTER TABLE and DROP TABLE Commands

Discussion in 'SQL PL/SQL' started by SBH, Nov 8, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    Tables are the data blocks to store data in database. The article explains the CREATE TABLE command to create a table, illustrates the use of ALTER command and DROP command

    2. Introduction

    In Oracle, tables are created using CREATE TABLE command. It is a DDL command which creates a Relational Table or an Object Table. Relational Table is a heap table which holds the record in a relational format. Object table is the one which is created on top of an object type and holds the instances of that object.

    3. Prerequisite

    The user attempting to create a table in the schema must have CREATE TABLE system privilege. Insufficient privilege to create a table would raise an exception ORA-01031.
    If the user wishes to create the table in another user’s schema, he must possess CREATE ANY TABLE system privilege.

    Syntax
    Code (Text):
    CREATE TABLE [schema.]table
              ( { column datatype [DEFAULT expr] [column_constraint] ...
                | table_constraint}
             [, { column datatype [DEFAULT expr] [column_constraint] ...
                | table_constraint} ]...)
    DEFAULT is the keyword which allows carrying a default value of a column at table level. If it is ignored during INSERT operation, oracle implicitly inserts its default value in the column. Please note that pseudo columns CURRVAL, NEXTVAL, LEVEL, and ROWNUM cannot be set as default value but SYSDATE or USER can be used for the purpose.

    Constraints are the set of database rules enforced on the column. They may be set at column level or table level. These constraints are validated upon any action on the table columns i.e. DMLs. If a single rule is violated at an instant, Oracle aborts the operation by raising an exception.

    Example

    Below SQL creates a table EMPLOYEE in ORCL schema

    Code (SQL):
    CREATE TABLE ORCL.EMPLOYEE
    (EMPID NUMBER,
     ENAME VARCHAR2(100),
     DEPARTMENT_ID NUMBER,
     SALARY NUMBER,
     JOB_ID VARCHAR2(3)DEFAULT ‘BENCH’);

    TABLE created.
    [​IMG]

    If other user wants to access EMPLOYEE table, he must prefix owner’s name with the table name.

    Code (SQL):
    SELECT  
    ENAME,
    SALARY
    FROM    ORCL.EMPLOYEE
    Default value can be given for a column during Table creation. Please note that this default value is not subject to change for the database and would remain same till it is modified.

    As shown in the above syntax, JOB_ID holds the default value ‘BENCH’. So, while inserting a new employee record, if JOB_ID column is ignored, then Oracle inserts BENCH to the JOB_ID column.

    Code (SQL):
    INSERT INTO EMPLOYEE
    (EMPID, ENAME, DEPARTMENT_ID, SALARY)
    VALUES
    (EMPID.NEXTVAL, ‘NEWBIE’, 10, 1250)

    SELECT EMPID, ENAME, DEPARTMENT_ID, JOB_ID
    FROM EMPLOYEE
    WHERE ENAME = ‘NEWBIE’

    EMPID   ENAME   DEPART  JOB
    ------- --------    ------- ------
    180 NEWBIE  10       BENCH


    1 ROW selected.
    4. ALTER command

    As the name suggests, ALTER command alters the definition of a schema object. ALTER TABLE command is used for several utilities to a table like addition/dropping of columns, renaming a column/table, adding/drop constraints on table columns, modify the column definition, or setting the table read only or revert back.

    [​IMG]

    Syntax:

    Code (Text):
    ALTER TABLE [TABLE NAME]
    [OPERATION]
    Examples

    1. Below command Renames EMPLOYEE table to EMP_NEW

    Code (SQL):
    ALTER TABLE EMPLOYEE
    RENAME TO EMP_NEW;
    2. Below command adds a column NEWCOL to EMPLOYEE table

    Code (SQL):
    ALTER TABLE EMPLOYEE
    ADD (NEWCOL VARCHAR2 (100))
    3. Below command drops the newly added column NEWCOL

    Code (SQL):
    ALTER TABLE EMPLOYEE
    DROP COLUMN NEWCOL;
    4. Below command modified the newly added string column to number

    Code (SQL):
    ALTER TABLE EMPLOYEE
    MODIFY NEWCOL NUMBER;
    5. Below command Renames column NEWCOL to RENCOL

    Code (SQL):
    ALTER TABLE EMPLOYEE
    RENAME COLUMN TESTCOL TO RENCOL
    6. Below command adds primary key constraint to the table

    Code (SQL):
    ALTER TABLE EMPLOYEE
    ADD PRIMARY KEY (EMPLOYEE_ID)
    7. Below command drops the primary key constraint of the table

    Code (SQL):
    ALTER TABLE EMPLOYEE
    DROP PRIMARY KEY
    8. Below command sets the EMPLOYEE table READ ONLY

    Code (SQL):
    ALTER TABLE EMPLOYEE
    READ ONLY
    5. DROP

    It is a DDL command which is used to drop the schema objects from the database. DROP TABLE is used to drop the tables from the database. Once dropped, the table cannot be queried or acted upon and is moved from schema to Oracle Recyclebin (After Oracle 10g).

    Syntax:
    Code (Text):
    DROP TABLE [TABLE NAME] [PURGE]
    Table can be retrieved back from the recyclebin using FLASHBACK utility.
    Recyclebin can be purged to remove the table permanently from the database and space is released back to the tablespace. This is done using PURGE command.

    Dropping a table drops the index and triggers associated with it.
     

    Attached Files:

  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    To drop constraint other than primary key.
    Code (SQL):
    ALTER TABLE EMPLOYEE DROP CONSTRAINT CONSTRAINT_NAME;
    we get constraint name from user_constraints table, we get it by querying:
    Code (SQL):
    SELECT CONSTRAINT_NAME, TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TABLE_NAME';
    Add a constraint other than primary key:
    Code (SQL):
    ALTER TABLE TABLE_NAME ADD(COL1 UNIQUE, COL2 .....);
    To drop multiple columns:
    Code (SQL):
    ALTER TABLE EMPLOYEE DROP (COL1, COL2,...);