제약조건

*기본키/외래키

<제약 조건의 이해와 설정 1> (PK, FK)
; 제약 조건이란 테이블 단위에서 데이터의 무결성을 보장해주는
 규칙이다. 제약 조건은 테이블에 데이터가 입력, 수정, 삭제시
 또는 테이블이 삭제, 변경될 경우 잘못된 트랜잭션이 수행되지
 않도록 결함을 유발할 가능성이 있는 작업을 방지하는 역할을
 수행한다.
 특히 PK와 FK는 테이블의 필수 요소로써 모든 테이블은 이들
 둘 중 하나 이상을 반드시 포함하고 있다.
 
 <제약 조건>
 1) 테이블 단위에서 정의되고 적용된다.
 2) 종속성이 존재하는 경우 테이블의 삭제를 막아준다.
    (자식 테이블이 있는 부모 테이블은 삭제 불가.
    자식 테이블부터 삭제하고 부모 테이블을 삭제해야 함)
 3) 자료가 삽입,갱신,삭제될 때마다 규칙이 적용된다.
 4) 일시적으로 활성화하거나 비활성화하는 것이 가능하다.  
 5) USER_CONSTAINTS, USER_CONS_COLUMNS 딕셔너리에서
   검색한다
 6) 제약조건은 개체처럼 관리되므로 반드시 이름이 필요하다.
    제약조건에 이름을 정의하지 않으면 오라클 서버가
    자동으로 SYS_Cn형태의 이름을 붙인다. 그러므로
    관리자가 규칙성 있는 이름을 붙여주는 것이 훨씬 좋다.
    
<오라클에서 제공되는 제약조건>
PRIMARY KEY(PK) : 테이블에 반드시 지정해야 한다  (테이블은 언제든지 다른테이블과 관계성을 맺을 수 있다는 것을 전제하니까)
FOREIGN KEY(FK) : 자식 테이블은 반드시 지정해야 한다
UNIQUE KEY(UK) : 유니크한 컬럼의 검색속도 향상 (중복x, 인덱스가 생성된다.)
NOT NULL       : PK는 무조건 NOT NULL, 타 컬럼 옵션
CHECK          : 기타 조건
    
1. PK 설정
컬럼에 PK를 지정하는 방법은 아래처럼 2가지 방법이 있다.
CREATE TABLE 테이블명(
...
CONSTRAINT 제약조건이름 PRIMARY KEY(컬럼));

CREATE TABLE 테이블명(
컬럼 데이터타입 CONSTRAINT 제약조건이름 PRIMARY KEY,
...);

1) 테이블을 생성할 때 PK를 정의한다
2) PK는 각 행을 고유하게 식별하는 역할을 담당한다
3) 테이블당 하나or 한개 그룹(복합키)만 정의 가능하다
4) 지정된 컬럼에는 중복된 값이나 NULL 값이 입력불가하다
5) PK로 지정 가능한 컬럼이 여러 개 있을 때는 검색에
  많이 사용되고 간단하고 짧은 컬럼을 지정한다
  (ex 사원 : 사번, 주민번호 중에 사번을 선택)
6) 주 식별자, 주키 등으로 불린다
7) 고유 인덱스(Unique Index)가 자동으로 생성된다. -->빠르게 검색. (map자료구조 기반)
    
2. FK 설정
CREATE TABLE 테이블명(
...
CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼)
REFERENCES 부모테이블 (참조컬럼) [ON DELETE CASCADE]);

