MySQL/혼공SQL

13강. SQL 테이블 제약조건(기본키, 외래키, 고유키)

묘걍 2023. 11. 24. 18:16

05-2. 제약조건으로 테이블을 견고하게

👉🏻 제약 조건

- 테이블을 견고하게 만든다

- 데이터가 완전무결하게 도와준다

 

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

✅ 기본키 제약 조건

- ID는 기본키(Primary key) / 중복 불가

- 중복을 허용해버리면 이메일을 보낼 때 누구에게 보내는 건지 알 수 없다

- 혼란스러운 상황을 미연에 방지

     - 데이터 결함이 없어지는 것

 

  1. 기본키
  2. 외래키
  3. 고유키
  4. 체크
  5. 기본값
  6. NOT NULL

👉🏻 기본키 제약 조건

🧩 기본키

  • 데이터를 구분할 수 있는 식별자
    • 카카오톡의 수많은 회원 행들 중에서 아이디(기본키)만 알면 어떤 행인지 알 수 있다
  • 값은 중복될 수 없다
  • NULL값이 허용되지 않는다

- 기본키 제약 조건은 가장 필수적으로 사용해야하는 제약 조건

- 테이블에는 기본키 제약 조건을 하나의 열에 지정할 수 있다

🧩 클러스터형 인덱스

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

🧩 하나의 열만

  • 테이블에 기본 키를 하나만 지정할 수 있다
  • 만약 ID 외에 주민등록번호나 이메일 등이 있다면 모두 중복되지도 않고 비어있지도 않은 열이다. 지정 가능할까?
    • 셋 다 기본 키가 될 자격은 있지만 그 중 하나를 선택해야 한다

출처: 혼공SQL 유튜브

🎮 회원 테이블 만들기(1)

- 네이버 DB 사용

- buy테이블과 member 테이블이 있다면 지워라

- 멤버 테이블 만들기

- 기본키로 지정: 열 뒤에  PRIMARY KEY 명시

- 제일 많이 쓰는 방식

- 잘 만들어짐

🎮 테이블 구성 보기

- DESCRIBE 테이블명

- key에 PRI 써 있는 것이 PRIMARY 키로 지정됐다는 것

🎮 회원 테이블 만들기(2)

- 다시 만들기 위해 지우기

- 열을 모두 지정한 뒤에 마지막에 PRIMARY KEY를 지정하기

- 문법만 다를 뿐 위와 동일

🎮 회원 테이블 만들기(3)

- 다시 만들기 위해 지우기

- 테이블을 먼저 만들고

- ALTER TABLE : 테이블을 수정하라

- ADD CONSTRAINT : 제약조건을 추가하라

- PRIMARY KEY: 다음의 컬럼을 프라이머리키로 지정

 

👉🏻 외래키 제약 조건

- 두 테이블의 관계를 연결

- 데이터의 무결성 보장

🧩 기준 테이블과 참조 테이블

출처: 혼공 SQL

- 회원 테이블의 PK(ID)를 구매 테이블의 FK(ID)와 연결

- 1:다의 관계

- 회원이 아니면 구매할 수 없다

- 구매한 사람들은 무조건 우리 회원이다

     - 데이터의 무결성

- 참조 테이블의 외래키로 지정하기 위해 연결시킨 기준 테이블의 컬럼은 Primary Key나 Unique Key로 지정해야 한다

🎮 회원 테이블과 구매 테이블 만들기(1)

- 이전에 만든 것을 지우기

- 멤버 테이블 만들기

- 구매 테이블 만들기

- 회원 테이블의 mem_id와 연결하기 위해서 FOREIGN KEY 지정, REFERENCE로 테이블 지정

🎮 회원 테이블과 구매 테이블 만들기(2)

- 이전 테이블을 지우고

- 회원 테이블을 만든다

- 구매 테이블을 만든다

- 외래키 컬럼명을 꼭 기준 테이블의 기본키와 똑같이 설정하지 않아도 된다

- 권장하지는 않는다 (나중에 혼란스러울 수 있음)

