테이블과 뷰 2 - 제약조건으로 테이블 견고하게 1 : 기본 키 제약조건 및 외래 키 제약조건

오후 공부 시간이 2시간 정도 남아있다. 이 단원을 공부하면 시간이 딱 맞을 것 같다.

테이블에는 기본 키, 외래 키와 같은 제약조건을 설정할 수 있다. 제약조건은 테이블을 구성하는 핵심 개념으로, 이를 잘 이해하고 활용하면 데이터의 오류를 줄여 완전무결한 코드를 만들 수 있다.

 

시작하기 전에

테이블을 만들 때는 테이블의 구조에 필요한 제약조건을 설정해줘야 한다. 기본 키와 외래 키가 대표적인 제약조건이다. 기본 키는 학번, 아이디, 사번 등과 같은 고유한 번호를 의미하는 열에, 외래 키는 기본 키와 연결되는 열에 지정한다.

이메일, 휴대폰과 같이 중복되지 않는 열에는 고유 키를 지정할 수 있다. 회원의 평균 키는 200cm를 넘지 않을 것이다. 이때 실수로 200을 입력하는 것을 방지하는 제약 조건이 체크이다. 회원 테이블에 국적을 입력한다면 99%는 대한면국일 것이다. 매번 입력하기 귀찮다면 제약조건으로 기본값을 설정할 수 있다.

---

 

 

 

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

제약조건은 데이터의 무결성을 지키기 위해 제한하는 조건이다. 데이터의 무결성이란 '데이터의 결함이 없음'이란 의미이다. 만약 회원의 아이디가 중복되면 상당히 큰 혼란이 일어날 것이다.

이러한 결함을 미리 방지하기 위해서 회원 테이블의 아이디를 기본 키로 지정할 수 있다. 기본 키의 조건은 '중복되지 않고, 비어 있지도 않음'이므로, 실수로 중복된 아이디를 넣으려고 해도 입력조차 불가능하다. 기본 키 외에 MySQL에서 제공하는 대표적인 제약조건은 다음과 같다.

• PRIMARY KEY 제약조건
• FOREIGN KEY 제약조건
• UNIQUE 제약조건
• CHECK 제약조건
• DEFAULT 정의
• NULL 값 허용

---

 

 

 

 

기본 키 제약조건

테이블에는 많은 행 데이터가 있다. 이 중에서 데이터를 구분할 수 있는 식별자를 기본 키라고 부른다. 

 

인터넷 쇼핑몰의 기본 키 설정

많은 인터넷 쇼핑몰에서 회원을 구분하기 위해서 기본 키를 아이디로 지정하고 있다. 하지만 설계 방법에 따라서 아이디가 아닌 다른 열을 기본 키로 지정할 수 있다. 기본 키는 중복되지 않고, 비어있지 않으면 되기 때문에 최근에는 주민등록번호나 Email 또는 휴대폰 번호 등으로 지정해서 회원을 구분하는 사이트도 많이 등장하고 있다.

 

 

대부분의 테이블은 기본 키를 가져야 한다. 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다. 한 가지 더 기억할 것은 테이블은 기본 키를 1개만 가질 수 있다. 

---

 

 

 

 

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

CREATE TABLE 문에 PRIMARY KEY 예약어를 넣어주었다.

 

 

처음에는 오류가 떴는데 먼저 naver_db가 만들어진 상태여야 한다. 이렇게 열 이름 뒤에 PRIMARY KEY를 붙여주면 기본 키로 설정된다.

 

이렇게 설정하면 회원 아이디는 회원 테이블의 기본 키가 되었으며, 앞으로 입력되는 회원 아이디는 중복될 수도 비어있을 수도 없다.

 

 

테이블을 삭제하는 순서

 

---

 

 

 

 

 

 

 

여기서는 PRIMARY KEY를 제일 마지막 행에 추가했다.

---

 

 

 

 

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

제약조건을 설정하는 또 다른 방법은 이미 만들어진 테이블을 수정하는 ALTER TABLE 구문을 이용하는 것입니다. ALTER TABLE은 다음과 같이 설정할 수 있다.

 

 

앞서 CREATE TABLE 안에 PRIMARY KEY를 설정한 것과 지금 ALTER TABLE로 PRIMARY KEY를 지정한 것을 모두 동일한 결과를 갖는다.

 

 

기본 키에 이름 지정하기

기본 키는 별도의 이름이 없으며, DESCRIBE 명령으로 확인하면 그냥 PRI로만 나온다. 필요하다면 기본 키의 이름을 직접 지어줄 수도 있다. 예를 들어 PK_member_mem_id와 같은 이름을 붙여주면, 이름 만으로도 'PK가 member 테이블의 mem_id 열에 지정됨'이라고 이해할 수 있다. 그러기 위해서는 다음과 같이 사용하면 된다.

 

