[혼공SQL] chapter 5. 테이블과 뷰(2)

여정이·2024년 7월 24일
mysql혼공단 12기혼자 공부하는 SQL
0

혼자 공부하는 SQL

목록 보기
15/28

📒 요약 : 테이블에는 기본 키, 외래 키와 같은 제약조건을 설정할 수 있다. 이를 활용하여 데이터의 무결정을 확보하여 더욱 완성도 높은 테이블을 구성할 수 있다.

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

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

제약 조건은 데이터의 무결성을 지키기 위해 제한하는 조건으로, 데이터에 결함이 발생하지 않도록 설정한다. 기본적인 제약 조건의 종류는 아래와 같다. 결함이 없는 상태를 '데이터의 무결성'이라고 표현한다.

  • 기본 키(Primary Key)
  • 외래 키(Foreign Key)
  • 고유 키(Unique)
  • CHECK
  • NOT NULL
  • DEFAULT




2. 기본 키 제약조건

기본 키(PK)는 가장 기본적인 제약 조건으로, 대부분의 테이블은 기본 키를 제약 조건으로 가지고 있다. 기본 키는 하나의 테이블에 존재하는 수많은 데이터들을 구분할 수 있는 식별자의 기능을 한다. 대표적인 예로 member 테이블의 아이디, student 테이블의 학번, employee 테이블의 사원번호 등이 있다. 기본 키는 서로 중복될 수 없고, Null 값을 입력할 수 없다.

기본 키로 생성한 데이터를 자동으로 클러스터형 인덱스가 생성된다. (클러스터형 인덱스에 대해서는 6장에서 다루도록 하자.) 또한 하나의 테이블은 반드시 하나의 기본 키를 가질 수 있다. 다시 말해, 여러 개의 열을 기본 키로 설정할 수 없다. 따라서 테이블의 특성을 가장 잘 반영하는 속성을 기본 키로 설정하는 것이 좋다.

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

먼저 아래는 테이블을 생성할 때, PRIMARY KEY 예약어를 이용하여 제약 조건을 설정하는 sql문이다. mem_id 속성은 회원 테이블의 기본 키가 되었기 때문에 중복된 값을 가질 수 없고, Null 값을 입력하는 것 역시 안 된다.

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

또한 아래와 같이CREATE TABLE문의 마지막 부분에 RIMARY KEY(지정할 열)을 이용하여 제약조건을 설정할 수도 있다. 여기에서는 PRIMARY KEY(mem_id)를 적어주도록 하자. DESCRIBE문으로 테이블의 정보를 확인하면 기본 키 제약조건이 잘 들어간 것을 확인할 수 있다.

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

그림1. member 테이블 정보 확인

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

이미 만들어진 테이블을 수정하여 제약조건을 설정하는 것도 가능하다. 테이블의 정보를 수정할 때에는 ALTER TABLE 구문을 사용한다.

ALTER TABLE member
     ADD CONSTRAINT 
     PRIMARY KEY (mem_id);




3. 외래 키 제약조건

외래 키 제약조건은 두 테이블 사이의 관계를 연결해주고, 그 결과 테이터의 무결성을 보장해주는 역할을 한다. 외래 키가 설정된 열(FK)은 반드시 다른 테이블의 기본 키(PK) 혹은 고유 키(Unique)와 연결되어야 한다. 기본 키가 있는 테이블을 '기준 테이블', 외래 키가 있는 테이블을 '참조 테이블'이라고 한다.

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

테이블을 생성할 때 외래 키를 생성하는 방법은 기본 키를 설정하는 방법과 거의 같다. 먼저 아래와 같이 CREATE TABLE문 끝에 FOREIGN 키워드를 설정하는 방법이 있다. 외래 키의 형식은 FOREIGN KEY(열 이름) REFERENCES 기준_테이블 (열 이름)이다. 만약 기준 테이블의 열이 PK 혹은 UNIQUE가 아니라면 외래 키 관계는 설정되지 않는다. 

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) #buy 테이블의 mem_id 열은 member 테이블의 mem_id를 참조한다. 
);

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