- 가능하면 동일하게 쓰기

🎮 회원 테이블과 구매 테이블 만들기(3)

- 구매 테이블만 새로 만들어보기

- 기존 방식대로 (외래키 지정 없이) 만들고

- ALTER TABLE을 통해 수정

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

출처: 혼공SQL 유튜브

- 위와 같이 기준 테이블의 데이터가 변경되는 등의 상황이 발생된다면?

🎮 블랙핑크로 알아보기

- 블랙핑크 회원 등록

- 물건 두 개 구매

- 두 테이블 조인

- UPDATE를 통해 id 바꾸기

- 오류 발생

- 이미 PK - FK 관계로 연결돼있기 때문에 불가능

- 매우 좋은 기능 (혼란 미연에 방지)

- 회원 탈퇴

- 데이터 삭제도 안됨

- BLK가 사라지면 BLK가 구매한 내용이 붕 뜸

- 결함 있는 데이터가 됨

🧩 ON UPDATE/DELETE CASCADE

출처: 혼공SQL 유튜브

- 연결된 내용이 자동으로 바뀐다면 문제 없을 것!

- 일일히 찾아 바꾸는 게 아니라 자동으로 바꾸는 것

- 변경과 삭제에 대하여

🎮 예제

- 테이블 지우고 다시 만들기

- ALTER TABLE을 통해 테이블 수정

- 여기서 CASCADE 문을 작성

- 다시 데이터 넣기

- UPDATE를 통해 ID 수정

- INNER JOIN을 통해 SELECT로 조회

- PINK로 바뀌었음

- 삭제해보기

- 성공

- 확인해보면

- id가 PINK인게 다 지워졌기 때문에 안 보임

출처: 혼공SQL 유튜브

👉🏻 기타 제약 조건

🧩 고유 키 제약조건 (UNIQUE 제약 조건)

  • 중복되지 않는 유일한 값을 입력
  • 기본 키와 비슷하지만, 기본키는 NULL값을 허용하지 않음
  • 고유키는 중복만 아니면 됨, NULL값 허용
  • Ex) 이미 ID를 기본키로 설정했는데 email도 있을 때, email은 기본키와 비슷(중복X)

🎮 예제

- 테이블 삭제

- 회원 테이블 만들기

- 블랙핑크

- 등록됨

- 트와이스

- 이메일은 없어도 됨

- 등록됨

- 에이핑크

- 안됨

- 블랙핑크와 이메일이 같아서

🧩 체크 제약조건

  • 특정 범위, 특정 값만 입력되도록 하는 제약조건
  • Ex) 국번에 123, 평균 키에 음수값, ...
  • 형식: CHECK(조건)
  • 잘못된 데이터를 막을 수 있는 데이터의 무결성이 추가됨

🎮 키에 체크 제약조건

- 테이블 삭제

- 테이블 만들기

- 키 컬럼을 만들어 100이상의 값만 들어오도록 하기

- 블랙핑크

- 정상적으로 입력됨

- 트와이스

- 100보다 작게 입력해서 입력 불가

🎮 국번에 체크 제약조건

- IN을 통해 여러가지 조건을 한 번에

- 트와이스, 입력됨

- 오마이걸

- 오류남, 조건에 맞지 않는 010을 입력했기 때문에

 

🧩 기본값(default 값) 정의

  • 내가 따로 입력하지 않으면 자동으로 들어갈 값

🎮 예제

- 테이블 삭제 후

- 테이블 만들기

- 키에 디폴트값 160

- ALTER TABLE로 수정해서 국번도 기본값 02로

- ALTER COLUMN으로 바꿔야함

- 레드벨벳 등록됨

- 우주소녀 키와 국번 지정하지 않고 디폴트값으로

- 확인

-160과 02가 자동으로 들어감

🧩 NULL 값 허용

  • NULL: 값이 비어 있는 것을 허용한다
  • NOT NULL: 값이 비어 있는 것을 허용하지 않는다

 

 

 

 

 

출처: https://youtu.be/BUHj-behLyc?si=LuBofmcHWnJXTU7W