[DBMS] Constraint 제약조건

Constraint
* 데이터 무결성(Integrity) : 저장된 데이터에는 ‘잘못된 데이터’ 가 없어야 한다

ex) 기본키(Primary key) 로 설정된 필드는 중복된 값 이나 NULL 값이 들어올수 없다
     성별 필드에는 ‘남’, ‘여’ 외에는 다른 값이 들어올수 없다.
     주문수량 필드는 반드시 1 이상의 값이 들어와야 한다.
     ….

* 제약조건 (Constraint)
- DBMS 는 데이터의 무결성을 보장하기 위해,  잘못된 데이터가 저장되는 것을 방지 하기 위해 ‘제약조건(Constraint)’ 을 사용한다
- 제약조건은 테이블의 ‘컬럼’ 에 설정하여 사용한다.
- 해당 ‘컬럼’에 설정된 제약조건에 위배된 데이터가 들어오는 경우 에러 발생

* 제약조건 종류

* 무결성 종류

 

-- MySQL 에서 Table 의 제약조건 확인
SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = 'mydb111' AND TABLE_NAME = 't_emp3';

* 제약조건 설정 방법
1. 테이블 생성시 동시에 제약조건 설정하는 방법
2. 테이블 생성후 제약조건 추가하는 방법

* 테이블 생성시 동시에 설정하기

SELECT * FROM t_dept2;

CREATE TABLE t_emp4 (
	NO INT(4) PRIMARY KEY,
	name VARCHAR(10) NOT NULL, 
	jumin VARCHAR(13) NOT NULL UNIQUE, -- 제약조건은 여러개 설정 가능
	area INT(1) CHECK(area < 5),
	deptno VARCHAR(6) REFERENCES t_dept2(dcode)
);

-- 별도의 항목으로 제약조건 정의 가능
CREATE TABLE t_emp4 (
	NO INT(4),
	name varchar(10) NOT NULL,
	jumin varchar(13) NOT NULL,
	area INT(1),
	deptno VARCHAR(6),
	PRIMARY KEY(no),
	UNIQUE(jumin),
	CHECK(area < 5),
	FOREIGN KEY (deptno) REFERENCES t_dept2(dcode)
);

제약조건명 ?
위의 예에서는 제약조건명이 없는 형태의 제약조건을 주었습니다.
그러나, 나중에는 제약조건을 활성화/비활성화 하는 등의 관리가 필요할때가 발생합니다.
이렇게 제약조건을 관리하려면 제약조건에 ‘이름’을 붙여서 관리해야 합니다.
실무에서는 제약조건에 이름을 지정해주는 것을 권장합니다.
이름없이 제약조건 설정하면 오라클이 알아서(?) 이름을 붙여주는데 읽기도 다루기 어렵습니다.

* 테이블 생성시 동시에 설정 + 제약조건명

-- 제약조건에 이름을 달아 정의 가능
CREATE TABLE t_emp3 (
	NO INT(4),
	name varchar(10) NOT NULL,
	jumin varchar(13) NOT NULL,
	area INT(1),
	deptno VARCHAR(6),
	CONSTRAINT emp3_no_pk PRIMARY KEY(no),
	CONSTRAINT emp3_jumin_uk UNIQUE(jumin),
	CONSTRAINT emp3_area_ck CHECK(area < 5),
	CONSTRAINT emp3_deptno_fk FOREIGN KEY (deptno) REFERENCES t_dept2(dcode)
);

SELECT * FROM information_schema.TABLE_CONSTRAINTS 
WHERE TABLE_SCHEMA = 'mydb111' AND TABLE_NAME = 't_emp3';

* 제약조건 조회하기

테이블에 제약조건을 설정하면 그 내용은 ‘딕셔너리’ 에 저장되어 있습니다.
- 사용자 딕셔너리 : USER_CONSTRAINTS 와 USER_CONS_COLUMNS  사용
- DB 전체 딕셔너리 : DBA_CONSTRAINTS 와 DBA_CONSTRAINT_COLUMNS 사용

** 딕셔너리( Dictionary ) : 스키마 · 사용자 · 객체 · 권한 · 롤 · 데이터베이스의 정보 등등, 오라클 데이터베이스를 운영하는데 필요한 정보를 관리하는 별도의 객체들

* 제약조건에 위배되는 DML 
테이블 생성후 제약조건 추가하기

-- #9005
-- t_emp3 의 제약조건에 맞는 / 위배되는 DML 시도.
INSERT INTO t_emp3 VALUES(
	1, 'MySQL', '1234561234567', 4, 1000
);
-- 두번 실행하면 오류 --> PK 제약조건 위배
-- SQL Error [1062] [23000]: Duplicate entry '1' for key 't_emp3.PRIMARY'

INSERT INTO t_emp3 VALUES(
	2, '오라클', '1234561234567', 4, 1000
); 
-- jumin unique 오류
-- SQL Error [1062] [23000]: Duplicate entry '1234561234567' for key 't_emp3.emp3_jumin_uk'

