[MySQL] 제약조건에 대해 알아보자

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

제약 조건 : 데이터의 무결성을 지키기 위해 제한하는 조건

데이터의 무결성 : ‘데이터에 결함이 없음’

 

MySQL에서 제공하는 제약조건

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• CHECK
• DEFAULT
• NULL 값 허용

 

기본 키 제약조건

기본 키 (Primary key) : 데이터를 구분할 수 있는 식별자, 중복 X, NULL값 입력 X

                                    기본키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다.

 

 

- 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
);

 

📌 제일 마지막 행에 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);

 

 

- 기본 키에 이름 지정하기

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)
  // 'PK가 member 테이블의 member_id 열에 지정됨'

 

 

 

외래 키 제약조건

외래 키 (Foreign key) : 두 테이블 사이의 관계 연결 → 데이터의 무결성 보장

 

외래 키가 설정된 열은 반드시 다른 테이블의 기본 키와 연결된다.

 

기본 키가 있는 테이블 : 기준 테이블 / 외래 키가 있는 테이블 : 참조 테이블

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

 

 

- 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 기준_테이블(열_이름)

 

 

- 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);

 

 

 

PK-FK 관계가 설정되면 기준 테이블의 열은 변경되거나 삭제되지 않는다.

→ 기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경된다면??

→ ON UPDATE CASCADE 문 사용하기

 

 

 

ON UPDATE CASCADE 문

ON UPDATE CASCADE

ON DELETE CASCADE

기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되는 기능이다.

 

 

 

고유 키 제약조건

고유 키(Unique) : 중복되지 않는 유일한 값을 입력해야 하는 조건

📌 NULL 값 허용, 여러 개 설정 가능

 

 

- 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 mamber VALUES('BLK', '블랙핑크', 163, 'pink@gmail.com');
INSERT INTO member VALUES('TWC', '트와이스', 167, NULL);
INSERT INTO member VALUES('APN', '에이핑크', 164, 'pink@gmail.com');

-> 중복되는 값이 있기 때문에 오류가 발생한다. (Action Output line 90)

 

 

 

체크 제약조건

체크 제약조건 : 입력되는 데이터를 점검하는 기능이다.

열의 정의 뒤에 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 ),
  phone    CHAR(3) NULL
);

// CHECK 조건을 위반하는 데이터를 포함해 입력
INSERT INTO member VALUES('BLK', '블랙핑크', 163, NULL);
INSERT INTO member VALUES('TWC', '트와이스', 99, NULL);

-> CHECK 조건을 위반했다는 오류가 발생한다.

 

 

📌 테이블을 만든 뒤 ALTER TABLE 문으로 CHECK 제약조건 추가하기

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

// CHECK 조건을 위반하는 데이터를 포함해 입력
INSERT INTO member VALUES('TWC', '트와이스', 167, '02');
INSERT INTO member VALUES('OMY', '오마이걸', 167, '010');

-> CHECK 조건을 위반했다는 오류가 발생한다.

 

 

 

기본 값 정의

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

 

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 사용시 ALTER COLUMN 문을 사용해 DEFAULT 지정

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;

-> default 로 작성한 부분이 기본 값으로 출력된 것을 확인할 수 있다.