How to alter Table in Oracal

[Japesh]

ALTER TABLE helps in programmatically modifies the structure of a table.
It has the following syntax :

ALTER TABLE TableName1 ADD | ALTER [COLUMN] FieldName1 FieldType

   [(nFieldWidth [, nPrecision])] [NULL | NOT NULL]

   [CHECK lExpression1 [ERROR cMessageText1]] [DEFAULT eExpression1]

   [PRIMARY KEY | UNIQUE] [REFERENCES TableName2 [TAG TagName1]]

   [NOCPTRANS] [NOVALIDATE]

-or-

ALTER TABLE TableName1 ALTER [COLUMN] FieldName2 [NULL | NOT NULL]

[SET DEFAULT eExpression2] [SET CHECK lExpression2 [ERROR cMessageText2]]

[DROP DEFAULT] [DROP CHECK] [NOVALIDATE]

-or-

ALTER TABLE TableName1 [DROP [COLUMN] FieldName3]

   [SET CHECK lExpression3[ERRORcMessageText3]] [DROP CHECK]

   [ADD PRIMARY KEY eExpression3 [FOR lExpression4] TAG TagName2]

   [DROP PRIMARY KEY]

   [ADD UNIQUE eExpression4 [[FOR lExpression5] TAG TagName3]]

   [DROP UNIQUE TAG TagName4] [ADD FOREIGN KEY [eExpression5]

      [FOR lExpression6] TAG TagName4 REFERENCES TableName2

      [TAG TagName5]]

   [DROP FOREIGN KEY TAG TagName6 [SAVE]]

   [RENAME COLUMN FieldName4 TO FieldName5] [NOVALIDATE]

[Japesh]

The following statement places in ENABLE VALIDATE state an integrity constraint named FK_DEPTNO in the EMP table:

ALTER TABLE emp

   ENABLE VALIDATE CONSTRAINT fk_deptno

   EXCEPTIONS INTO except_table;

Each row of the EMP table must satisfy the constraint for Oracle to enable the constraint. If any row violates the constraint, the constraint remains disabled. Oracle lists any exceptions in the table EXCEPT_TABLE. You can also identify the exceptions in the EMP table with the following statement:

SELECT emp.*

   FROM emp e, except_table ex

   WHERE e.row_id = ex.row_id

      AND ex.table_name = 'EMP'

      AND ex.constraint = 'FK_DEPTNO';

ENABLE NOVALIDATE Example :

The following statement tries to place in ENABLE NOVALIDATE state two constraints on the EMP table:

ALTER TABLE emp

   ENABLE NOVALIDATE UNIQUE (ename)

   ENABLE NOVALIDATE CONSTRAINT nn_ename;

This statement has two ENABLE clauses:

The first places a unique constraint on the ENAME column in ENABLE NOVALIDATE state.

The second places the constraint named NN_ENAME in ENABLE NOVALIDATE state.

In this case, Oracle enables the constraints only if both are satisfied by each row in the table. If any row violates either constraint, Oracle returns an error and both constraints remain disabled.

[Japesh]

DISABLE Example :

Consider a referential integrity constraint involving a foreign key on the combination of the AREACO and PHONENO columns of the PHONE_CALLS table. The foreign key references a unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table. The following statement disables the unique key on the combination of the AREACO and PHONENO columns of the CUSTOMERS table:

ALTER TABLE customers

   DISABLE UNIQUE (areaco, phoneno) CASCADE;

The unique key in the CUSTOMERS table is referenced by the foreign key in the PHONE_CALLS table, so you must use the CASCADE clause to disable the unique key. This clause disables the foreign key as well.

CHECK Example :

The following statement defines and disables a CHECK constraint on the EMP table:

ALTER TABLE emp

   ADD (CONSTRAINT check_comp CHECK (sal + comm <= 5000) )

   DISABLE CONSTRAINT check_comp;

The constraint CHECK_COMP ensures that no employee's total compensation exceeds $5000. The constraint is disabled, so you can increase an employee's compensation above this limit.


Triggers Example :

The following statement enables all triggers associated with the EMP table:

ALTER TABLE emp

   ENABLE ALL TRIGGERS;

DEALLOCATE UNUSED Example :

The following statement frees all unused space for reuse in table EMP, where the high water mark is above MINEXTENTS:

ALTER TABLE emp

    DEALLOCATE UNUSED;

[Japesh]

DROP COLUMN Example :

This statement illustrates the drop_column_clause with CASCADE CONSTRAINTS. Assume table T1 is created as follows:

CREATE TABLE t1 (

   pk NUMBER PRIMARY KEY,

   fk NUMBER,

   c1 NUMBER,

   c2 NUMBER,

   CONSTRAINT ri FOREIGN KEY (fk) REFERENCES t1,

   CONSTRAINT ck1 CHECK (pk > 0 and c1 > 0),

   CONSTRAINT ck2 CHECK (c2 > 0)

);

An error will be returned for the following statements:

ALTER TABLE t1 DROP (pk); -- pk is a parent key

ALTER TABLE t1 DROP (c1);  -- c1 is referenced by multicolumn

                           constraint ck1

Submitting the following statement drops column PK, the primary key constraint, the foreign key constraint, RI, and the check constraint, CK1:

ALTER TABLE t1 DROP (pk) CASCADE CONSTRAINTS;

If all columns referenced by the constraints defined on the dropped columns are also dropped, then CASCADE CONSTRAINTS is not required. For example, assuming that no other referential constraints from other tables refer to column PK, then it is valid to submit the following statement without the CASCADE CONSTRAINTS clause:

ALTER TABLE t1 DROP (pk, fk, c1);