---

 

 

 

 

외래 키 제약조건

외래 키 제약조건은 두 테이블 사이의 관계를 연결해 주고, 그 결과 데이터의 무결성을 보장해 주는 역할을 한다. 외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결된다.

우리가 사용하는 회원 테이블과 구매 테이블이 바로 대표적인 기본 키 - 외래 키 관계이다. 여기서 기본 키가 있는 회원 테이블을 기준 테이블이라고 부르며, 외래 키가 있는 구매 테이블을 참조 테이블이라고 부른다.

 

구매 테이블의 아이디(PK)는 반드시 회원 테이블의 아이디(PK)로 존재한다. 그러므로 구매한 기록은 있으나 구매한 사람이 누구인지 모르는 심각한 일은 절대 발생하지 않는다. 구매 테이블의 데이터는 모두 누가 구매했는지 확실히 알 수 있는 무결한(오류가 없는) 데이터가 된다.

이전에 APN을 입력할 때 오류가 발생했던 이유는 외래 키 제약조건을 위반했기 때문이다. 아직 가입도 하지 않은 APN이 물건을 구매하려고 시도했었다.

 

또 하나 기억할 것은 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나 고유 키로 설정되어 있어야 한다.

---

 

 

 

 

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

외래 키를 생성하는 방법은 CREATE TABLE 끝에 FOREIGN KEY 키워드를 설정하는 것이다.

 

 

여기서 보면 구매 테이블(buy)의 열(mem_id)이 참조하는 기준 테이블(member)의 열(mem_id)은 기본 키로 설정이 되어 있다. 만약, 기준 테이블의 열이 PRIMARY KEY 또는 UNIQUE가 아니라면 외래 키 관계는 설정되어 있지 않다.

---

 

 

 

기준 테이블의 열 이름과 참조 테이블의 열 이름

여기서는 둘의 열 이름이 같지만 참조 테이블의 아이디 열 이름이 달라도 상관없다.

 

---

 

 

 

 

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

외래 키를 설정하는 다른 방법은 ALTER TABLE 구문을 이용하는 것이다.

 

---

 

 

 

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

예를 들어 회원 테이블의 BLK가 물품을 2건 구매한 상태에서 회원 아이디를 PINK로 변경하면 어떻게 될까? 두 테이블의 정보가 일치하지 않게 된다.

이 그림을 코드로 확인해 보면 다음과 같다. 먼저 첫 번째 그림처럼 정상적으로 데이터를 입력했다.

 

 

 

 

이제 내부 조인을 사용해서 물품 정보 및 사용자 정보를 확인해 보겠다.

 

 

 

이번에는 BLK의 아이디를 PINK로 변경해 보겠다.

 

하지만 오류가 떴다. 기본 키 - 외래 키로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않는다. 열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문이다.

지금은 회원 테이블의 BLK가 물건을 구매한 기록이 구매 테이블에 존재하기 때문에 변경할 수 없는 것이다. 만약 BLK가 물건을 구매한 적이 없다면(구매 테이블에 데이터가 없다면) 회원 테이블의 BLK는 변경 가능하다.

 

 

 

역시 삭제하려 하자 오류가 발생한다. 기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경되면 더 효율적일 것 같다.

 

이런 기능을 지원하는 것이 ON UPDATE CASCADE 문이다. 이것은 기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되는 기능이다. 다시 테이블을 생성하고 ALTER TABLE 문도 수행해 보겠다.

 

 

 

 

이제 구매 테이블에 데이터를 다시 입력해 본다.

 

 

데이터를 입력했고 회원 테이블의 이름도 변경했는데 오류 없이 잘 되었다.

 

 

아이디가 모두 잘 변경된 것을 확인할 수 있다. 이번에는 PINK가 탈퇴한 것으로 가정하고 기준 테이블에서 삭제해 보자.

 

 

 

구매 테이블의 데이터에 아무것도 남지 않음을 볼 수 있다.

---

 

 

 

 

학습을 마치고

여러 가지 제약조건에 대해 학습해 보았다. 기타 제약조건은 분량이 많아서 다음 포스트에서 이어서 하려고 한다. 역시 난 인사교에 오면 집중이 집에서 하는 것의 반도 되지 않아서 오늘도 학습 목표를 채우지 못했다.

그리고 요즘 감기 걸린 사람들이 있어서 기침을 1분에 한번 꼴로 하는 사람이 있어 그것도 무척이나 귀에 거슬렸다. 가끔은 분노가 문제를 해결하는 열쇠가 되기도 한다.