[SQL] Foreign Keys: Data Normalization / ON DELETE / One-To-Many / Many-To-Many

Database Normalization(데이터베이스 정규화)의 목적  

1. 데이터 중복 제거 (Redundancy Reduction)

: 같은 데이터가 여러 테이블에 중복 저장되지 않도록 방지

2. 데이터 무결성 유지 (Integrity Preservation)

: 한 곳에서 데이터를 수정하면 모든 관련 데이터가 자동으로 반영되도록 함

3. 이상(Anomaly) 방지

  - 삽입 이상 (Insertion Anomaly): 불필요한 데이터를 추가해야만 원하는 데이터를 삽입할 수 있는 문제

  - 삭제 이상 (Deletion Anomaly): 불필요한 데이터가 함께 삭제되는 문제

  - 갱신 이상 (Update Anomaly): 데이터를 변경할 때 모든 중복된 데이터를 일일이 수정해야 하는 문제

4. 데이터 저장 공간 최적화

: 중복을 줄이면서 효율적인 데이터 저장이 가능해짐

 

---

Foreign Keys 

CREATE TABLE dogs (
    dog_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    breed_name VARCHAR(50) NOT NULL,
    breed_size_category ENUM('small', 'medium', 'big') DEFAULT 'small',
    breed_typical_lifespan TINYINT,
    date_of_birth DATE,
    weight DECIMAL(5,2),
    owner_name VARCHAR(50) NOT NULL,
    owner_email VARCHAR(100) UNIQUE,
    owner_phone VARCHAR(20),
    owner_address TINYTEXT
);

예를 들어 위 테이블의 경우, 1명의 owner가 여러 마리의 개를 데리고 있는 경우 owner 관련 칼럼의 데이터가 중복된다 

=> owner는 별도의 entity로 관리해야! 

=> breed 역시 별도의 entity로 관리해야! 

 

CREATE TABLE
dogs (
    dog_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    weight DECIMAL(5, 2),
    owner_id BIGINT UNSIGNED,  -- owers 테이블과 연결 
    breed_id BIGINT UNSIGNED   -- breeds 테이블과 연결 
);
-- 여기서 dog_id: primary key / owner_id, breed_id: foreign key 



CREATE TABLE
owners (
    owner_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    address TINYTEXT
);


CREATE TABLE
breeds (
    breed_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    size_category ENUM('small', 'medium', 'big') DEFAULT 'small',
    typical_lifespan TINYINT
);

문제점

- 'dogs' 테이블에 데이터를 추가할 때, 'owners' 테이블에 존재하지 않는 owner_id 또는 'breeds' 테이블에 존재하지 않는  breed_id를 입력 가능

- 존재하지 않는 id를 참조하면 안된다(즉, 존재하지 않는 id를 foreign key로 사용하면 안된다) 

 

CREATE TABLE
dogs (
    dog_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    weight DECIMAL(5, 2),
    owner_id BIGINT UNSIGNED,
    breed_id BIGINT UNSIGNED,
    FOREIGN KEY (owner_id) REFERENCES owners (owner_id),
    -- 'owners' 테이블에 존재하는 owner_id를 참조하도록 제약 
    CONSTRAINT breed_fk FOREIGN KEY (breed_id) REFERENCES breeds (breed_id)
    -- constraint에 이름을 붙여줄 수도 있다 
);

해결

- FOREIGN KEY 추가할 때, 해당 테이블의 데이터를 미리 살펴봐서 존재하는 경우에만 참조하도록 제약 

- 이때, foreign key로 사용한 owner_id의 데이터를 'owners' 테이블에서 삭제 불가

---

ON DELETE

 

Foreign Key에 사용할 수 있는 참조 옵션(Referential Actions)

CASCADE  부모 테이블에서 삭제(DELETE), 수정(UPDATE) 발생 시 자식 테이블도 동일하게 반영
SET NULL 부모 테이블에서 삭제(DELETE), 수정(UPDATE) 발생 시 자식 테이블의 해당 값을 NULL로 설정
SET DEFAULT 부모 테이블에서 삭제(DELETE), 수정(UPDATE) 발생 시 자식 테이블의 값을 기본값(DEFAULT)으로 설정
RESTRICT 부모 테이블의 데이터가 참조될 경우 삭제(DELETE), 수정(UPDATE)을 막음
NO ACTION RESTRICT와 유사하지만, 트랜잭션 완료 후 무결성 검사를 수행

CREATE TABLE dogs (
    dog_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    weight DECIMAL(5,2),
    owner_id BIGINT UNSIGNED,
    breed_id BIGINT UNSIGNED,
    FOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE CASCADE,
    -- CASCADE: 참조된 owner_id가 삭제되면 'dogs' 테이블에서 해당 owner_id를 참조하는 모든 데이터가 삭제
    CONSTRAINT breed_fk FOREIGN KEY (breed_id) REFERENCES breeds (breed_id)
);

 

---

One-To-Many / One-To-One 

어떤 관점에서 relationship을 정의할 것인가 

하나의 breed_id는 여러 dogs 와 연결될 수 있다: breeds와 dogs는 One-To-Many

여러 dogs는 하나의 breed와 연결된다: dogs와 breeds는 Many-To-One

 

 

Many-To-Many

여러 dogs가 하나의 trick을 배울 수 있다 & 하나의 dog이 여러 trick을 배울 수 있다: Many-To-Many

직접적으로 Many-To-Many 관계를 만들 수 없고 연결테이블(join table)이 필요 

CREATE TABLE dogs (
  dog_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  weight DECIMAL(5,2),
  date_of_birth DATE,
  owner_id BIGINT UNSIGNED,
  breed_id BIGINT UNSIGNED,
  FOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE CASCADE,
  FOREIGN KEY (breed_id) REFERENCES breeds (breed_id) ON DELETE CASCADE
);


CREATE TABLE breeds (
  breed_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  size_category ENUM('small', 'medium', 'big') DEFAULT 'small',
  typical_lifespan TINYINT
);


CREATE TABLE owners (
  owner_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE,
  phone VARCHAR(20),
  address TINYTEXT
);


CREATE TABLE dogs_passports (
  dog_passports_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  blood_type VARCHAR(10),
  allergies TEXT,
  last_checkup_date DATE,
  dog_id BIGINT UNSIGNED UNIQUE,  -- UNIQUE 제약을 추가: 1-1 관계가 됨 
  -- 1번 강아지와 연결된 펫 여권을 만들면 Database에 1번 값이 저장 
  FOREIGN KEY (dog_id) REFERENCES dogs (dog_id) ON DELETE CASCADE
);


CREATE TABLE tricks(
  trick_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) UNIQUE NOT NULL,
  difficulty ENUM('easy', 'medium', 'hard') NOT NULL DEFAULT 'easy'
  );
             
             
CREATE TABLE dog_tricks(
  dog_id BIGINT UNSIGNED,
  trick_id BIGINT UNSIGNED,
  profency ENUM('beginner', 'intermediate', 'expert') NOT NULL DEFAULT 'beginner',
  date_learned TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (dog_id, trick_id), -- 둘을 연결하여 primary key를 만든다 
  FOREIGN KEY (dog_id) REFERENCES dogs (dog_id) ON DELETE CASCADE,
  FOREIGN KEY (trick_id) REFERENCES tricks (trick_id) ON DELETE CASCADE
  );

 

 

 

 

https://dbdiagram.io