디비랑[dɪ'bɪraŋ]

/********************************************************************************************
-- Title : [10g] 제약조건 관리 - ver.dbrang
-- Reference : dbrang.tistory.com
-- Key word : constraint 제약조건 제약사항
********************************************************************************************/

/*
-- 제약조건 기술 데이터 사전 뷰
*/
-- 테이블 생성
CREATE TABLE scott.tbl90
( a NUMBER NOT NULL
, c VARCHAR2(5) NULL
, CONSTRAINT pk_tbl90 PRIMARY KEY (a)
) tablespace users;

CREATE TABLE scott.tbl91
( a NUMBER NOT NULL
, b VARCHAR2(10) NULL
, CONSTRAINT pk_tbl9 PRIMARY KEY (a)
, CONSTRAINT uk_tbl9 UNIQUE (b)
, CONSTRAINT fk_tbl91_tbl90 FOREIGN KEY (a) REFERENCES scott.tbl90(a)
) tablespace ts_data1;

-- 제약 확인
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL90', 'TBL91');

/*
-- 제약조건과 연관된 열 보기
*/
SELECT *
FROM DBA_CONS_COLUMNS
WHERE owner = 'SCOTT' AND table_name IN ('TBL90', 'TBL91');

/*
-- 제약 조건 정의
*/
-- 테이블 생성
CREATE TABLE scott.tbl93
( empno NUMBER(4) NULL      -- 왜 만들어질까?
, ename VARCHAR2(10)
, deptno NUMBER(7,2) NOT NULL
, CONSTRAINT tbl93_empno_pk PRIMARY KEY (empno)
);

-- 제약 확인
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL93');

/*
-- NOT NULL 제약조건 이름 추가
*/  
CREATE TABLE scott.tbl94
( empno NUMBER(4)
, ename VARCHAR2(10) NOT NULL
, deptno NUMBER(7,2) CONSTRAINT tbl94_deptno_nn NOT NULL
  -- 그냥 deptno NUMBER(7,2) NOT NULL하면?
);

-- 제약 확인
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL94');

/*
-- UNIQUE Key 제약조건
*/
CREATE TABLE scott.tbl95
( deptno NUMBER(2)
, dname VARCHAR2(14)
, loc VARCHAR2(13)
, CONSTRAINT dept_dname_uk UNIQUE(dname)
);

-- 제약 확인
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL95');

/*
-- PRIMARY KEY 제약조건
*/
CREATE TABLE scott.tbl96
( deptno NUMBER(2)
, dname VARCHAR2(14)
, loc VARCHAR2(13)
, CONSTRAINT dept_dname_uk22 UNIQUE(dname)
, CONSTRAINT dept_deptno_pk22 PRIMARY KEY(deptno)
);

-- 제약 확인
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL96');

/*
-- FOREIGN KEY 제약조건
*/
CREATE TABLE scott.tbl100
( a NUMBER NOT NULL
, c VARCHAR2(5) NULL
, CONSTRAINT pk_tbl100 PRIMARY KEY (a)
) tablespace users;

CREATE TABLE scott.tbl101
( a NUMBER NOT NULL
, b VARCHAR2(10) NULL
, CONSTRAINT pk_tbl101 PRIMARY KEY (a)
, CONSTRAINT uk_tbl101 UNIQUE (b)
, CONSTRAINT fk_tbl101_tbl100 FOREIGN KEY (a) REFERENCES scott.tbl100(a)
) tablespace ts_data1;

-- 제약 확인
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL101');

 
/*
 -- CHECK 제약조건
*/
CREATE TABLE scott.tbl55
( empno NUMBER(4)
, ename VARCHAR2(10) NOT NULL
, job VARCHAR2(9)
, hiredate DATE
, deptno NUMBER(7,2) NOT NULL
, CONSTRAINT ck_tbl55 CHECK (deptno BETWEEN 10 AND 99)
);

-- 제약 확인
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL55');
 
 
/*
-- 제약 조건의 추가
*/
ALTER TABLE scott.tbl100
ADD CONSTRAINT chk_tbl100_c CHECK (c > 100);

 

/*
-- 제약 조건의 삭제
*/
-- 제약 삭제
ALTER TABLE scott.tbl100
DROP CONSTRAINT chk_tbl100_c;

-- 제약 확인
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL101');

-- CASCADE 제약 삭제
ALTER TABLE scott.tbl100
DROP PRIMARY KEY CASCADE;
 
-- 제약 확인(Relation 자동 삭제 확인)
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL101');

/*
-- 제약조건 DISABLE/ENABLE
*/
-- 제약 확인
SELECT *
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL101');

-- DISABLE
ALTER TABLE scott.tbl101
DISABLE CONSTRAINT UK_TBL101 CASCADE;

-- 제약 확인(status)
SELECT owner, constraint_name, table_name, search_condition, status
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL101');

-- ENABLE
ALTER TABLE scott.tbl101
ENABLE CONSTRAINT UK_TBL101;

-- 제약 확인(status)
SELECT owner, constraint_name, table_name, search_condition, status
FROM DBA_CONSTRAINTS 
WHERE owner = 'SCOTT' AND table_name IN ('TBL101');

/*
-- UNUSED 설정 및 삭제
*/
-- 테이블생성
CREATE TABLE scott.tbl777
( a NUMBER NOT NULL
, b VARCHAR2(10) NOT NULL
, c NUMBER(12) NOT NULL
);

-- 컬럼 확인
SELECT *
FROM dba_tab_columns
WHERE owner = 'SCOTT' AND table_name = 'TBL777';

-- UNUSED 설정
ALTER TABLE scott.tbl777
SET unused COLUMN c;

-- 컬럼 확인
SELECT *
FROM dba_tab_columns
WHERE owner = 'SCOTT' AND table_name = 'TBL777';

-- UNUSED 컬럼 삭제
ALTER TABLE scott.tbl777
DROP unused columns;