[SQL] 05-2 제약조건으로 테이블을 견고하게

기본 개념

1. 앞에서 확인한 기본 키와 외래 키가 대표적인 제약조건임.
   1) 기본 키는 학번, 아이디, 사번 등과 같은 고유한 번호를 의미하는 열에 지정함.
   2) 외래 키는 기본 키와 연결되는 열에 지정함.

2. 이메일, 휴대폰과 같이 중복되지 않는 열에는 고유 키를 지정할 수 있음.

3. 실수를 방지하는 제약조건은 체크(check)라고 하며, 매번 입력하기 귀찮다면 입력 조건으로 기본값을 설정할 수 있음.
   1) 값을 꼭 입력해야 하는 NOT NULL 제약조건도 있음.

제약조건의 기본 개념과 종류

1. 제약조건은 데이터의 무결성을 지키기 위해 제한하는 조건임.
   1) 데이터의 무결성이란 '데이터에 결함이 없음.'이란 의미임.

2. MySQL에서 제공하는 대표적인 제약조건
   1) PRIMARY KEY 제약조건
   2) FOREIGN KEY 제약조건
   3) UNIQUE 제약조건
   4) CHECK 제약조건
   5) DEFAULT 정의
   6) NULL 값 허용

기본 키 제약조건

1. 테이블 내에 많은 행 데이터 중에서 데이터를 구분할 수 있는 식별자
   1) 예로, 회원 테이블의 아이디, 학생 테이블의 학번, 직원 테이블의 사번 등이 있음.

2. 기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없음.

3. 대부분의 테이블은 기본 키를 가져야 함.
   1) 기본 키가 없어도 테이블 구성이 가능하지만, 실무에서 사용하는 테이블에는 기본 키를 설정해야 중복된 데이터가 입력되지 않음.
   2) 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성됨.

4. 테이블은 기본 키를 1개만 가질 수 있음.
   1) 기본 키로 테이블의 특성을 가장 잘 반영하는 열을 선택하는 것이 좋음.

CREATE TABLE에서 설정하는 기본 키 제약조건

1. CREATE TABLE 문에 PRIMARY KEY 예약어를 넣음.
   
   1) 이렇게 설정함으로써 회원 아이디(mem_id)는 회원 테이블의 기본 키가 되었으며, 앞으로 입력되는 회원 아이디는 중복될 수 없고, 비어있을 수도 없음.

2. 참고: 테이블을 삭제하는 순서
   1) 회원 테이블과 구매 테이블은 기본 키-외래 키로 연결되어 있음. 즉, 회원 테이블의 회원만 구매 테이블에 입력될 수 있음.
   2) 구매 테이블을 먼저 삭제하고, 회원 테이블을 나중에 삭제해야 함.
   3) 즉, 기본 키-외래 키 관계로 연결된 테이블은 외래 키가 설정된 테이블을 먼저 삭제해야 함.

3. DESCRIBE 문을 사용하여 테이블의 정보 살피기
   

4. CREATE TABLE에서 기본 키를 지정하는 다른 방법은 제일 마지막 행에 PRIMARY KEY(mem_id)를 추가하는 것임.
   

ALTER TABLE에서 설정하는 기본 키 제약조건

1. 제약조건을 설정하는 또 다른 방법은 이미 만들어진 테이블을 수정하는 ALTER TABLE 구문을 사용하는 것임.
   
   1) member를 변경함.
   2) 제약조건을 추가함.
   3) mem_id 열에 기본 키 제약조건을 설정함.

2. 앞서 CREATE TABLE 안에 PRIMARY KEY를 설정한 것과 지금 ALTER TABLE로 PRIMARY KEY를 지정한 것은 모두 동일한 결과를 가짐.

3. 기본 키에 이름 지정하기
   1) 기본 키는 별도의 이름이 없으며, DESCRIBE 명령으로 확인하면 그냥 PRI로만 나옴.
   2) 기본 키의 이름을 직접 지을 수 있음. 예를 들어, PK_member_mem_id와 같은 이름을 붙이면, 이름 만으로도 PK가 member 테이블의 mem_id 열에 지정됨이라고 이해할 수 있음.

외래 키 제약조건

1. 외래 키 제약조건은 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 함.

2. 외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결됨.

3. 앞서 사용한 회원 테이블과 구매 테이블이 대표적인 기본 키-외래 키 관계임.

4. 기본 키가 있는 회원 테이블을 기준 테이블이라고 부르며, 외래 키가 있는 구매 테이블을 참조 테이블이라고 부름.
   1) 구매 테이블의 아이디(FK)는 반드시 회원 테이블의 아이디(PK)로 존재함.

5. 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나 고유 키로 설정되어 있어야 함.

CREATE TABLE에서 설정하는 외래 키 제약조건

1. 외래 키를 생성하는 방법은 CREATE TABLE 끝에 FOREIGN KEY 키워드를 설정하는 것임.
   
   1) 외래 키의 형식은 FOREIGN KEY(열 이름) REFERENCES 기준 테이블(열 이름)임.
   2) 구매 테이블의 열(mem_id)이 참조하는 기준 테이블의 열(mem_id)은 기본 키로 설정되어 있는 것을 알 수 있음.
   3) 만약, 기준 테이블이 PRIMARY KEY 또는 UNIQUE가 아니라면 외래 키 관계는 설정되지 않음.

