DBA가 되고 싶은 병아리
constraints,테이블 변경 본문
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;
PK와 FK는 변경이 불가능..
출처 : http://ss64.com/ora/table_a_cons.html
'Oracle Study > SQL' 카테고리의 다른 글
테이블 구조를 알아보자. (0) | 2012.03.13 |
---|---|
Create Table 형식... (0) | 2012.03.13 |
user 재정의 하는 방법 (0) | 2012.03.05 |
오라클 단일행 함수 (0) | 2012.03.03 |
Oracle Data Type의 종류? (0) | 2012.03.03 |