Programmers

[20일차]"SQL: 기본키, 외래키, 데이터 타입, 그리고 JOIN

PARKpatchnotes 2025. 9. 29. 14:10

기본키(PK)와 외래키(FK)의 정의와 존재 이유

기본키 (Primary Key)

  • 정의: 테이블 내 각 행(Row)을 고유하게 식별하는 하나의 열(Column) 또는 열들의 조합이다.
  • 특징:
    • 단일 테이블 내에서 반드시 유일해야 한다.
    • NULL 값을 가질 수 없다.
  • 존재 이유:
    • 데이터의 무결성을 보장한다.
    • 테이블의 각 행을 식별할 수 있도록 한다.

외래키 (Foreign Key)

  • 정의: 한 테이블의 특정 열이 다른 테이블의 기본키를 참조하도록 설정된 키이다.
  • 특징:
    • 테이블 간의 관계를 정의한다.
    • 외래키는 참조 무결성을 유지한다(즉, 참조된 데이터는 반드시 존재해야 한다).
  • 존재 이유:
    • 테이블 간 관계를 설정하여 관계형 데이터베이스를 구성한다.
    • 데이터 일관성을 유지한다.

기본키와 외래키 생성 방법

테이블을 생성할 때 설정

기본키 설정

CREATE TABLE Child (
    id INT NOT NULL,
    name VARCHAR(50),
    parent_id INT,
    PRIMARY KEY (id) -- 기본키 설정
);

외래키 설정

CREATE TABLE Parent (
    id INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id) -- 기본키 설정
);

CREATE TABLE Child (
    id INT NOT NULL,
    name VARCHAR(50),
    parent_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (parent_id) REFERENCES Parent(id) -- 외래키 설정
);

테이블 생성 후 ALTER로 설정

기본키 설정

ALTER TABLE Child
ADD PRIMARY KEY (id);

외래키 설정

ALTER TABLE Child
ADD CONSTRAINT fk_parent
FOREIGN KEY (parent_id)
REFERENCES Parent(id);

MariaDB 데이터 타입 상세 설명

정수 데이터 타입

TINYINT

  • 설명: 작은 범위의 정수를 저장하는 데 사용됩니다.
  • 저장 크기: 1바이트
  • 범위:
    • SIGNED: -128 ~ 127
    • UNSIGNED: 0 ~ 255
  • 사용 예시: 상태 플래그(예: 0 = OFF, 1 = ON), 작고 제한적인 숫자 필드

SMALLINT

  • 설명: 중간 크기의 정수를 저장하는 데 사용됩니다.
  • 저장 크기: 2바이트
  • 범위:
    • SIGNED: -32,768 ~ 32,767
    • UNSIGNED: 0 ~ 65,535
  • 사용 예시: 나이, 계정 상태 코드

INT 또는 INTEGER

  • 설명: 일반적인 정수 데이터를 저장하는 데 사용됩니다.
  • 저장 크기: 4바이트
  • 범위:
    • SIGNED: -2,147,483,648 ~ 2,147,483,647
    • UNSIGNED: 0 ~ 4,294,967,295
  • 사용 예시: 사용자 ID, 수량, 가격 등

BIGINT

  • 설명: 매우 큰 정수를 저장하는 데 사용됩니다.
  • 저장 크기: 8바이트
  • 범위:
    • SIGNED: -9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,807
    • UNSIGNED: 0 ~ 18,446,744,073,709,551,615
  • 사용 예시: 고유 식별자(UUID), 대규모 데이터 처리

날짜 데이터 타입

DATE

  • 설명: 날짜를 저장하는 데 사용됩니다.
  • 형식: YYYY-MM-DD (예: '2025-09-29')
  • 범위: 1000-01-01 ~ 9999-12-31
  • 사용 예시: 생년월일, 계약일, 주문 날짜

DATETIME

  • 설명: 날짜와 시간을 저장하는 데 사용됩니다.
  • 형식: YYYY-MM-DD HH:MM:SS (예: '2025-09-29 04:45:15')
  • 범위: 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • 특징: 서버 시간대의 영향을 받지 않음
  • 사용 예시: 생성일, 수정일, 이벤트 시작 시간

TIMESTAMP

  • 설명: UTC(협정 세계시) 기준 날짜와 시간을 저장합니다.
  • 형식: YYYY-MM-DD HH:MM:SS (예: '2025-09-29 04:45:15')
  • 범위: 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC
  • 특징: 서버 및 클라이언트 시간대에 따라 값이 변환됨
  • 사용 예시: 데이터 생성일 및 수정일 자동 기록

TIME

  • 설명: 시간만 저장하는 데 사용됩니다.
  • 형식: HH:MM:SS (예: '12:34:56')
  • 범위: -838:59:59 ~ 838:59:59
  • 특징: 음수(-) 값 지원 (시간 간격 계산에 유용)
  • 사용 예시: 작업 시간, 소요 시간, 시간 차이 기록

문자 데이터 타입

CHAR(n)

  • 설명: 고정 길이 문자열을 저장합니다.
  • 최대 길이: n 문자 (최대 255)
  • 특징: 항상 고정된 길이로 저장 (남는 공간은 공백으로 채움)
  • 사용 예시: 국가 코드('KR', 'US'), 우편번호

