SQL에서 ER 다이어그램의 키: 기본 키, 외래 키, 후보 키의 식별 방법

SQL 데이터베이스 설계의 기본인 ER 다이어그램(엔터티 관계 다이어그램)은 데이터베이스 내의 데이터와 그 관계를 시각적으로 표현하는 중요한 도구입니다. 이 기사에서는 ER 다이어그램에서 기본 키, 외래 키, 후보 키를 식별하는 방법에 대해 자세히 설명합니다. 이러한 키의 역할과 식별 방법을 이해함으로써 보다 효율적이고 정확한 데이터베이스 설계가 가능합니다.

ER 다이어그램이란

ER 다이어그램(엔터티 관계 다이어그램)은 데이터베이스 내의 엔터티(데이터의 대상)와 그들 간의 관계를 시각적으로 표현한 도표입니다. ER 다이어그램을 사용하면 데이터베이스 설계자는 시스템 전체의 데이터 구조를 명확하게 이해하고 데이터의 중복 및 불일치를 방지할 수 있습니다. ER 다이어그램은 주로 엔터티, 속성, 관계라는 3가지 기본 요소로 구성됩니다.

엔터티

엔터티란 데이터베이스 내에서 표현되는 실체로, 구체적인 물체나 사건을 가리킵니다. 예를 들어, 고객, 제품, 주문 등이 엔터티에 해당합니다.

속성

속성은 엔터티가 가진 특성이나 정보를 나타냅니다. 예를 들어, 고객 엔터티의 속성에는 이름, 주소, 전화번호 등이 포함됩니다.

관계

관계는 엔터티 간의 연결을 나타냅니다. 예를 들어, 고객과 주문 간에는 “주문하다”라는 관계가 존재합니다.

기본 키(Primary Key)의 정의와 식별 방법

기본 키(Primary Key)는 테이블 내의 각 레코드를 고유하게 식별하기 위한 키입니다. 기본 키는 반드시 유일해야 하며, NULL 값을 가질 수 없습니다. 기본 키를 적절하게 설정하면 데이터의 고유성과 일관성을 유지할 수 있습니다.

기본 키의 정의

기본 키는 테이블 내의 레코드를 고유하게 특정하기 위해 사용되는 하나 이상의 열(필드)로 구성됩니다. 기본 키는 고유성을 보장하므로 각 레코드는 다른 값을 가져야 합니다.

기본 키의 특징

고유성: 각 레코드를 고유하게 식별할 수 있습니다.
비NULL: NULL 값을 가질 수 없습니다.
단일 또는 복합: 하나의 열로 구성되거나 여러 열을 조합하여 구성될 수 있습니다(복합 기본 키).

기본 키의 식별 방법

기본 키를 식별하기 위해 다음 기준을 사용합니다.

고유성 확인: 후보 키 중에서 고유한 열을 선택합니다.
NULL 값 확인: 후보 키 중에서 NULL 값을 가지지 않는 열을 선택합니다.

SQL에서의 기본 키 설정

다음은 SQL에서 기본 키를 설정하는 예시입니다.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ContactName VARCHAR(100),
    Country VARCHAR(50)
);

이 예시에서는 CustomerID 열이 기본 키로 설정되어 있으며, 각 고객 레코드를 고유하게 식별합니다.

외래 키(Foreign Key)의 정의와 식별 방법

외래 키(Foreign Key)는 테이블 간의 관계를 정의하는 키입니다. 외래 키는 특정 테이블의 열이 다른 테이블의 기본 키를 참조하는 방식으로 데이터의 일관성을 유지합니다.

외래 키의 정의

외래 키는 테이블의 열이 다른 테이블의 기본 키나 후보 키를 참조하는 키입니다. 이를 통해 데이터베이스 내의 테이블 간 관계가 확립됩니다.

외래 키의 특징

관계 구축: 외래 키는 특정 테이블의 레코드가 다른 테이블의 레코드와 어떻게 연관되는지를 나타냅니다.
참조 무결성: 외래 키 제약을 사용하여 참조하는 데이터가 존재하는지 확인합니다. 참조된 데이터가 삭제된 경우 관련 데이터의 업데이트나 삭제를 제어합니다.

외래 키의 식별 방법

