ALTER TABLE constraints

Change the Constraints and Primary Key for an existing table.

Syntax:

   ALTER TABLE [schema.]table
      constraint_clause [,…]
          [ENABLE enable_clause | DISABLE disable_clause]
              [{ENABLE|DISABLE} TABLE LOCK]
                 [{ENABLE|DISABLE} ALL TRIGGERS];

constraint_clause:
   ADD out_of_line_constraint(s)
   ADD out_of_line_referential_constraint
   DROP PRIMARY KEY [CASCADE] [{KEEP|DROP} INDEX]
   DROP UNIQUE (column [,…]) [{KEEP|DROP} INDEX]
   DROP CONSTRAINT constraint [CASCADE]
   MODIFY CONSTRAINT constraint constrnt_state
   MODIFY PRIMARY KEY constrnt_state
   MODIFY UNIQUE (column [,…]) constrnt_state
   RENAME CONSTRAINT constraint TO new_name

constrnt_state:   
    [[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE|DEFERRED}]
       [RELY | NORELY] [USING INDEX using_index_clause]
          [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]
              [EXCEPTIONS INTO [schema.]table]

Examples

Add a column to a table:

   ALTER TABLE STAFF_OPTIONS
      ADD SO_INSURANCE_PROVIDER Varchar2(35);

Add a default value to a column:

   ALTER TABLE STAFF_OPTIONS
      MODIFY SO_INSURANCE_PROVIDER Varchar2(35) DEFAULT 'ABC Ins';

Add two columns to a table and remove a constraint:

   ALTER TABLE STAFF_OPTIONS
      ADD (SO_STAFF_ID INT, SO_PENSION_ID INT)
          STORAGE INITIAL 10 K
          NEXT 10 K
          MAXEXTENTS 121
          PCTINCREASE 0
          FREELISTS 2
      DROP CONSTRAINT cons_SO;

"For madam, said Sir Launcelot, I love not to be constrained to love;
for love must arise of the heart, and not by no constraint." ~ Le Morte D'Arthur

Related Commands:

ANALYZE TABLE COMPUTE STATISTICS
ALTER INDEX
ALTER VIEW
COMMENT - Add a comment to a table or a column.
RENAME
DBMS_REDEFINITION

Related Views:

  DBA_ALL_TABLES       ALL_ALL_TABLES       USER_ALL_TABLES
  DBA_CONSTRAINTS      ALL_CONSTRAINTS      USER_CONSTRAINTS
  DBA_CONS_COLUMNS     ALL_CONS_COLUMNS     USER_CONS_COLUMNS
  DBA_TABLES           ALL_TABLES           USER_TABLES         TAB
  DBA_TAB_COLUMNS      ALL_TAB_COLUMNS      USER_TAB_COLUMNS
  DBA_PART_TABLES      ALL_PART_TABLES      USER_PART_TABLES


© Copyright SS64.com 1999-2013
Some rights reserved