INSERT INTO t_emp3 VALUES(
	2, '오라클', '222222222222222222222222', 4, 1000
);  -- VARCHAR(13)  초과 오류
-- SQL Error [1406] [22001]: Data truncation: Data too long for column 'jumin' at row 1

INSERT INTO t_emp3 VALUES(
	2, 'tigers', '2222222222222', 10, 1000
);  -- CHECK  오류
-- SQL Error [3819] [HY000]: Check constraint 'emp3_area_ck' is violated.

INSERT INTO t_emp3 VALUES(
	2, 'tigers', '2222222222222', 3, 2000
);  -- FK  오류
-- SQL Error [1452] [23000]: Cannot add or update a child row: a foreign key constraint fails (`mydb`.`t_emp3`, CONSTRAINT `emp3_deptno_fk` FOREIGN KEY (`deptno`) REFERENCES `t_dept2` (`DCODE`))

INSERT INTO t_emp3(NO, jumin, area, deptno) VALUES(
	2, '3333333333333', 4, 1001
);  -- NN 오류
-- SQL Error [1364] [HY000]: Field 'name' doesn't have a default value

-- UPDATE/DELETE 에서도 제약조건 오류 발생 한다.

SELECT * FROM t_emp3;

UPDATE t_emp3 SET area = 10 WHERE NO = 1; -- CK 오류

DELETE FROM t_dept2 WHERE dcode = 1000;  -- 참조 되고 있는 부모는 삭제 불가
-- SQL Error [1451] [23000]: Cannot delete or update a parent row: a foreign key constraint fails (`mydb111`.`t_emp3`, CONSTRAINT `emp3_deptno_fk` FOREIGN KEY (`deptno`) REFERENCES `t_dept2` (`DCODE`))

-- #9005)   ALTER 명령 사용하여 테이블 에 제약조건 추가가능
-- 위에서 생성한 t_emp4 테이블의 name 컬럼에 UNIQUE 제약조건 추가하기

ALTER TABLE t_emp4 ADD CONSTRAINT emp4_name_uk UNIQUE(name);

* 외래키 설정시 주의 → 반드시 Unique!
참조되는 ‘부모 테이블의 컬럼’은 Primary Key 이거나 Unique 이어야 한다

외래키 추가
t_emp4 테이블의 name 컬럼이 t_emp2 테이블의 name 컬럼의 값을 참조하도록 참조키 제약조건을 설정하세요
(외래키에서 이 경우  t_emp2 를 ‘부모(parent)테이블’ 이라 하고 t_emp4 를 ‘자식(child)테이블’ 이라 합니다)

* FOREIGN KEY  + ON DELETE / ON UPDATE 옵션

- FK 를 설정한후 부모테이블의 데이터를 지우고 싶은데 만약 자식 테이블에서 부모테이블의 해당 데이터를 참조하고 있는 경우 지울수가 없습니다.
- 그래서 FK 를 생성할때 ON DELETE CASCADE 옵션을 주면 부모테이블의 데이터가 지워질때 자식테이블의 데이터도 함께 지울수 있습니다.
- 또한 ON DELETE SET NULL 옵션을 주면 부모테이블의 데이터가 지워질때 자식 테이블을 NULL 값으로 설정하게 됩니다.
- 마찬가지로 ON UPDATE 에도 옵션을 부여 가능  (** ORACLE 은 없음)

* ON DELETE ~~ / ON UPDATE ~~의 reference_option 들

* 제약조건 관리하기

- 테이블의 각 칼럼에 설정되는 각 제약조건들은 어떤 필요에 의해 일시적으로 DISABLE/ENABLE 할 수 있습니다.
- 가령, 이미 입력된 대량의 데이터는, 다시 입력할 경우 굳이 검사할 필요는 없습니다.  

  이런경우 제약조건을 임시로 DISABLE 시키면 입력 시간을 단축시킬수있습니다.

* 제약조건 DISABLE 하기
- DISABLE 하는 옵션은 NOVALIDATE 와  VALIDATE 두가지가 있음.

- NOVALIDATE : 해당 제약조건이 없어서 데이터가 전부 들어온다는 뜻

* DISABLE NOVALIDATE 사용하기

* DISABLE VALIDATE 사용하기

-- 복합키 제약조건 만들기

CREATE TABLE test_member(
	mb_uid INT,
	mb_nick VARCHAR(10),
	mb_name VARCHAR(10) NOT NULL,
	CONSTRAINT test_member_pk PRIMARY KEY(mb_uid , mb_nick)
);

INSERT INTO test_member VALUES(1, 'aaa', 'John');
INSERT INTO test_member VALUES(1, 'aaa', 'John');
INSERT INTO test_member VALUES(2, 'aaa', 'John');

SELECT * FROM test_member;