외래 키를 식별하기 위해 다음 기준을 사용합니다.

참조 대상 확인: 참조할 테이블의 기본 키나 후보 키를 특정합니다.
관계 확인: 관련된 테이블 간의 관계를 확인하고 외래 키를 설정합니다.

SQL에서의 외래 키 설정

다음은 SQL에서 외래 키를 설정하는 예시입니다.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderNumber INT,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

이 예시에서는 Orders 테이블의 CustomerID 열이 Customers 테이블의 CustomerID 열을 참조하는 외래 키로 설정되어 있습니다. 이를 통해 각 주문이 어떤 고객과 관련이 있는지를 나타냅니다.

후보 키(Candidate Key)의 정의와 식별 방법

후보 키(Candidate Key)는 테이블 내의 레코드를 고유하게 식별할 수 있는 열 또는 열의 조합입니다. 후보 키는 기본 키로 선택될 수 있는 모든 키를 지칭합니다.

후보 키의 정의

후보 키는 테이블 내의 각 레코드를 고유하게 특정할 수 있는 하나 이상의 열로 구성됩니다. 테이블에는 여러 후보 키가 존재할 수 있으며, 그 중 하나가 기본 키로 선택됩니다.

후보 키의 특징

고유성: 각 레코드를 고유하게 식별할 수 있습니다.
비NULL: 후보 키의 열은 NULL 값을 가질 수 없습니다.
여러 개 존재 가능: 하나의 테이블에는 여러 후보 키가 존재할 수 있습니다.

후보 키의 식별 방법

후보 키를 식별하기 위해 다음 기준을 사용합니다.

고유성 확인: 각 열 또는 열의 조합이 고유한 값을 가지는지 확인합니다.
비NULL 확인: 각 후보 키의 열이 NULL 값을 가지지 않는지 확인합니다.
최소성 확인: 최소한의 열 개수로 고유성을 보장하는 열의 조합을 특정합니다.

SQL에서의 후보 키 설정

다음은 SQL에서 후보 키를 나타내는 예시입니다.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    NationalIDNumber VARCHAR(15) NOT NULL,
    EmployeeNumber VARCHAR(10) NOT NULL,
    EmployeeName VARCHAR(100),
    CONSTRAINT UQ_NationalIDNumber UNIQUE (NationalIDNumber),
    CONSTRAINT UQ_EmployeeNumber UNIQUE (EmployeeNumber)
);

이 예시에서는 Employees 테이블에서 EmployeeID가 기본 키로 설정되어 있지만, NationalIDNumber 및 EmployeeNumber에도 유일 제약 조건(UNIQUE 제약 조건)이 설정되어 있어 이들이 후보 키임을 나타냅니다.

기본 키와 외래 키의 관계

기본 키와 외래 키의 관계는 데이터베이스 설계에서 관계를 표현하는 중요한 요소입니다. 기본 키와 외래 키를 적절하게 설정하면 데이터의 일관성과 참조 무결성을 보장할 수 있습니다.

기본 키와 외래 키의 기본적인 관계

기본 키는 테이블 내의 각 레코드를 고유하게 식별하는 키입니다. 반면, 외래 키는 다른 테이블의 기본 키를 참조하기 위한 키입니다. 외래 키를 사용하여 테이블 간의 관계를 확립할 수 있습니다.

관계의 종류

기본 키와 외래 키를 사용하여 다음과 같은 관계를 표현할 수 있습니다.

일대다(1:N) 관계: 하나의 기본 키가 여러 외래 키와 연결되는 경우.
다대다(N:M) 관계: 여러 기본 키가 여러 외래 키와 연결되는 경우. 이 경우 중간 테이블을 사용하여 관계를 표현합니다.

관계의 예시

다음은 Customers 테이블과 Orders 테이블 간에 일대다 관계를 설정하는 예시입니다.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    ContactName VARCHAR(100),
    Country VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderNumber INT,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

이 예시에서는 Customers 테이블의 CustomerID가 기본 키로 정의되어 있으며, Orders 테이블의 CustomerID가 외래 키로 설정되어 있습니다. 이를 통해 각 주문이 어떤 고객과 관련이 있는지를 나타낼 수 있습니다.

참조 무결성의 보장