CREATE TABLE 테이블명(
컬럼명 데이터타입 CONSTRAINT 제약조건이름 FOREIGN KEY
                REFERENCES 부모테이블 (참조컬럼)
                [ON DELETE CASCADE],
...);
 1) 부모 테이블을 참조하여 테이블을 생성할 때 FK를 정의한다
 2) FK가 정의된 테이블이 자식(하위) 테이블이다
 3) 참조되는 테이블을 부모(상위) 테이블이라고 한다
 4) 부모 테이블은 미리 생성되어 있어야 한다
 5) 부모 테이블의 참조되는 컬럼에 존재하는 값만을 입력할 
    수 있다.
 6) 부모 테이블에 참조되는 행의 데이터는 FK를 위배하는
    삭제나 변경이 불가능하다.
 7) ON DELETE CASCADE : 참조되는 부모 테이블의 행에 대한
    DELETE 를 허용한다.
    (부모 테이블의 행을 삭제하면 참조하는 자식테이블의 행도
    함께 지워진다)
 8) 자료형이 반드시 부모테이블의 컬럼과 일치해야 한다
 9) 참조되는 컬럼은 PK이거나 UK(Unique Key)만 가능하다
 10) 외부키, 참조키, 외부식별자 등으로 불린다.
   11) REFERENCES 'S'를 꼭 붙일 것
 
 * 제약조건을 정의하는 방법은 2가지 방법이 제공된다.
   1) 테이블 생성 명령어 마지막에 기술하는 방법
      : 테이블 레벨 정의
   2) 컬럼을 정의할 때 같이 정의하는 방법
      : 컬럼 레벨 정의
   둘 중에 테이블 레벨 정의가 가독성이 높다.
      
기존 스크립트(*.sql)에는 의미적으로만 dno로 연결되었을 뿐
제약조건이 없기 때문에 dept의 행을 지워도 에러가 안생겼음

1) PK와 FK가 추가된 dept와 emp 테이블을 생성한다.   
drop table emp;
drop table dept;
PURGE RECYCLEBIN -- Windows의 휴지통 비우기 기능과 동일
  
--부모 테이블인 dept 테이블부터 생성해야 한다      
--1번 방법 (더 많이 쓰임)
CREATE TABLE dept(
dno VARCHAR(2),
dname VARCHAR(14),
loc VARCHAR(8),
director VARCHAR2(4),
CONSTRAINT dept_dno_pk PRIMARY KEY(dno)
);

--2번 방법
CREATE TABLE dept(
dno VARCHAR(2) CONSTRAINT dept_dno_pk PRIMARY KEY,
dname VARCHAR(14),
loc VARCHAR(8),
director VARCHAR2(4),
);

CREATE TABLE emp(
eno VARCHAR2(4),
ename VARCHAR2(10),
job VARCHAR2(6),
mgr VARCHAR2(4),
hdate DATE,
sal NUMBER,
comm NUMBER,
dno VARCHAR2(2),
CONSTRAINT emp_eno_pk PRIMARY KEY(eno),
CONSTRAINT emp_dno_fk FOREIGN KEY(dno)
  REFERENCES dept(dno)
);
-- 하지만 제약조건이 있으면 데이터를 입력할 때 번거로운 점이 많아서 일단 테이블을 만들고 
-- 데이터를 입력 후에 ALTER TABLE로 제약조건을 추가하는 방법이 일반적이다.


 PK로 정의된 dept의 dno, emp의 eno 컬럼은 
중복된 값 X, NULL X
 FK로 정의된 emp의 dno는 dept의 dno를 참조하도록 
설정했으므로, emp에 데이터를 입력할 때 fk가 존재하지 않는
데이터를 입력하면 에러가 발생한다.

2) dept와 emp에 데이터를 입력하고 제약조건 설정을 테스트
INSERT INTO dept(dno, dname, loc)
 VALUES ('10','개발','서울');
 
INSERT INTO emp(eno, ename, dno)
 VALUES ('2000','김연아','10');


3) PK에 이미 '10'번 부서가 있으므로 중복데이터 입력 방지
PK에 대한 무결성을 '개체 무결성'이라 한다
INSERT INTO dept(dno, dname, loc)
 VALUES ('10','총무','부산');  --개체무결성 조건 위배 : PK는 유일해야하는데 중복이므로 오류가 뜬다.


4) FK값이 부모테이블에 존재하지 않으므로 입력 방지
FK에 대한 무결성을 '참조 무결성'이라 한다
INSERT INTO emp(eno, ename, dno)
 VALUES ('2001','손하늘','20');
 
 -- 이러한 제약조건들 때문에 처음엔 비활성화, 나중에 활성화시킨다.


