[SQL] 테이블 제약조건

테이블

• 테이블은 표 형태로 구성되 2차원 구조로, 행과 열로 구성되어 있다.
• 행은 로우(row) 나 레코드(record) 라고 부른다.
• 열은 컬럼(column) 또는 필드(field) 라고 부른다.

 

제약조건

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

이메일, 휴대폰과 같이 중복되지 않는 열에는 고유키(UNIQUE KEY) 를 지정할 수 있다.

잘못된 입력을 방지하는 체크(CHECK) 제약조건

매번 입력하기 귀찮다면 기본값(DEFAULT)

값을 꼭 입력해야 하는 NOT NULL 제약조건을  설정할 수 있다.

 

 

제약조건 기본 개념과 종류

제약조건(constraint)은 데이터의 무결성을 지키기 위해 제한하는 조건이다.

네이버 회원의 아이디가 중복되어 저장된다면 데이터의 결함

이런 결함이 없는 것을 데이터의 무결성이라고 한다.

 

이러한 결함을 미리 방지하기 위해서 회원 테이블의 아이디를 기본 키(PRIMARY KEY)로 지정할 수 있다.

기본 키의 조건은 '중복되지 않고, 비어 있지도 않음' 이므로, 실수로 중복된 아이디를 넣으려 해도 입력 조차 불가능하다.

기본 키 외에 MySQL 에서 제공되는 대표적인 제약조건은 다음과 같습니다.

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

 

기본 키 제약조건

• 테이블에는 많은 행들이 있다. 이 중에서 데이터를 구분할 수 있는 식별자를 기본키라고 부른다.
• 기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없다.
• 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다.
• 기본 키는 테이블 당 1개이다.
• 기본 키 - 외래 키 관계로 연결된 테이블은 외래 키가 설정된 테이블 먼저 삭제해야 한다.

 

 

외래 키 제약조건

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

 

구매 테이블의 아이디(FK) 는 반드시 회원 테이블의 아이디(FK)로 존재한다.

그러므로 구매한 기록은 있으나 구매한 사람이 누구인지 모르는 심각한 일은 절대 발생하지 않는다.

구매 테이블의 데이터는 모두 누가 구매했는지 확실히 알 수있는, 무결한 데이터가 되는 것이다.

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

 

 

외래 키 설정 방법

• 외래 키의 형식은 FOREIGN KEY(열 이름) REFERENCES 기준 테이블(열_이름) 이다.
• 기준 테이블의 열 이름과 참조 테이블의 열 이름이 달라도 된다.

FOREIGN KEY(user_id) REFERENCES member(mem_id)

 

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

• ALTER TABLE 구문을 사용해 설정할 수도 있다.

ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id)
    REFERENCES member(mem_id);

 

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

예를 들어 회원 테이블의 BLK 가 물품을 2개 구매한 상태에서 회원 아이디를 PINK로 변경, 삭제되면 어떻게 될까?

두 테이블의 정보가 일치하지 않게된다.

 

 

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

 

 

 

 

"기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경되면 더 효율적이지 않을까?"

이런 기능을 지원하는 것이 ON UPDATE CASCADE 문이다.

ON DELETE CASCADE 문은 기준 테이블의 데이터 삭제되면 참조 테이블의 데이터도 삭제되는 기능이다.

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

 

 

고유키 제약조건

고유 키(UNIQUE KEY) 제약조건은 '중복되지 않은 유일한 값' 을 입력해야하는 조건이다.

이것은 기본 키와 비슷하지만, 차이점은 고유 키 제약조건은 NULL 값을 허용한다는 것이다.

또, 기본 키는 테이블에 1개만 설정해야 하지만 고유 키는 여러 개를 설정해도 된다. 

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); -- 고유 키 NULL 값 O
INSERT INTO member VALUES ('APN', '블랙핑크', 164, 'pink@gmail.com'); -- 고유 키 중복 X

 

 

 

체크 제약조건

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

 

먼저 테이블을 정의하면서 CHECK 제약조건을 설정해보자.

평균 키는 반드시 100 이상의 값만 입력되도록 설정했다.

열의 정의 뒤에 CHECK(조건)을 추가해주면 된다.

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

 

 

ALTER TABLE 문으로 CHECK 제약 조건

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'); -- 오류

 

 

 

 

기본값 정의

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

키를 입력하지 않고 165 이라고 입력되도록 하고 싶다면 다음과 같이 정의할 수 있다.

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 TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';

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

 

 

NULL 값 허용

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

기본키가 설정된 열에는 NULL 값이 있을 수 없으므로 생략하면 자동으로 NOT NULL 처리가 된다.

 

 

 

 

해당 포스팅의 글과 내용은 우재남 저자님의 혼자 공부하는 SQL 책을 재구성하여 만들었습니다.

좀 더 자세한 내용이 필요하시다면 '혼자 공부하는 SQL 책'을 구매해주세요

우재남 저자님 카페: https://cafe.naver.com/thisisMySQL

이것이MySQL이다 : 네이버 카페