2. 참고: 기준 테이블의 열 이름과 참조 테이블의 열 이름
   1) 기준 테이블의 열 이름과 참조 테이블의 열 이름이 꼭 같아야 하는 것은 아님.

ALTER TABLE에서 설정하는 외래 키 제약조건

1. 외래 키를 설정하는 다른 방법은 ALTER TABLE 구문을 이용하는 것임.
   
   1) buy를 수정함.
   2) 제약조건을 추가함.
   3) 외래 키 제약조건을 buy 테이블의 mem_id에 설정함.
   4) 참조할 기준 테이블은 member 테이블의 mem_id 열임.

기준 테이블의 열이 변경될 경우

예: 회원 테이블의 BLK가 물품을 2건 구매한 상태에서 회원 아이디를 PINK로 변경하면?

1. 정상적으로 데이터 입력하기
   

2. 내부 조인을 사용해서 물품 정보 및 사용자 정보 확인하기
   

3. BLK의 아이디를 PINK로 변경하기 -> 오류 발생
   
   1) 기본 키-외래 키로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않음. -> 열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문임.
   2) 지금은 회원 테이블의 BLK가 물건을 구매한 기록이 구매 테이블에 존재하기 때문에 변경할 수 없는 것임.
   3) 만약, BLK가 물건을 구매한 적이 없다면 구매 테이블에 데이터가 없으므로 회원 테이블의 BLK는 변경 가능함.

4. 삭제 시도 또한 같은 오류로 삭제되지 않음.
   

5. 기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경되도록 만들기
   1) 회원 테이블의 BLK가 PINK로 변경되면 자동으로 구매 테이블의 BLK도 PINK로 변경되도록 하기
   2) ON DELETE CASCADE 문은 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되는 기능임.
   

6. 구매 테이블에 데이터 다시 입력하기
   

7. 회원 테이블의 BLK를 PINK로 변경하기
   
   1) 전과 달리, 오류 없이 잘 변경됨.

8. 내부 조인을 사용하여 물품 정보 및 사용자 정보 확인하기
   1) 결과를 보면, 기준 테이블과 참조 테이블의 아이디가 모두 변경된 것을 확인 가능함.
   

9. PINK가 탈퇴한 것으로 가정하고 기준 테이블에서 삭제하기
   1) 전과 달리, 오류 없이 잘 삭제됨.
   

10. 구매 테이블의 데이터 확인하기
    

기타 제약조건

고유 키 제약조건

1. 고유 키 제약조건은 중복되지 않은 유일한 값을 입력해야 하는 조건임.
   1) 기본 키 제약조건과 거의 비슷하지만, 차이점은 고유 키 제약조건은 NULL 값을 허용한다는 것임.
   2) NULL 값은 여러 개가 입력되어도 상관 없음.
   3) 기본 키는 테이블에 1개만 설정해야 하지만, 고유 키는 여러 개를 설정해도 됨.

2. 만약 회원 테이블에 이메일 주소가 있다면, 중복되지 않으므로 고유 키로 설정할 수 있음.
   

3. 데이터 입력하기
   

체크 제약조건

1. 체크 제약조건은 입력되는 데이터를 점검하는 기능을 함.
   1) 예를 들어, 평균 키에 마이너스 값이 입력되지 않도록 하거나 연락처의 국번에 02, 031, 041, 055 중 하나만 입력되도록 할 수 있음.

2. 테이블을 정의하면서 CHECK 제약조건 설정하기
   1) 평균 키는 반드시 100 이상의 값만 입력되도록 설정하기
   

3. 데이터 입력하기
   

4. 테이블을 만든 후에 ALTER TABLE 문으로 제약조건을 추가해도 됨.
   1) 연락처의 국번(phone1)에 02, 031, 032, 054, 055, 061 중 하나만 입력되도록 설정하기
   

5. 데이터 입력하기
   

기본값 정의

1. 기본값 정의는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법임.

2. 키를 입력하지 않고 기본적으로 160이라고 입력되도록 하고 싶다면, 다음과 같이 정의할 수 있음.
   

3. ALTER TABLE 사용 시 열에 DEFAULT를 지정하기 위해서는 ALTER COLUMN 문을 사용함.
   1) 예를 들어, 연락처의 국번을 입력하지 않으면 자동으로 02가 입력되도록 할 수 있음.
   

4. 기본값이 설정된 열에 기본값을 입력하려면 default라 써주고, 원하는 값을 입력하려면 해당 값을 써주면 됨.
   

널 값 허용

1. 널 값을 허용하려면 생략하거나 NULL을 사용하고, 허용하지 않으려면 NOT NULL을 사용함.

2. 다만, PRIMARY KEY가 설정된 열에는 NULL 값이 있을 수 없으므로 생략하면 자동으로 NOT NULL로 인식됨.

3. NULL 값은 아무것도 없다는 의미임.
   1) 공백이나 0과는 다르다는 점에 주의할 것