외래 키 제약을 사용하여 참조 무결성을 보장합니다. 예를 들어, 참조된 기본 키가 삭제된 경우, 관련 외래 키의 처리(삭제 또는 업데이트)를 제어할 수 있습니다.

실제 데이터베이스 설계에서 키 선택

데이터베이스 설계에서 적절한 키를 선택하는 것은 데이터의 일관성과 효율적인 쿼리 성능을 보장하는 데 매우 중요합니다. 여기에서는 실제 데이터베이스 설계 시 키를 선택하는 기준과 베스트 프랙티스에 대해 설명합니다.

키 선택 기준

키를 선택할 때는 다음 기준을 고려합니다.

고유성: 각 레코드를 고유하게 식별할 수 있는지 확인합니다.
안정성: 시간이 지나도 키 값이 변경되지 않는 것이 바람직합니다. 빈번한 변경이 발생하는 키는 피합니다.
단순성: 키는 가능한 한 단순하고 짧은 것이 좋습니다. 복잡한 키는 이해하기 어렵고 쿼리 성능에 영향을 미칠 수 있습니다.
자연 키 vs. 대리 키: 실제 세계의 데이터를 반영하는 자연 키(예: 직원 번호나 이메일 주소)를 사용할지, 시스템이 생성한 대리 키(예: 자동 증가하는 ID)를 사용할지 결정합니다.

기본 키 선택

기본 키를 선택할 때는 다음 사항에 주의합니다.

고유성 및 비NULL 제약: 기본 키는 반드시 고유하며, NULL 값을 가질 수 없는 열을 선택합니다.
안정성: 기본 키는 변경되지 않는 열을 선택합니다. 예를 들어, 직원 번호나 사회 보장 번호 등은 적절한 기본 키가 될 수 있습니다.

외래 키 선택

외래 키를 선택할 때는 다음 사항에 주의합니다.

관계 명확화: 외래 키는 참조하는 테이블의 기본 키 또는 후보 키와 명확한 관계를 가져야 합니다.
참조 무결성 유지: 외래 키를 설정함으로써 참조 무결성을 보장하고 데이터의 일관성을 유지합니다.

후보 키 선택

후보 키를 선택할 때는 다음 사항에 주의합니다.

고유성 확인: 후보 키는 테이블 내의 다른 열과 고유성을 유지해야 합니다.
중복성 제거: 불필요하게 많은 후보 키를 설정하지 않도록 하며, 단순한 설계를 유지합니다.

베스트 프랙티스

대리 키 사용: 많은 실무 환경에서는 간단하고 고유성이 보장되는 대리 키를 기본 키로 사용하는 것이 일반적입니다. 예를 들어, 자동 증가하는 정수 ID를 사용합니다.
자연 키 보완: 자연 키도 데이터의 일관성을 유지하기 위해 사용하지만, 기본 키로는 대리 키를 사용하고 자연 키에는 유일 제약 조건(UNIQUE 제약 조건)을 설정하는 것이 권장됩니다.

ER 다이어그램에서 키의 표기 방법

ER 다이어그램(엔터티 관계 다이어그램)에서는 키의 종류를 명확하게 표현하기 위해 특정 기호나 스타일을 사용합니다. 여기에서는 ER 다이어그램에서 기본 키, 외래 키, 후보 키의 표기 방법에 대해 설명합니다.

기본 키 표기 방법

기본 키는 ER 다이어그램에서 엔터티의 속성 중 고유성을 가지는 속성으로 특별히 표기됩니다. 일반적인 표기 방법은 다음과 같습니다.

밑줄: 기본 키를 나타내는 속성에 밑줄을 긋습니다.
예: CustomerID
PK 라벨: 속성명 옆에 “(PK)”를 추가합니다.
예: CustomerID (PK)

외래 키 표기 방법

외래 키는 ER 다이어그램에서 다른 엔터티의 기본 키를 참조하는 속성으로 표기됩니다. 일반적인 표기 방법은 다음과 같습니다.

FK 라벨: 속성명 옆에 “(FK)”를 추가합니다.
예: CustomerID (FK)
점선: 외래 키 속성을 점선으로 둘러쌉니다.
예: -- CustomerID --

후보 키 표기 방법