데이터 모델링해서 물리 데이터베이스 테이블 생성방법 2가지
1) ER-Win 툴에서 설정을 잘 선택, 제약조건이름 부여...
   자동 생성(툴을 잘 사용해야 함)
2) 모델링(ERD) -> 테이블 상세표 -> 스크립트(*.sql) 생성 -> 실행
  현재 이 방법을 더 추천
내용을 입력하세요.

*그 외 제약조건


<제약 조건의 이해와 설정2>
; NOT NULL, UK, CHECK

1) PK, FK를 제외한 나머지 제약조건
2) 이들은 모든 테이블에 반드시 필요한 것은 아니다.
   하지만 이를 통해 미리 프로세스상의 오류를 예방할 수 있다
   
<Unique Key 설정>
CREATE TABLE 테이블명(
  ...
  CONSTRAINT 제약조건명 UNIQUE (컬럼)
);

CREATE TABLE 테이블명(
  컬럼 데이터타입 CONSTRAINT 제약조건명 UNIQUE,
...);
 1) UK(Unique Key)를 정의한다
 2) 중복된 값을 허용하지 않지만 여러개의 NULL 은 허용한다
    - NULL 값은 비교가 불가능하므로 여러개라도 중복이
     아니다.
 3) 고유키라고도 불린다
 4) 고유 인덱스(Unique Index)가 자동으로 생성된다. (검색시 빠름)
 5) 사원 테이블의 사번, 주민번호 중에 사번은 PK,
    주민번호는 UK 설정을 한다. 
    (주로 조인해서 검색하기 위해 uk를 만든다.
    uk를 통해 외래키를 만들 수도 있다.
    uk를 참조하는 테이블을 만들 수 있다는 뜻이다.)
    
<NOT NULL 설정>
CREATE TABLE 테이블명(
  컬럼 데이터타입 CONSTRAINT 제약조건명 NOT NULL,
  ...
);
1) 컬럼 레벨 정의만 가능하다
2) 지정된 컬럼은 NULL 값을 허용하지 않는다
3) 반드시 NULL 값이 필요한 컬럼을 제외하고
  가능한 모든 컬럼에 NULL 값을 허용하지 않는 것이 좋다.
    
<CHECK 설정>
CREATE TABLE 테이블명(
  ...
  CONSTRAINT 제약조건명 CHECK(조건)
);

CREATE TABLE 테이블명(
  컬럼 데이터타입 CONSTRAINT 제약조건명 CHECK (조건),
  ...
);
 1) 행에 입력될 데이터의 조건을 정의한다
 2) 조건은 WHERE 절에 기술하는 조건형식과 동일하다.
 3) 어플리케이션에서 데이터의 조건을 체크하는 대신
    테이블에서 조건을 체크하도록 하는 것이다.
    (어플리케이션 코드양을 줄일 수 있다)
    
emp4 테이블을 생성한다

CREATE TABLE emp4 (
  eno VARCHAR2(4),
  ename VARCHAR2(50) CONSTRAINT emp4_ename_nu NOT NULL,
  gno VARCHAR2(13),
  sex VARCHAR2(3),        --한글은 3바이트
  CONSTRAINT emp4_eno_pk PRIMARY KEY(eno),
  CONSTRAINT emp4_gno_uk UNIQUE(gno),
  CONSTRAINT emp4_gno_ch CHECK(LENGTH(gno)=13),  --13글자 아니면 오류
  CONSTRAINT emp4_sex_ch CHECK(sex IN ('여','남'))  --여 혹은 남만 들어갈 수 있다.
);


drop table emp4;

--정상 조건을 만족할 때 잘 입력됨
INSERT INTO emp4(eno,ename,gno,sex)
 VALUES ('1001','김연아','0602183123456','여');

--NOT NULL 컬럼에 NULL 을 입력한 경우
INSERT INTO emp4(eno,ename,gno,sex)
 VALUES('1002', NULL, '0602184123457', '남');

--gno(주민번호) 중복시
INSERT INTO emp4(eno,ename,gno,sex)
 VALUES ('1003','장나라','0602183123456','여');
  --unique 제약조건 위배 : 김연아와 주민번호가 같기때문에 오류.


