12.MySql(scott)-기본문법 - 제약조건의 이해

1.제약조건이란?

제약 조건(constraint)

제약조건(constraint)이란 데이터의 무결성을 지키기 위해, 데이터를 입력받을 때 실행되는 검사 규칙을 의미한다.이러한 제약 조건은 CREATE 문으로 테이블을 생성할 때나 ALTER 문으로 컬럼을 추가할 때도 설정할 수 있다

한마디로...부적절한 자료가 입력되는 것을 방지

MySQL에서 사용할 수 있는 제약 조건은 다음과 같다.

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK

1. NOT NULL

• NOT NULL 제약 조건을 설정하면, 해당 필드는 NULL 값을 저장할 수 없다.
• 즉, 이 제약 조건이 설정된 필드는 무조건 데이터를 가지고 있어야 한다.

CREATE TABLE Test(    
	ID INT NOT NULL,    
    Name VARCHAR(30),    
    ReserveDate DATE,    
    RoomNum INT
);

2. UNIQUE

• UNIQUE 제약 조건을 설정하면, 해당 필드는 서로 다른 값을 가져야 한다.
• 즉, 이 제약 조건이 설정된 필드는 중복된 값을 저장할 수 없다.

-- 컬럼 레벨
CREATE TABLE uni_tab1
(deptno NUMBER(2) CONSTRAINT uni_tab1_deptno_uk UNIQUE,
 dname  CHAR(14),
 LOC    CHAR(13)); 
-- 테이블 레벨
CREATE TABLE uni_tab2
(deptno NUMBER(2),
 dname  CHAR(14),
 loc    CHAR(13),
 CONSTRAINT uni_tab2_deptno_uk UNIQUE(deptno));
INSERT INTO uni_tab1 VALUES (10, 'AA', 'AA');
INSERT INTO uni_tab1 VALUES (10, 'BB', 'BB'); --error!
INSERT INTO uni_tab1 VALUES (null, 'AA', 'AA');
);

3. PRIMARY KEY

PRIMARY KEY 제약 조건을 설정하면, 해당 필드는 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가진다.따라서 이 제약조건이 설정된 필드는 NULL 값을 가질 수 없으며, 또한 중복된 값을 가질 수 없다.이러한 PRIMARY KEY 제약 조건을 기본 키라고 한다.테이블의 데이터를 쉽고 빠르게 찾도록 도와주는 역할을 한다.

CREATE TABLE department
(deptno NUMBER(2) CONSTRAINT department_deptno_pk PRIMARY KEY,
 dname  VARCHAR2(15),
 loc    VARCHAR2(15));
INSERT INTO department VALUES(10, '인사과', '서울');
INSERT INTO department VALUES(10, '영업', '서울'); -- error!
INSERT INTO department VALUES(NULL, '영업', '서울'); -- error!
);

복합컬럼 형태로도 가능

-- 복합 컬럼으로 기본키 지정
CREATE TABLE department3
(deptno NUMBER(2),
 dname  VARCHAR2(15),
 loc    VARCHAR2(15),
 CONSTRAINT department3_deptno_pk PRIMARY KEY(deptno, loc));
INSERT INTO department3 VALUES (10, 'AA', 'AA');
INSERT INTO department3 VALUES (10, 'BB', 'AA'); -- error!
INSERT INTO department3 VALUES (10, 'AA', 'BB');
-- 두 컬럼의 집합이 기본키이므로
-- deptno가 같아도 loc이 다르면 유일한 것으로 판단됨 
);

4. FOREIGN KEY

• FOREIGN KEY 제약 조건을 설정한 필드를 외래 키라고 부르며, 한 테이블을 다른 테이블과 연결해주는 역할을 한다.
• 즉, FOREIGN KEY 제약 조건은 하나의 테이블을 다른 테이블에 의존하게 만든다.
• FOREIGN KEY를 설정할 때 참조되는 테이블의 필드는 반드시 UNIQUE나 PRIMARY KEY가 설정되어 있어야 한다.

• 부모 테이블에서 자식 테이블이 참조하고 있는 값이 있는 행은 삭제할 수 없음
• 자식테이블은 지워도 부모테이블에 영향 없음

-- 부모 테이블 --
CREATE TABLE dept02
(deptno NUMBER(2) CONSTRAINT dept02_deptno_pk PRIMARY KEY,
 dname  VARCHAR2(15),
 loc    VARCHAR2(15));
INSERT INTO dept02(deptno, dname, loc) VALUES(10, '인사', '서울');
INSERT INTO dept02(deptno, dname, loc) VALUES(20, '개발', '광주');
INSERT INTO dept02(deptno, dname, loc) VALUES(30, '관리', '부산');
INSERT INTO dept02(deptno, dname, loc) VALUES(40, '영업', '경기');
-- 자식 테이블 (컬럼 레벨) --
CREATE TABLE emp02
(empno  NUMBER(4) CONSTRAINT emp02_empno_pk PRIMARY KEY,
 ename  VARCHAR2(15),
 deptno NUMBER(2) CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno));
INSERT INTO emp02 VALUES(1000, 'JOHN', 10);
INSERT INTO emp02 VALUES(2000, 'MIKE', 50); -- error(parent key not found)
-- 부모 테이블인 dept02 테이블의 deptno에 50번이 없으므로 에러남
-- 자식 테이블 (테이블 레벨) --
CREATE TABLE emp03
(empno  NUMBER(4) CONSTRAINT emp03_empno_pk PRIMARY KEY,
 ename  VARCHAR2(15),
 deptno NUMBER(2),
CONSTRAINT emp03_deptno_fk FOREIGN KEY(deptno) REFERENCES dept02(deptno));

부모 테이블의 행 삭제 시 문제될 수 있는 자식 테이블 행 설정법

• ON DELETE CASCADE: 부모 테이블의 행이 삭제되면 해당 행을 참조하는 자식 테이블의 행도 같이 삭제
• ON DELETE SET NULL: 부모 테이블의 행이 삭제되면 해당 행을 참조하는 자식 테이블의 컬럼을 NULL로 설정

-- ON DELETE SET NULL --
CREATE TABLE emp02
(empno  NUMBER(4) CONSTRAINT emp02_empno_pk PRIMARY KEY,
 ename  VARCHAR2(15),
 deptno NUMBER(2) CONSTRAINT emp02_deptno_fk REFERENCES dept02(deptno) ON DELETE SET NULL);
DELETE FROM dept02
WHERE deptno = 20;
-- deptno가 20번인 행이 부모 테이블에선 지워지고
-- 자식 테이블에선 deptno가 null이 됨

5. CHECK

CHECK(CK): 해당 컬럼에 반드시 만족해야 될 조건을 지정

CREATE TABLE sawon1
(s_no   NUMBER(2),
 s_name VARCHAR2(10),
 s_sal  NUMBER(10) CONSTRAINT sawon1_s_sal_ck CHECK(s_sal < 500));
CREATE TABLE sawon2
(s_no       NUMBER(2),
 s_name     VARCHAR2(10),
 s_gender   CHAR(3),
 CONSTRAINT sawon2_s_gender_ck CHECK(s_gender IN('남', '여')));
INSERT INTO sawon1 VALUES (10, 'AA', 400);
INSERT INTO sawon1 VALUES (20, 'BB', 500); -- error!
INSERT INTO sawon2 VALUES (10, 'AA', '남');
INSERT INTO sawon2 VALUES (20, 'AA', '뭵'); -- error!

https://cheeseb.github.io/?category=MySQL
https://dev-coco.tistory.com/55