후보 키는 기본 키로 선택될 가능성이 있는 키로 표기됩니다. 일반적인 표기 방법은 다음과 같습니다.

CK 라벨: 속성명 옆에 “(CK)”를 추가합니다.
예: NationalIDNumber (CK)
물결선: 후보 키 속성을 물결선으로 둘러쌉니다.
예: ~ NationalIDNumber ~

ER 다이어그램의 구체적인 예

다음은 ER 다이어그램에서 키의 표기 방법을 나타내는 구체적인 예시입니다.

|----------------------|
|      Customers       |
|----------------------|
| CustomerID (PK)      |
| CustomerName         |
| ContactName          |
| NationalIDNumber (CK)|
|----------------------|

|----------------------|
|        Orders        |
|----------------------|
| OrderID (PK)         |
| OrderNumber          |
| CustomerID (FK)      |
|----------------------|

이 예시에서는 Customers 테이블의 CustomerID가 기본 키로 밑줄이 그어져 있으며, Orders 테이블의 CustomerID가 외래 키로 표기되어 있습니다. 또한, Customers 테이블의 NationalIDNumber는 후보 키로 물결선으로 표기되어 있습니다.

연습 문제와 그 해답 예시

이해를 높이기 위해 ER 다이어그램에서 기본 키, 외래 키, 후보 키를 식별하는 방법에 대한 연습 문제를 제공합니다. 이 문제들을 풀어봄으로써 실제 데이터베이스 설계에서 키의 역할과 중요성을 더 깊이 이해할 수 있습니다.

연습 문제 1

다음 엔터티를 포함하는 ER 다이어그램을 생각해 보세요.

Employees 엔터티

EmployeeID
EmployeeName
DepartmentID
NationalIDNumber

Departments 엔터티

DepartmentID
DepartmentName

다음 질문에 답해 보세요.

Employees 엔터티의 기본 키는 무엇인가요?
Departments 엔터티의 기본 키는 무엇인가요?
Employees 엔터티의 외래 키는 무엇인가요?
Employees 엔터티의 후보 키는 무엇인가요?

해답 예시 1

Employees 엔터티의 기본 키는 EmployeeID입니다.
Departments 엔터티의 기본 키는 DepartmentID입니다.
Employees 엔터티의 외래 키는 DepartmentID입니다.
Employees 엔터티의 후보 키는 NationalIDNumber입니다.

연습 문제 2

다음 SQL 스크립트를 보고 각 테이블의 기본 키, 외래 키, 후보 키를 식별해 보세요.

CREATE TABLE Projects (
    ProjectID INT PRIMARY KEY,
    ProjectName VARCHAR(100),
    StartDate DATE,
    EndDate DATE
);

CREATE TABLE EmployeeProjects (
    EmployeeID INT,
    ProjectID INT,
    AssignmentDate DATE,
    PRIMARY KEY (EmployeeID, ProjectID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
    FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
);

다음 질문에 답해 보세요.

Projects 테이블의 기본 키는 무엇인가요?
EmployeeProjects 테이블의 기본 키는 무엇인가요?
EmployeeProjects 테이블의 외래 키는 무엇인가요?

해답 예시 2

Projects 테이블의 기본 키는 ProjectID입니다.
EmployeeProjects 테이블의 기본 키는 복합 기본 키로, EmployeeID와 ProjectID의 조합입니다.
EmployeeProjects 테이블의 외래 키는 EmployeeID와 ProjectID입니다.

요약

이 기사에서는 SQL에서 ER 다이어그램의 키, 즉 기본 키, 외래 키, 후보 키의 식별 방법에 대해 자세히 설명했습니다. ER 다이어그램은 데이터베이스 설계에 중요한 도구이며, 각 키를 적절하게 설정하는 것은 데이터의 고유성과 일관성을 유지하는 데 필수적입니다. 기본 키는 각 레코드를 고유하게 식별하고, 외래 키는 테이블 간의 관계를 설정하며, 후보 키는 기본 키로 선택 가능한 키를 나타냅니다. 이러한 키의 역할과 식별 방법을 이해하고 실제 데이터베이스 설계에 적용함으로써 효율적이고 신뢰성 높은 데이터베이스를 구축할 수 있습니다.