--gno(주민번호) 글자가 12글자일 때
INSERT INTO emp4(eno,ename,gno,sex)
 VALUES ('1005','문근영','060218312345','여');

--'여'/'남' 대신 'M'을 넣을 때
INSERT INTO emp4(eno,ename,gno,sex)
 VALUES('1002', NULL, '0602184123457', 'M');
내용을 입력하세요.

*제약조건 관리


<제약 조건 관리>
;제약 조건 생성하는 방법
1) 테이블을 생성할 때 같이 생성하기
   1-1) 컬럼 레벨 생성
   1-2) 테이블 레벨 생성
2) 테이블의 컬럼과 자료형만 생성한 후
   나중에 ALTER 명령으로 제약조건 추가하기
   
* 모델러들은 2)번을 더 선호하는 경향이 있다

* 제약조건을 일시적으로 활성화/비활성화 할 수 있다
1) 대규모 배치 처리(대용량 데이터 입력시)
  데이터의 무결성이 확인된 경우라면
  제약조건 검사를 하지 않는 편이 속도가 훨씬 빠르다
  이럴 때 제약조건을 비활성하고 데이터 입력 후
  다시 제약조건을 활성화한다
  만일 제약조건에 위배되는 데이터가 있다면 쿼리로
  검사해서 발견할 수 있다.
2) 사이트 초기 개발시
  부모-자식 테이블의 관계가 설정되어 있으면
  테이블의 단위테스트에서 데이터 입력이 번거로울 수 
  있다. 개발자들은 초기 개발시 일시 비활성화하여 테스트
  를 진행하는 경우가 종종 있다.
  물론 나중에 다시 활성화해야 한다.
  
  <제약 조건 추가/삭제>
  ALTER TABLE 테이블명
  ADD CONSTRAINT 제약조건명 제약조건타입;
  1) NOT NULL을 제외한 제약조건을 추가한다
  2) 제약조건타입 : 제약 조건별 설정 방법과 동일
  3) NOT NULL 제약 조건은 MODIFY 명령으로 추가
  
  ALTER TABLE 테이블명
  MODIFY 컬럼 CONSTRAINT 제약조건명 NOT NULL;
  1) NOT NULL 제약 조건을 추가한다
  2) 삭제 방법은 다른 제약조건과 동일하다.
  
  ALTER TABLE 테이블명
  DROP PRIMARY KEY|UNIQUE(컬럼)|CONSTRAINT 제약조건명 [CASCADE];
  1) 제약 조건을 삭제한다
  2) CASCADE : PK나 UK가 삭제될 경우 이를 참조하는 FK도 삭제된다.

CREATE TABLE class(
  cno VARCHAR2(2),
  cname VARCHAR2(50)
);

CREATE TABLE st(
  sno VARCHAR2(2),
  sname VARCHAR2(50),
  cno VARCHAR2(2)
);

INSERT INTO class VALUES('01', '노랑새싹반');
INSERT INTO class VALUES('02', '연두잎새반');

INSERT INTO st VALUES('01', '김연아', '01');
INSERT INTO st VALUES('02', '홍길동', '02');
INSERT INTO st VALUES('03', '임꺽정', '03');

COMMIT;

SELECT * FROM class;
SELECT * FROM st;

--제약 조건 추가
ALTER TABLE class ADD CONSTRAINT class_cno_pk PRIMARY KEY(cno);
ALTER TABLE class ADD CONSTRAINT class_cname_uk UNIQUE(cname);
ALTER TABLE st ADD CONSTRAINT st_sno_pk PRIMARY KEY(sno);

테이블에 잘못된 데이터가 입력되는 경우
제약조건 추가가 실패하게 된다.
ALTER TABLE st ADD CONSTRAINT st_cno_fk FOREIGN KEY(cno)
  REFERENCES class(cno);

데이터를 수정하고 다시 위의 명령을 실행하면
정상적으로 동작한다
UPDATE st SET cno='01'
 WHERE cno='03';
