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 Primary Key Constraint

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

  1. SBH

    SBH Forum Expert

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

    Data Integrity is one of the important dimension to be maintained in database. The article explains the implementation and use of Primary key constraint in Oracle.

    2. Introduction

    Primary key is one of the data integrity constraints which technically combine the UNIQUE and NOT NULL constraints. It does not allow the column value to be NULL or get duplicated.

    1. A table can have only one primary key.
    2. Multiple columns can be clubbed under composite primary key.
    3. Oracle internally creates a unique index to prevent duplication in the column values.

    [​IMG]

    3. Syntax

    Column level:
    Code (Text):
    COLUMN [data type] [CONSTRAINT <constraint name> PRIMARY KEY]
    Table level:

    Code (Text):
    CONSTRAINT [constraint name] PRIMARY KEY [column (s)]
    4. Examples

    4.1. The following example shows how to use PRIMARY KEY constraint at column level.

    Code (SQL):
    CREATE TABLE TEST
    ( ID  NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
      ...  );
    4.2. The following example shows how to define composite primary key using PRIMARY KEY constraint at the table level.

    Code (SQL):
    CREATE TABLE TEST
     ( ...,
       CONSTRAINT TEST_PK PRIMARY KEY (ID)
     );
    Constraints on a table can be queried from the dictionary view [ALL | DBA | USER]_CONSTRAINTS and USER_CONS_COLUMNS.

    Below query lists the constraints contained by the table, their associated indexes (if any) and status (ENABLED or DISABLED).

    Code (SQL):
    SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, INDEX_NAME , STATUS
    FROM USER_CONSTRAINTS  
    WHERE TABLE_NAME = '<TABLE_NAME>';
    5. Notes

    1. Primary Key can be added/disabled/enabled using ALTER TABLE command. Single r Column or multiple columns can be specified in the syntax. In this case, constraint name is generated by the Oracle server in 'SYS_*' form.

    Below ALTER command adds the primary key to a table

    Code (SQL):
    ALTER TABLE [TABLE NAME]
    ADD PRIMARY KEY(COLUMN NAME)
    Below ALTER command syntax enables/disables the primary key of a table

    Code (SQL):
    ALTER TABLE [TABLE NAME]
    [ENABLE | DISABLE] PRIMARY KEY
    Disabling the primary key does the below changes
    • Primary key constraint is ineffective
    • Oracle created unique Index is dropped
    • Status is DISABLED in USER_CONSTRAINTS dictionary view

    2. Primary key can be dropped using ALTER TABLE command. It drops the constraint and associated index.

    Code (SQL):
    ALTER TABLE
    DROP RPIMARY KEY
    An index can be associated to a primary key using ‘USING INDEX’ clause in the ALTER table.

    Code (SQL):
    ALTER TABLE [TABLE NAME]
    ADD CONSTRAINT [CONSTRAINT NAME] PRIMARY KEY(col_1)  
    USING INDEX [INDEX AME];  
    An Index can be re-associated to an existing Primary key by disabling it and associating an index while enabling it.

    Code (SQL):
    ALTER TABLE [TABLE NAME]
    ENABLE CONSTRAINT [CONSTRAINT NAME]
    USING INDEX [INDEX NAME];
    A non unique index can also be associated with a PK. Bitmap indexes cannot be associated with PK.
    If a Primary key is dropped, explicitly associated indexes are not dropped. However, if the index is implicitly created by the oracle server during the creation of Primary Key constraint, it would be dropped during dropping and disabling of primary key. It can be retained using KEEP INDEX clause.
     

    Attached Files: