Sieun Kim

안녕하세요 :)

7 분 소요

테이블과 뷰

제약조건으로 테이블을 견고하게

테이블을 만들 때는 테이블의 구조에 필요한 제약조건을 설정해줘야 한다.
기본 키(Primary Key)와 외래 키(Foreign Key)가 대표적인 제약조건이다.
기본 키는 고유한 번호를 의미하는 열에, 외래 키는 기본 키와 연결되는 열에 지정한다.

이메일, 휴대폰과 같이 중복되지 않는 열에는 고유 키(Unique)를 지정할 수 있다.
당연히 200cm를 넘을 리 없는 회원의 평균 키를 실수로 200으로 입력하는 것을 방지하는 제약조건은 체크(Check)이다.
회원의 국적의 99%가 대한민국일 때 매번 입력하기 귀찮다면 제약조건으로 기본값(Default)을 설정할 수 있다.
또한, 값을 꼭 입력해야 하는 NOT NULL 제약조건도 있다.

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

제약조건(constraint)은 데이터의 무결성을 지키기 위해 제한하는 조건이다.
일단 데이터의 무결성이란 ‘데이터에 결함이 없음’이란 의미이다.
간단한 예로 네이버 회원의 아이디가 중복되면 이메일, 블로그, 쇼핑 기록 등 상당한 혼란이 일어날 것이다.
이런 것이 바로 데이터의 결함이고, 이런 결함이 없는 것을 데이터의 무결성이라고 표현한다.

MySQL에서 제공하는 대표적인 제약조건은 다음과 같다.

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

기본 키 제약조건

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

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

대부분의 테이블은 기본 키를 가져야 한다.
기본 키가 없어도 테이블 구성이 가능하지만 실무에서 사용하는 테이블에는 기본 키를 설정해야 중복된 데이터가 입력되지 않는다.

테이블은 기본 키를 1개만 가질 수 있다.
어떤 열에 설정해도 문버밧ㅇ 문제는 없으나 테이블의 특성을 가장 잘 반영하는 열을 선택하는 것이 좋다.

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

열 이름 뒤에 PRIMARY KEY를 붙여주면 기본 키로 설정된다.

USE naver_db;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id    CHAR(8) NOT NULL PRIMARY KEY,
 mem_name  VARCHAR(10) NOT NULL,
 height    TINYINT UNSIGNED NULL
);

이렇게 설정함으로써 회원 아이디(mem_id)는 회원 테이블(member)의 기본 키가 되었으며, 앞으로 입력되는 회원 아이디는 당연히 중복될 수 없고, 비어 있을 수도 없다.

CREATE TABLE에서 기본 키를 지정하는 다른 방법은 제일 마지막 행에 PRIMARY KEY(열_이름)을 추가하는 것이다.

DROP TABLE IF EXISTS member;
CREATE TABLE member
(mem_id    CHAR(8) NOT NULL,
 mem_name  VARCHAR(10) NOT NULL,
 height    TINYINT UNSIGNED NULL,
 PRIMARY KEY(mem_id)
);

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

제약조건을 설정하는 또 다른 방법은 이미 만들어진 테이블을 수정하는 ALTER TABLE 구문을 사용하는 것이다.

DROP TABLE IF EXISTS member;
CREATE TABLE member
(mem_id    CHAR(8) NOT NULL,
 mem_name  VARCHAR(10) NOT NULL,
 height    TINYINT UNSIGNED NULL
);

ALTER TABLE member
    ADD CONSTRAINT
    PRIMARY KEY (mem_id);

  1. member를 변경한다.

  2. 제약조건을 추가한다.

  3. mem_id 열에 기본 키 제약조건을 설정한다.

기본 키에 이름 지정하기

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

DROP TABLE IF EXISTS member;
CREATE TABLE member
(mem_id    CHAR(8) NOT NULL,
 mem_name  VARCHAR(10) NOT NULL,
 height    TINYINT UNSIGNED NULL,
 CONSTRAINT PRIMARY KEY PK_member_mem_id (mem_id)
);

외래 키 제약조건

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

기본 키가 있는 테이블을 기준 테이블이라고 부르며, 외래 키가 있는 테이블을 참조 테이블이라고 부른다.

참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키(Primary Key)나 고유 키(Unique)로 설정되어 있어야 한다.

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

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

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id    CHAR(8) NOT NULL PRIMARY KEY,
 mem_name  VARCHAR(10) NOT NULL,
 height    TINYINT UNSIGNED NULL
);
CREATE TABLE buy
(num        INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
 mem_id     CHAR(8) NOT NULL,
 prod_name  CHAR(6) NOT NULL,
 FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

외래 키의 형식은 FOREIGN KEY(열_이름) REFERENCES 기준_테이블(열_이름)이다.
만약 기준 테이블의 열이 Primary Key 또는 Unique가 아니라면 외래 키 관계는 설정되지 않는다.

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

기준 테이블의 열 이름과 참조 테이블의 열 이름이 동일하지 않아도 상관없다.

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

DROP TABLE IF EXISTS buy;
CREATE TABLE buy
(num        INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
 mem_id     CHAR(8) NOT NULL,
 prod_name  CHAR(6) NOT NULL
);
ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id)
    REFERENCES member(mem_id);

  1. buy를 수정한다.

  2. 제약조건을 추가한다.

  3. 외래 키 제약조건을 buy 테이블의 mem_id에 설정한다.

  4. 참조할 기준 테이블은 member 테이블의 mem_id 열이다.

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

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

먼저 데이터를 입력하겠다.

INSERT INTO member VALUES('BLK', '블랙핑크', 163);
INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
INSERT INTO buy VALUES(NULL, 'BLK', '맥북');

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

SELECT M.mem_id, M.mem_name, B.prod_name
    FROM buy B
      INNER JOIN member M
      ON B.mem_id = M.mem_id;

스크린샷 2024-06-01 184559

-> 결과가 제대로 나왔다.

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

UPDATE member SET mem_id = 'PINK' WHERE mem_id='BLK';

-> 오류가 발생했다.

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

삭제도 시도해보겠다.

DELETE FROM member WHERE mem_id='BLK';

-> 같은 오류로 삭제되지 않는다.

기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경되면 더 효율적일 것 같다.

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

DROP TABLE IF EXISTS buy;
CREATE TABLE buy
(num        INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
 mem_id     CHAR(8) NOT NULL,
 prod_name  CHAR(6) NOT NULL
);
ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

구매 테이블에 데이터를 다시 입력한다.

INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
INSERT INTO buy VALUES(NULL, 'BLK', '맥북');

이제 회원 테이블의 BLK를 PINK로 변경해보겠다.

UPDATE member SET mem_id = 'PINK' WHERE mem_id='BLK';

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

SELECT M.mem_id, M.mem_name, B.prod_name
    FROM buy B
      INNER JOIN member M
      ON B.mem_id = M.mem_id;

스크린샷 2024-06-01 192338

-> 기준 테이블과 참조 테이블의 아이디가 모두 변경된 것을 확인할 수 있다.

이번에는 PINK를 기준 테이블에서 삭제해보겠다.

DELETE FROM member WHERE mem_id='PINK';

-> 오류 없이 잘 실행된다.

SELECT * FROM buy;

스크린샷 2024-06-01 192649

-> 구매 테이블의 데이터를 확인하면 아무것도 없다.

회원 테이블의 PINK를 삭제하면 PINK의 구매 기록도 함께 삭제된다.

기타 제약조건

고유 키 제약조건

고유 키(Unique) 제약조건은 ‘중복되지 않은 유일한 값’을 입력해야 하는 조건이다.
이것은 기본 키 제약조건과 거의 비슷하지만, 차이점은 고유 키 제약조건은 NULL 값을 허용한다는 것이다.
또, 기본 키는 테이블에 1개만 설정해야 하지만, 고유 키는 여러 개를 설정해도 된다.

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

DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id    CHAR(8) NOT NULL PRIMARY KEY,
 mem_name  VARCHAR(10) NOT NULL,
 height    TINYINT UNSIGNED NULL,
 email     CHAR(30) NULL UNIQUE
);

데이터를 입력해보겠다.

INSERT INTO member VALUES('BLK', '블랙핑크', 163, 'pink@gmail.com');
INSERT INTO member VALUES('TWC', '트와이스', 167, NULL);
INSERT INTO member VALUES('APN', '에이핑크', 164, 'pink@gmail.com');

-> 세 번째 행은 이메일이 중복되기 때문에 오류가 발생한다.

체크 제약조건

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

평균 키는 반드시 100 이상의 값만 입력되도록 설정하겠다.
열의 정의 뒤에 CHECK (조건)을 추가해주면 된다.

DROP TABLE IF EXISTS member;
CREATE TABLE member
(mem_id    CHAR(8) NOT NULL PRIMARY KEY,
 mem_name  VARCHAR(10) NOT NULL,
 height    TINYINT UNSIGNED NULL CHECK (height >= 100),
 phone1    CHAR(3) NULL
);

데이터를 입력해보겠다.

INSERT INTO member VALUES('BLK', '블랙핑크', 163, NULL);
INSERT INTO member VALUES('TWC', '트와이스', 99, NULL);

-> 두 번째 행은 체크 제약조건에 위배되므로 오류가 발생한다.
Check constraint 오류는 체크 제약조건에서 설정한 값의 범위를 벗어났기 때문에 발생한 것이다.

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

ALTER TABLE member
    ADD CONSTRAINT
    CHECK (phone1 IN ('02', '031', '032', '054', '055', '061'));

데이터를 입력해보겠다.

INSERT INTO member VALUES('TWC', '트와이스', 167, '02');
INSERT INTO member VALUES('OMY', '오마이걸', 167, '010');

-> 두 번째 행은 체크 제약조건에 위배되므로 오류가 발생한다.

기본값 정의

기본값(Default) 정의는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.

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

DROP TABLE IF EXISTS member;
CREATE TABLE member
(mem_id    CHAR(8) NOT NULL PRIMARY KEY,
 mem_name  VARCHAR(10) NOT NULL,
 height    TINYINT UNSIGNED NULL DEFAULT 160,
 phone1    CHAR(3) NULL
);

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

ALTER TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';

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

INSERT INTO member VALUES('RED', '레드벨벳', 161, '054');
INSERT INTO member VALUES('SPC', '우주소녀', default, default);
SELECT * FROM member;

스크린샷 2024-06-01 200651

널 값 허용

널(NULL) 값을 허용하려면 생략하거나 NULL을 사용하고, 허용하지 않으려면 NOT NULL을 사용한다.
다만 PRIMARY KEY가 설정된 열에는 NULL 값이 있을 수 없으므로 생략하면 자동으로 NOT NULL로 인식된다.

NULL 값은 ‘아무 것도 없다’라는 의미로 공백(‘‘)이나 0과는 다르다

참고