6. 관계와 제약조건

1.관계란?

관계는 여러 테이블에 분산 저장된 데이터가 서로 어떻게 연결돼 있는지를 정의하는 개념이다.

테이블이 서로 관련 있는 데이터를 묶은 것이라면, 관계는 여러 테이블에 흩어져 있는 데이터 간 연결고리를 맺어주는 것이다.

관계는 크게 일대일(1:1), 일대다(1:N), 다대다(N:M) 관계로 나뉜다.

 

1.1 일대일(1:1) 관계

하나의 데이터가 하나의 데이터와만 연결된 관계이다.

A 테이블의 한 튜플이 B 테이블의 특정 튜플과 1:1로 매칭될 때 일대일 관계 라고 한다.

 

ex) 사용자(User)와 프로필(Profile) 테이블

CREATE TABLE User (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE Profile (
    profile_id INT PRIMARY KEY,
    user_id INT UNIQUE,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES User(user_id)
);

 

- Profile 테이블의 user_id는 User 테이블의 user_id를 참조하는 외래키(FK)이다.

- UNIQUE 제약 조건을 사용하여 한 사용자가 하나의 프로필만 가지도록 설정한다.

 

1.2 일대다(1:N) 관계

하나의 데이터가 여러 데이터와 연결된 관계이다.

A 테이블의 한 튜플이 B 테이블의 여러 튜플과 연결된다면 일대다 관계라고 한다.

 

ex) 사용자(User)와 게시글(Post) 테이블

CREATE TABLE User (
    user_id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

CREATE TABLE Post (
    post_id INT PRIMARY KEY,
    user_id INT,
    title VARCHAR(100) NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES User(user_id)
);

 

- Post 테이블의 user_id가 User 테이블의 user_id를 참조하는 외래키(FK) 이다.

- 한 사용자가 여러 개의 게시글을 작성할 수 있도록 설계되었다.

 

1.3 다대다(N:M) 관계

여러 데이터가 여러 데이터와 연결된 관계이다.

다대다 관계를 구현하기 위해서는 중간 테이블을 사용한다.

 

ex) 학생(Student)과 강의(Course) 테이블

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL
);

CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

 

- Enrollment 테이블은 Student와 Course의 관계를 연결하는 중간 테이블이다.

- 복합 기본키(PK)를 사용해 student_id와 course_id가 동시에 중복되지 않도록 설정한다.

- 이를 통해 한 학생이 여러 강의를 수강할 수 있고, 한 강의에 여러 학생이 등록될 수 있다.

 

 

2. 기본키(PK)와 외래키(FK)

2.1 기본키(Primary Key, PK)

테이블 내에서 모든 튜플을 유일하게 식별할 수 있는 칼럼 또는 칼럼 조합이다.

 

특징:

- 유일성(Unique): 같은 값을 가지는 행이 존재할 수 없다.

- 비 NULL(Not Null): 반드시 값이 존재해야 한다.

- 불변성(Immutability): 한 번 지정된 값은 변경되지 않는다.

 

2.2 외래키(Foreign Key, FK)

다른 테이블의 기본키를 참조하는 칼럼이다.

 

특징:

- 참조 무결성: 참조하는 값이 부모 테이블에 존재해야 한다.

- NULL 허용 가능: 특정 데이터가 관계를 맺지 않을 경우 NULL 값을 가질 수 있다.

 

사용방법:

FOREIGN KEY (칼럼명) REFERENCES 연결대상_테이블(연결대상_테이블_기본키)

 

 

3. 제약조건(Constraints)

테이블의 칼럼 값에 대한 제한이나 규칙을 정의한다.

 

제약조건	설명
AUTO_INCREMENT	숫자형 칼럼의 값을 자동 증가
UNIQUE	중복된 값을 허용하지 않음
NOT NULL	NULL 값을 허용하지 않음
DEFAULT	입력 값이 없을 때 기본값 설정
CHECK	특정 조건을 만족해야 함
UNSIGNED	음수를 허용하지 않고 양수만 저장

 

 

ex) CHECK 제약조건 사용

CREATE TABLE Product (
    product_id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price > 0)
);

 

- price 칼럼은 0보다 큰 값만 허용됨

 

 

4. 관계 설정 요약

관계 유형	설명	외래키 위치
일대다(1:N)	한 레코드가 여러 레코드와 매칭	다쪽(많은 데이터가 있는 테이블)
다대다(N:M)	여러 레코드가 여러 레코드와 매칭	중간 테이블 사용
일대일(1:1)	한 레코드가 다른 테이블의 한 레코드와 매칭	선택적 (더 적은 사용 빈도 쪽)

 

 

ex) 온라인 쇼핑몰 데이터베이스

 

1) 일대일(1:1) 관계:

 - 각 고객(Customer)은 하나의 기본 배송 주소(ShippingAddress)를 가진다.

2) 일대다(1:N) 관계:
 - 한 고객(Customer)은 여러 주문(Orders)을 할 수 있다.
 - 한 상품(Product)은 여러 리뷰(Review)를 받을 수 있다.

3) 다대다(N:M) 관계:

 - 여러 주문(Orders)에 여러 상품(Product)이 포함될 수 있으며, 이는 OrderItem 중간 테이블을 통해 구현된다.

 

/* 사용자 테이블 */
CREATE TABLE Customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    registration_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

/* 배송 주소 테이블 (일대일 관계) */
CREATE TABLE ShippingAddress (
    address_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT UNIQUE,
    address_line1 VARCHAR(200) NOT NULL,
    address_line2 VARCHAR(200),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20) NOT NULL,
    country VARCHAR(100) NOT NULL,
    is_default BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id) ON DELETE CASCADE
);

/* 주문 테이블 (일대다 관계) */
CREATE TABLE Orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    total_amount DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id) ON DELETE RESTRICT
);

/* 상품 테이블 */
CREATE TABLE Product (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock_quantity INT NOT NULL DEFAULT 0,
    category VARCHAR(100)
);

/* 주문 상세 테이블 (다대다 관계를 위한 중간 테이블) */
CREATE TABLE OrderItem (
    order_id INT,
    product_id INT,
    quantity INT NOT NULL CHECK (quantity > 0),
    price_at_time DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE RESTRICT
);

/* 상품 리뷰 테이블 (일대다 관계) */
CREATE TABLE Review (
    review_id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NOT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    review_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES Product(product_id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES Customer(customer_id) ON DELETE CASCADE
);