이미 만들어진 테이블에서 외래 키를 설정할 수도 있다. ALTER TABLE 구문을 이용하면 된다.

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											# buy 테이블을 수정한다.
    ADD CONSTRAINT 										# 제약조건을 추가한다.
    FOREIGN KEY(mem_id) REFERENCES member(mem_id);		# 외래 키 제약 조건과 참조한 기준 테이블을 설정한다.

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

기준 테이블의 열이 변경되는 경우, 이를 참조한 테이블과 정보가 서로 일치하지 않게 된다. 그래서 기본 키-외래 키 관계가 형성된 이후에는 기준 테이블의 열 이름을 바꾸려고 하면 오류가 발생한다. 기준 테이블의 열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문이다. 역시 같은 이유로 기준 테이블의 열도 삭제할 수 없다. 따라서 SQL에는 기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름 역시 자동으로 변경되어 데이터 무결성을 지킬 수 있는 기능이 있다. 바로 ON UPDATE CASCATE문과 ON DELETE CASCADE문이다. 두 구문은 각각 기준 테이블의 데이터가 변경되거나 삭제되면 참조 테이블의 데이터도 똑같이 변경/삭제하는 기능을 한다. 아래의 예시를 통해 살펴보자.

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', '맥북');    

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

위와 같이 buy 테이블을 구성하고 데이터를 입력한 뒤, member 테이블의 데이터를 변경해도 오류가 발생하지 않는 것을 확인할 수 있다. 삭제하는 것 역시 잘 동작한다.





4. 기타 제약조건

고유 키 제약조건

고유 키 제약조건(Unique)는 중복되지 않는 유일한 값을 입력해야 하는 조건이다. 기본 키(PK)와 거의 비슷한데, 고유 키 제약조건에는 NULL 값을 입력할 수 있다는 것이 가장 큰 차이점이다. 이 NULL 값은 여러 개가 입력되어도 상관 없다. 또한 기본 키는 하나의 테이블에 하나의 열에만 설정해야 하지만 고유 키는 여러 개의 열에 설정해도 된다. 고유 키는 아래의 예시와 같이 설정할 수 있다. 

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



체크 제약조건

체크 제약조건은 입력되는 데이터를 점검하는 기능을 한다. 입력되는 데이터의 조건을 따진다고 생각하면 된다. 예를 들어, 평균 키에 음수가 입력되지 않도록 하거나, 100 이상의 값만 입력 가능하도록 설정할 수 있다. 체크 제약조건은 아래와 같이 사용할 수 있다.

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),			# 평균 키에는 100 이상의 값만 들어갈 수 있다.
  phone1      CHAR(3)  NULL
);



기본값 정의

기본값 정의는 데이터의 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 것이다. 예를 들어, 아래와 같이 키를 입력하지 않고 기본적으로 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
);

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

INSERT INTO member VALUES('SPC', '우주소녀', default, default);



널 값 허용

데이터에 NULL 값을 허용하려면 테이블을 생성할 때 NULL을 사용하거나 생략하면 되고, 허용하지 않을 경우 NOT NULL을 ㅇ입력하면 된다. 그 동안 많이 해봤으니까 이정도는 쉬울 것이다!



제약조건에 이름 지정하기

제약조건는 별도의 이름이 없다. 필요할 경우 이름을 따로 지정할 수 있는데, 아래와 같이 제약 조건의 이름을 설정할 수 있다. 

CONSTRAINT [제약조건] [제약조건 이름] (열 이름)

#위의 내용 활용 예시
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)
);
profile
여정이
0w0
이전 포스트

[혼공SQL] chapter 5. 테이블과 뷰(1)

다음 포스트

[혼공SQL] chapter 5. 테이블과 뷰(3)

0개의 댓글