모든 가이드로 돌아가기
데이터베이스 설계··읽는 시간 6분

일대다와 다대다: SQL 관계 패턴 설명

SQL에서 일대다 대 다대다 관계에 대해 알아보세요. 외래 키, 교차 테이블 및 실제 사례를 통해 각각을 구현하는 방법을 확인하세요.

고객-주문 외래 키 및 복합 기본 키가 있는 학생-과정 교차 테이블
이 가이드에서
  1. SQL 관계 패턴 이해하기
  2. 일대다 관계
  3. 다대다 관계
  4. 일대일 관계
  5. ERD에서 관계가 나타나는 방식
  6. 올바른 관계 패턴 선택하기
  7. 참조 및 추가 자료

다른 방언이 명시되지 않는 한 SQL 예제는 PostgreSQL을 사용합니다. 짧은 코드 조각은 참조된 테이블이 이미 존재한다고 가정합니다. 적용하기 전에 대상 데이터베이스에서 DDL을 검증하세요.

SQL 관계 패턴 이해하기

관계형 데이터베이스는 테이블 간의 연결에서 그 힘을 얻습니다. 소규모 애플리케이션을 설계하든 기업용 데이터 웨어하우스를 설계하든, 올바른 관계 패턴을 선택하는 것은 데이터를 얼마나 효율적으로 저장, 쿼리 및 유지 관리할 수 있는지를 결정합니다. 세 가지 기본 패턴은—일대일, 일대다, 그리고 다대다—많은 일반적인 연관 관계를 다룹니다. 재귀적 및 고차 관계는 추가 모델링이 필요합니다.

이 가이드에서는 PostgreSQL 예시를 통해 각 패턴을 분석하고, 교차 테이블의 역할을 탐구하며, 이러한 관계가 개체-관계 다이어그램(ERD)에 어떻게 나타나는지 보여줍니다. 지금 바로 자신의 스키마를 시각화하려면 다음을 여세요. Erd Go 편집기 및 붙여넣기 지원됨 CREATE TABLE 다이어그램을 생성하기 위한 정의.

customers와 orders 사이의 ERD 관계로 매핑된 외래 키 SQL
외래 키는 단순한 구문 그 이상입니다. ERD에서는 테이블이 서로 어떻게 의존하는지 설명하는 관계선이 됩니다.

일대다 관계

A 일대다 (1:N) 관계는 데이터베이스 설계에서 가장 일반적인 패턴입니다. 이는 다음의 단일 행이 부모 테이블은 다음의 많은 행에서 참조될 수 있습니다: 자식 테이블이며, null이 아닌 자식 외래 키는 하나의 부모를 가리킵니다. Null을 허용하는 외래 키는 부모가 없는 자식을 허용합니다.

클래식 예시: 부서 및 직원

회사는 여러 부서를 가지고 있으며, 각 부서에는 여러 명의 직원이 포함됩니다. 하지만 모든 직원은 특정 시점에 정확히 하나의 부서에만 속합니다.

