[Basic] 데이터 무결성 제약 조건

1 데이터 무결성 제약조건

• 데이터의 정확성과 일관성을 보장하기 위해 조건을 입력하는 것.
  • 테이블 생성시 정의
  • 응용 프로그램에서 입력된 모든 데이터에 동일하게 적용
  • 활성화, 비활성화 할 수 있는 융통성

2 종류

2-1 NOT NULL

• 해당 칼럼이 Null을 가질 수 없다.
• 테이블 레벨이 아니라 열 레벨로만 가능
• ex) 학생 명부에 이름

2-2 고유 키 UNIQUE KEY

• 동일한 값을 가질 수 없다. (NULL은 모두 unique한 값. NULL은 가능)
• 테이블 레벨, 열 레벨 모두 가능.
• 정의되는 순간, 테이블에 제약조건과 같은 이름의 인덱스가 자동 생성(나중에 알아볼 수 있게 내가 명명)
• ex) 학생 명부에 홈페이지 ID

2-3 기본 키 PRIMARY KEY

• NOT NULL과 UNIQUE KEY이 합쳐진 것. => 식별자로서 쓴다. 관계형 데이터 모델의 테이블은 식별자가 필요.
• ex) 학생 명부에 학번

2-4 참조 키 FOREIGH KEY

• 자식 테이블(외래foreign 키) => 부모 테이블(참조reference 키)
• 한 테이블의 칼럼 값이 자신, 다른 테이블의 칼럼 값 중 하나와 일치시키기 위한 제약 조건
• primary key 이슈: 자식이 primary key면 참조하려는 부모도 primary key여야 한다
• unique나 다른 경우는 해당 X. 유니크는 그냥 동일한 값 없다는 조건만 만족하면 ㅇㅋ.
• 지우는 경우(update, delete, restrict) 부모 값의 경우 자식 값에 참조되어 있는 경우는 못지운다
• 새로 만드는 경우(insert, update) 자식 값의 경우 부모 값에 없는 건 못만든다

2-5 check

• 허용 가능한 데이터 범위나 조건을 지정
• 하나의 칼럼에 여러 개의 check 제약 조건 지정 가능
• CURRVAL, NEXTVAL 같은 가상 칼럼, SYSDATE, USER 같은 함수 불가능
• ex) 학년 1~4학년만 가능

3 생성 방법

• 테이블 생성과 동시에 생성 => 이후에 추가, 삭제 가능
• 제약조건 이름 직접 지정 안하면 SYS_Cn 형태로 자동 생성
• 칼럼레벨은 칼럼에 지정하고, 테이블 레벨은 칼럼 조건 다 끝난 이후 가능

3-1 위의 것 의외의 키워드

• ON DELETE CASCADE :부모 테이블에서 외래 키가 참조하는 primary key나 unique key를 포함한 행을 삭제할 때, 자식 테이블의 외래 키를 포함하는 행도 같이 삭제.
• USING INDEX: 물리적으로 어디 저장될지 스토리지 지정.
• NOT DEFERRABLE / DEFERRABLE 전자는 하나의 DML 명령문 처리될 때마다 제약조건 위반 여부 검사로 위반하면 바로 롤백(이게 deffault 값으로, defer_spec 명시 안하면 이렇게 됨). 후자는 트랜잭션 종료시까지 제약조건 검사를 연기.
  => DEFERRABLE 중에서도 INITIALLY IMMEDIATE / INITIALLY DEFERRED: 전자는 DML 명령문 종료될 때마다 제약조건 검사(NOT DEFERRABLE이랑의 차이를 잘은 모르겠음). / 후자는 트랜잭션 끝날 때 한거번에 검사해서, 그동안 위반했던 것들 다 롤백. 후자의 경우 성능 향상을 위해 사용.
• 예를 들면, defferable initially deferred로 설정된 건, unique에 똑같은 거 2개 넣어도 작동한다. 하지만 commit하면 둘 다 사라진다. not deferrable의 경우, 똑같은 거 넣자마자 바로 에러 뜨면서 롤백.

CREATE TABLE subject (
subno NUMBER(5)
	CONSTRAINT subject_no_pk PRIMARY KEY 
    (subject~는 내가 붙이는 이름, 나중에 찾으려고. pk는 primary key 약자)
    (과목 번호를 프라이머리 키로 설정) 
    (CONSTRAINT 이름 PRIMARY KEY)
	DEFERRABLE INITIALLY DEFERRED (deferrable 설정)
    (DEFERRABLE immediate/deferred 선택)
    USING INDEX TABLESPACE indx, 
    (USING INDEX 테이블스페이스 이름 지정(경로?)
subname VARCHAR2(20)
	CONSTRAINT subject_name_nn NOT NULL,(과목 이름 NOT NULL 설정)
    (CONSTRAINT 이름 NOT NULL)
term VARCHAR2(1) 
	CONSTRAINT subject_term_ck CHECK (term in ('1', '2')), (학기, 범위 설정)
    (CONSTRAINT 이름 CHECK (범위. 여기서는 in으로 설정))
type VARCHAR2(1)); (이건 제약 조건 없는 경우)

CREATE TABLE sugang(
studno NUMBER(5)
	CONSTRAINT sugang_studno_fk REFERENCES student(studno), 
    (CONSTRAINT 이름 REFERENCES 부모)
subno NUMBER(5)
	CONSTRAINT sugang_subno_fk REFERENCES subject(subno), 
regdate DATE,
result NUMBER(3), 
CONSTRAINT sugang_pk PRIMARY KEY(studno, subno));
(이건 컬럼 설정 밖의 CONSTRAINT로 테이블 레벨의 제약 조건, 그래서 한거번에 2개의 컬럼에 제약을 걸고 있음)

4 제약조건 추가

ALTER TABLE student
ADD CONSTRAINT stud_deptno_fk FOREIGN KEY(deptno) REFERENCES department(deptno)
(ADD CONSTRAINT 이름 FOREIGN KEY(받을 것) REFERENCES(할 것)

일반적으로 이런 ALTER, ADD CONSTRAINT에, 추가할 것에(컬럼 이름)을 더한다

ALTER TABLE student
MODIFY (name CONSTRAINT stud_name_nn NOT NULL);

NOT NULL은 MODIFY로 한다. 만약 NOT NULL에도 이름을 명시하고 싶으면

ALTER TABLE student
MODIFY name VARCHAR2(20) CONSTRAINT 이름 NOT NULL;

이렇게 되어야 한다.

5 제약조건 조회

SELECT constraint_name, constraint_type, statUs
FROM user_constraints
WHERE table_name IN ('테이블이름')

SELECT constraint_name
FROM user_cons_columns (칼럼의 경우)
WHERE table_name IN ('테이블 이름')

• 그 테이블에 있는 제약조건들 다 나온다.
• 타입은 C(check 또는 not null), P(primary key), U(unique key), R(foreign key)

6 제약조건 삭제

ALTER TABLE subject
DROP CONSTRAINT subject_term_ck [CASCADE];

여기서 cascade는 삭제되는 칼럼의 reference key가 삭제될 때, 참조하는 칼럼의 foreign key의 제약조건도 함께 삭제

7 제약조건 비활성화

ALTER TABLE sugang
DISABLE CONSTRAINT constraint_name [CASCADE];

• 다시 활성화할 때는 ENABLE NOVALIDATE. 후자는 기존 데이터에 대해선 제약조건 적용 안하고, 새로 입력되는 값만 검사.
• 무결성 제약조건 위반 여부를 검사할 때 처리 시간이 너무 오래 걸리면 => 일시적 비활성화해서 데이터 입력 후, 다시 활성화