오라클 제약조건 추가/삭제/비활성화/활성화

PK제약조건 생성하기

alter table 유저명.테이블명
add constraint PK제약조건명 primary key(PK컬럼명);

 

FK제약조건 생성하기

alter table 유저명.테이블명
add constraint FK제약조건명 foreign key(FK컬럼)
references FK참조테이블명(FK참조컬럼명) on delete cascade;

 

제약조건 삭제하기

ALTER TABLE 유저명.테이블명 DROP CONSTRAINT 제약조건명;

ALTER TABLE 유저명.테이블명 DROP CONSTRAINT 제약조건명 CASCADE; /*관련 참조관계를 끊고 제약조건 삭제*/

 

제약조건 비활성화

ALTER TABLE 유저명.테이블명 DISABLE CONSTRAINT 제약조건명;

 

제약조건 활성화

ALTER TABLE 유저명.테이블명 ENABLE CONSTRAINT 제약조건명;

ALTER TABLE 유저명.테이블명 ENABLE NOVALIDATE CONSTRAINT 제약조건명; /*기존 데이터에 대한 정합성 체크 없이 제약조건 활성화*/ 

 

제약조건 확인

SELECT OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,STATUS FROM DBA_CONSTRAINTS

 

제약조건 확인 결과

OWNER  |CONSTRAINT_NAME               |CONSTRAINT_TYPE|TABLE_NAME                   |STATUS |
-------|------------------------------|---------------|-----------------------------|-------|
SYS    |REGISTRY_SCHEMA_FK            |R              |REGISTRY$SCHEMAS             |ENABLED|
SYS    |REGISTRY_PROGRESS_FK          |R              |REGISTRY$PROGRESS            |ENABLED|
SYS    |DEPENDENCIES_REQ_FK           |R              |REGISTRY$DEPENDENCIES        |ENABLED|
SYS    |DEPENDENCIES_FK               |R              |REGISTRY$DEPENDENCIES        |ENABLED|
SYS    |REGISTRY_PARENT_FK            |R              |REGISTRY$                    |ENABLED|
SYSTEM |MVIEW$_ADV_BASETABLE_FK       |R              |MVIEW$_ADV_BASETABLE         |ENABLED|
SYSTEM |MVIEW$_ADV_ROLLUP_FK          |R              |MVIEW$_ADV_ROLLUP            |ENABLED|
SYSTEM |MVIEW$_ADV_OUTPUT_FK          |R              |MVIEW$_ADV_OUTPUT            |ENABLED|
SYSTEM |MVIEW$_ADV_LEVEL_FK           |R              |MVIEW$_ADV_LEVEL             |ENABLED|
SYSTEM |MVIEW$_ADV_JOURNAL_FK         |R              |MVIEW$_ADV_JOURNAL           |ENABLED|
SYSTEM |MVIEW$_ADV_INFO_FK            |R              |MVIEW$_ADV_INFO              |ENABLED|
SYSTEM |MVIEW$_ADV_FILTERINSTANCE_FK  |R              |MVIEW$_ADV_FILTERINSTANCE    |ENABLED|
SYSTEM |MVIEW$_ADV_EXCEPTION_FK       |R              |MVIEW$_ADV_EXCEPTIONS        |ENABLED|
SYSTEM |MVIEW$_ADV_ELIGIBLE_FK        |R              |MVIEW$_ADV_ELIGIBLE          |ENABLED|
SYSTEM |MVIEW$_ADV_CLIQUE_FK          |R              |MVIEW$_ADV_CLIQUE            |ENABLED|
SYSTEM |MVIEW$_ADV_AJG_FK             |R              |MVIEW$_ADV_AJG               |ENABLED|
SYSTEM |MVIEW$_ADV_ROLLUP_PFK         |R              |MVIEW$_ADV_ROLLUP            |ENABLED|
SYSTEM |MVIEW$_ADV_ROLLUP_CFK         |R              |MVIEW$_ADV_ROLLUP            |ENABLED|
SYSTEM |MVIEW$_ADV_FJG_FK             |R              |MVIEW$_ADV_FJG               |ENABLED|
SYSTEM |MVIEW$_ADV_GC_FK              |R              |MVIEW$_ADV_GC                |ENABLED|
SYS    |ATTRIBUTE_TRANSFORMATIONS_FK  |R              |ATTRIBUTE_TRANSFORMATIONS$   |ENABLED|
SYS    |DAM_CONFIG_PARAM_FK1          |R              |DAM_CONFIG_PARAM$            |ENABLED|