CREATE TABLE departments (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    location    VARCHAR(255),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id              SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    department_id   INT NOT NULL,
    hire_date       DATE DEFAULT CURRENT_DATE,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

주요 구현 세부 사항

  • 외래 키 (department_id)는 항상 다음에 위치합니다 다(Many) 측면— employees 이 경우 테이블입니다.
  • ON DELETE RESTRICT 직원이 여전히 할당되어 있는 부서를 실수로 삭제하는 것을 방지합니다.
  • ON UPDATE CASCADE 부서의 기본 키가 변경되면 모든 직원 행이 자동으로 업데이트되도록 보장합니다.
  • 다음에 인덱스 추가 중: department_id 조인 및 부서 필터에 도움이 될 수 있습니다. 대표 데이터와 실행 계획으로 이점을 확인하십시오.

일대다 데이터 쿼리하기

-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';

-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;

다른 일상적인 일대다 예시는 다음과 같습니다 작성자 → 블로그 게시물, 고객 → 주문, 그리고 카테고리 → 제품.

다대다 관계

A 다대다 (M:N) 관계는 다음의 행들이 둘 다 측면은 다른 측면의 여러 행과 연결될 수 있습니다. 관계형 데이터베이스는 단일 외래 키로 이를 직접 표현할 수 없으므로, 다음을 도입합니다 연결 테이블 (또한 ~라고도 불리는 브릿지, 조인, 또는 연관 테이블).

클래식 예시: 학생 및 강의

학생은 여러 과목에 등록할 수 있으며, 각 과목에는 여러 학생이 있을 수 있습니다. 교차 테이블(junction table) enrollments 둘 사이에 위치하며 양쪽 모두에 대한 외래 키를 보유합니다.

CREATE TABLE students (
    id          SERIAL PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    enrolled_on DATE DEFAULT CURRENT_DATE
);

CREATE TABLE courses (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    credits     SMALLINT NOT NULL DEFAULT 3,
    department  VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id  INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    course_id   INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade       CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

연결 테이블(Junction Table)이 필요한 이유

  • 그 복합 기본 키 (student_id, course_id)는 학생-코스 쌍당 하나의 행을 허용합니다. 학기 간 반복 등록이 필요한 경우, 비즈니스 키에 학기를 포함하거나 각 등록을 별도로 모델링하십시오.
  • 다음과 같이 관계 자체에 추가 속성을 첨부할 수 있습니다. grade 또는 enrolled_at, 어느 엔티티에도 단독으로 속하지 않는 것.
  • ON DELETE CASCADE 두 외래 키 모두에 대해 학생이나 강좌를 삭제하면 등록 행이 자동으로 정리됨을 의미합니다.

다대다 데이터 쿼리하기

-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;

-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';

실제 다대다 예시는 다음과 같습니다 사용자 ↔ 역할, 제품 ↔ 태그, 배우 ↔ 영화, 그리고 주문 ↔ 제품 (~를 통해 order_items 수량과 가격도 저장하는 연결 테이블).

일대일 관계

A 일대일 (1:1) 관계는 테이블 A의 각 행이 테이블 B의 최대 한 행에 매핑되고, 그 반대의 경우도 마찬가지임을 의미합니다. 이 패턴은 덜 일반적이지만 거의 액세스되지 않거나 민감한 컬럼을 별도의 테이블로 분리하는 데 유용합니다.

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(60) UNIQUE NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id     INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
    bio         TEXT,
    avatar_url  VARCHAR(500),
    birthdate   DATE,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

다음을 참고하십시오. user_profiles.user_id 둘 다입니다 기본 키 그리고 외래 키. 이 제약 조건은 데이터베이스 수준에서 일대일 매핑을 강제합니다. 각 사용자는 최대 하나의 프로필 행을 가질 수 있습니다.

ERD에서 관계가 나타나는 방식

개체-관계 다이어그램(ERD)은 테이블 간의 선과 기호를 사용하여 카디널리티를 한눈에 전달합니다. 가장 인기 있는 두 가지 표기법은 다음과 같습니다. 까마귀 발 및 Chen 표기법.

관계까마귀 발 기호읽는 방법
일대일||—||각 측면에 정확히 하나
일대다||—<왼쪽에 하나, 오른쪽에 여럿 (까마귀 발 끝)
다대다>—<양쪽 모두 다수; 교차 테이블을 통해 구현됨

이 텍스트 기호들은 최대 카디널리티를 위한 약어입니다. 선택성은 추가적인 0 또는 1 표시가 필요합니다. 외래 키 자체만으로는 부모가 자식을 가질 것을 요구하지 않습니다.

ERD 도구에서 다대다 관계를 모델링할 때 일반적으로 두 개의 엔티티 테이블과 그 사이의 교차 테이블이라는 세 개의 상자가 표시되며, 각 상자는 일대다 선으로 연결됩니다.

이 문서의 SQL 예시 중 하나를 다음에 붙여넣으세요 Erd Go 편집기 외래 키 화살표와 카디널리티 표시가 포함되어 자동으로 생성된 다이어그램을 확인하기 위해.

올바른 관계 패턴 선택하기

잘못된 카디널리티를 선택하면 데이터 중복, 복잡한 쿼리 및 무결성 버그가 발생합니다. 다음 의사 결정 프레임워크를 사용하십시오:

  • A측의 한 행이 B측의 여러 행과 관계를 맺을 수 있지만 그 반대는 불가능합니까? → 일대다. B 쪽에 외래 키를 배치합니다.
  • 양측의 행이 상대측의 여러 행과 관계를 맺을 수 있습니까? → 다대다. 복합 키를 가진 교차 테이블을 생성합니다.
  • 매핑이 엄격하게 1:1이며 테이블 분리가 필요합니까? → 일대일. 외래 키와 동일한 기본 키를 사용합니다.

확신이 서지 않는다면 먼저 스키마를 스케치하세요. 빠른 ERD는 운영 환경에서 제약 조건 오류가 발생하기 훨씬 전에 관계 문제를 드러냅니다. 다음에서 다이어그램을 작성해 보세요: Erd Go 편집기—몇 초면 충분하며 몇 시간의 디버깅을 절약할 수 있습니다.

참조 및 추가 자료

추가 게시물