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 Oracle 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