Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 30-03-2009
Member
 
Join Date: Oct 2008
Posts: 20
How to alter Table in Oracal

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

Code:
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-

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

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

[DROP DEFAULT] [DROP CHECK] [NOVALIDATE]
-or-

Code:
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]
Reply With Quote
  #2  
Old 30-03-2009
Member
 
Join Date: Oct 2008
Posts: 20
Re: How to alter Table in Oracal

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

Code:
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:

Code:
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:


Code:
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.
Reply With Quote
  #3  
Old 30-03-2009
Member
 
Join Date: Oct 2008
Posts: 20
Re: How to alter Table in Oracal

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:

Code:
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:

Code:
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:
Code:
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:

Code:
ALTER TABLE emp

    DEALLOCATE UNUSED;
Reply With Quote
  #4  
Old 30-03-2009
Member
 
Join Date: Oct 2008
Posts: 20
Re: How to alter Table in Oracal

DROP COLUMN Example :

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

Code:
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:

Code:
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:

Code:
ALTER TABLE t1 DROP (pk, fk, c1);
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to alter Table in Oracal"
Thread Thread Starter Forum Replies Last Post
Unable to work with table columns in opera browser when the table has wide layout Janya Technology & Internet 5 27-08-2011 10:53 AM
Link a Table to another Table to Drop Down In Main Table himeshRES Windows Software 6 11-12-2010 02:01 PM
Colonizing a table of employees from a table of applicants in Microsoft Access laplapye Windows Software 4 30-11-2010 03:01 AM
To convert a pivot table to a flattened table in MS Excel zeemga Windows Software 3 27-11-2010 06:48 AM
Bitmap Index for Oracal 10g Deeptimoy Software Development 2 07-02-2009 11:58 AM


All times are GMT +5.5. The time now is 12:54 AM.