COMMIT;
ALTER TABLE st ADD CONSTRAINT st_cno_fk FOREIGN KEY(cno)
  REFERENCES class(cno);
  
NOT NULL 조건 추가
ALTER TABLE class
 MODIFY cname CONSTRAINT class_cname_nu NOT NULL;


제약 조건 삭제
CASCADE 를 쓰면 class_cno_pk 조건이 삭제되고
관련된 참조 조건인 st_cno_fk 조건도 삭제된다.

만약 CASCADE 를 안쓰면 class_cno_pk 도 삭제가 안된다.
왜냐하면 자식테이블이 fk조건으로 참조하고 있기 때문에
ALTER TABLE class
 DROP CONSTRAINT class_cno_pk;
 
ALTER TABLE class
 DROP CONSTRAINT class_cno_pk CASCADE;
ALTER TABLE class
 DROP CONSTRAINT class_cname_nu;
ALTER TABLE class
 DROP CONSTRAINT class_cname_uk;
ALTER TABLE st
 DROP CONSTRAINT st_sno_pk;


제약 조건 활성화/비활성화
ALTER TABLE 테이블명
ENABLE CONSTRAINT 제약조건명;
1) 비활성화된 제약조건을 활성화한다
2) PK나 UK 제약조건이 활성화되면 인덱스가 만들어진다

ALTER TABLE 테이블명
DISABLE CONSTRAINT 제약조건명 [CASCADE];
1) 제약조건을 비활성화한다
2) PK나 UK인 경우 인덱스가 삭제된다
3) 비활성화된 제약조건은 제약조건을 검사하지 않는다.

부모 class테이블의 pk, 자식 st테이블의 fk 설정 제약조건
ALTER TABLE class
 ADD CONSTRAINT class_cno_pk PRIMARY KEY(cno);
ALTER TABLE st
 ADD CONSTRAINT st_cno_fk FOREIGN KEY(cno)
 REFERENCES class(cno);

부모 class에 cno='03'이 없으므로 에러 발생
INSERT INTO st VALUES('10', '김다은', '03');

부모 class 먼저 cno='03'인 행을 삽입
자식 st 에 cno='03'인 행을 삽입


제약조건에 의해 부모 먼저-자식 나중이라는 순서가 생기지만
현장상황에서는 일단 자식데이터가 먼저 확보되면
자식부터 입력해야 할수도 있다
이럴 때 "제약조건의 비활성화"를 사용한다
ALTER TABLE st
 DISABLE CONSTRAINT st_cno_fk;


제약조건을 비활성화했으므로 자식 먼저 들어갈 수 있다
INSERT INTO st VALUES('10', '김다은', '03');

나중에 부모데이터를 넣는다
INSERT INTO class VALUES('03', '분홍열매반');

제약조건을 다시 활성화한다
ALTER TABLE st
 ENABLE CONSTRAINT st_cno_fk;


대용량 배치 작업(테이블 자동 생성, 데이터 삽입...)시
제약조건을 비활성화하는 이유
1) 부모테이블의 데이터보다 자식테이블의 데이터가 먼저
  확보되는 경우
2) 데이터를 삽입할 때마다 제약조건이 있으면 검사하는 시간이
  소요된다. 그래서 무결성이 인정되는 데이터라면
  검사시간을 줄이기 위해 제약조건을 비활성화하고 
  삽입후 다시 활성화한다

<대규모 배치 작업을 효율적으로 수행하는 방법>
1. 가능한 DATABASE BUFFER CACHE 의 크기를 늘려 잡는다.
  - 온라인 처리중이 아니면 SHARED POOL 의 크기를 임시로
    줄인다.
2. 다이렉트 로드를 이용해서 작업을 수행한다
  - 이때는 DATABASE BUFFER CACHE 를 이용하지 않음으로
    크기를 늘릴 필요가 없다.
3. 입력 테이블과 관련된 모든 제약조건을 비활성화한다.
  - 제약조건에 맞지 않은 데이터는 입력이 끝난 이후
   EXCEPTION 기능을 이용해서 검색할 수 있다.