VARCHAR(n)

  • 설명: 가변 길이 문자열을 저장합니다.
  • 최대 길이: n 문자 (최대 65,535, 단, 행 전체 크기에 따라 제한될 수 있음)
  • 특징: 실제 데이터 길이에 따라 저장 공간이 동적으로 할당됨
  • 사용 예시: 이름, 주소, 이메일 등 길이가 다양한 문자열

TEXT

  • 설명: 긴 문자열 데이터를 저장합니다.
  • 최대 길이:
    • TINYTEXT: 255자
    • TEXT: 65,535자
    • MEDIUMTEXT: 16,777,215자
    • LONGTEXT: 4,294,967,295자
  • 특징: 매우 긴 텍스트 데이터를 저장할 때 적합
  • 사용 예시: 블로그 글, 설명 필드, 로그 데이터

JOIN

정의

  • 테이블 간 관계를 활용하여 데이터를 조회하는 방법이다.
  • 분리된 테이블의 데이터를 한 번에 가져오기 위해 사용된다.

JOIN 종류와 SQL 예제 결과

테이블 데이터 예시

Parent 테이블

id name
1 Parent 1
2 Parent 2
3 Parent 3

Child 테이블

id name parent_id
1 Child 1 1
2 Child 2 1
3 Child 3 2
4 Child 4 NULL

1. INNER JOIN

  • 정의: 두 테이블 모두에서 ON 조건을 만족하는 일치하는 데이터만 조회된다. (교집합)
  • SQL 예제:
    SELECT 
  Child.id AS child_id,
  Child.name AS child_name,
  Parent.id AS parent_id,
  Parent.name AS parent_name
FROM 
  Child
INNER JOIN 
  Parent
ON 
  Child.parent_id = Parent.id;
  • 결과:
    child_id child_name parent_id parent_name
    1 Child 1 1 Parent 1
    2 Child 2 1 Parent 1
    3 Child 3 2 Parent 2

2. LEFT JOIN

  • 정의: 왼쪽 테이블의 모든 데이터와, 오른쪽 테이블에서 일치하는 데이터를 조회한다. 오른쪽 테이블에 일치하는 데이터가 없을 경우 NULL 값이 반환된다.
  • SQL 예제:
    SELECT 
  Child.id AS child_id,
  Child.name AS child_name,
  Parent.id AS parent_id,
  Parent.name AS parent_name
FROM 
  Child
LEFT JOIN 
  Parent
ON 
  Child.parent_id = Parent.id;
  • 결과:
    child_id child_name parent_id parent_name
    1 Child 1 1 Parent 1
    2 Child 2 1 Parent 1
    3 Child 3 2 Parent 2
    4 Child 4 NULL NULL

3. RIGHT JOIN

  • 정의: 오른쪽 테이블의 모든 데이터와, 왼쪽 테이블에서 일치하는 데이터를 조회한다. 왼쪽 테이블에 일치하는 데이터가 없을 경우 NULL 값이 반환된다.
  • SQL 예제:
    SELECT 
  Child.id AS child_id,
  Child.name AS child_name,
  Parent.id AS parent_id,
  Parent.name AS parent_name
FROM 
  Child
RIGHT JOIN 
  Parent
ON 
  Child.parent_id = Parent.id;
  • 결과:
    child_id child_name parent_id parent_name
    1 Child 1 1 Parent 1
    2 Child 2 1 Parent 1
    3 Child 3 2 Parent 2
    NULL NULL 3 Parent 3

4. FULL OUTER JOIN (MariaDB에서는 UNION으로 대체)

  • 정의: 두 테이블의 모든 데이터를 조회하며, 한쪽 테이블에만 있는 데이터는 NULL 값으로 표시된다. (합집합)
  • SQL 예제:
    SELECT 
  Child.id AS child_id,
  Child.name AS child_name,
  Parent.id AS parent_id,
  Parent.name AS parent_name
FROM 
  Child
LEFT JOIN 
  Parent
ON 
  Child.parent_id = Parent.id
UNION
SELECT 
  Child.id AS child_id,
  Child.name AS child_name,
  Parent.id AS parent_id,
  Parent.name AS parent_name
FROM 
  Child
RIGHT JOIN 
  Parent
ON 
  Child.parent_id = Parent.id;
  • 결과:
    child_id child_name parent_id parent_name
    1 Child 1 1 Parent 1
    2 Child 2 1 Parent 1
    3 Child 3 2 Parent 2
    4 Child 4 NULL NULL
    NULL NULL 3 Parent 3

'Programmers' 카테고리의 다른 글

[22일차]웹 인증과 인가: 개념, 방식, 그리고 구현, next(), .env  (0) 2025.10.02
[21일차]유효성 검사, err처리, 모듈화  (0) 2025.09.30
[19일차]관계형 데이터베이스 관리 시스템(RDBMS) 개념과 주요 특징  (0) 2025.09.25
[18일차]웹 애플리케이션 동작 원리, ERD와 관계 예시  (0) 2025.09.25
[17일차]Object.keys() , 모듈화, API 설계  (0) 2025.09.24

'Programmers'의 다른글

  • 현재글[20일차]"SQL: 기본키, 외래키, 데이터 타입, 그리고 JOIN

관련글

티스토리툴바