[SQL] FOREIGN KEY

정규화

DB 설계 시 데이터 중복을 없애고 일관성을 유지하기 위한 과정

외래키(FOREIGN KEY)

테이블에 있는 컬럼이 다른 테이블의 컬럼을 참조

• 다른 테이블에서 참조하는 컬럼이 있어야만 데이터가 들어갈 수 있도록 제약을 걸어둠
• 데이터 일관성을 유지하도록 만듦

정규화 되지 않은 테이블

-- 한 주인이 2마리 이상 강아지를 키운다면 주인에 대한 정보에 중복이 생김
-- 3개 entity에 대한 데이터를 가지고 있음 (dog, breed, owner)

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  
);

정규화 한 테이블 ERD

https://dbdiagram.io/d/6702bd14fb079c7ebd802fdf

dogs (강아지 메인)

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 DEFAULT 2,
    
  	-- 품종 아이디(breed_id)를 외래키로 지정하여 breeds 테이블의 breed_id 컬럼을 참조
    -- breeds 테이블의 breed_id가 사라지면 디폴트로 2 지정
  	CONSTRAINT breed_fk FOREIGN KEY (breed_id) REFERENCES breeds (breed_id) ON DELETE SET DEFAULT
);

-- 주인 아이디(owner_id)를 외래키로 지정하여 owners 테이블의 owenr_id 컬럼을 참조
ALTER TABLE dogs
ADD CONSTRAINT owner_fk FOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE SET DEFAULT;

-- owner_fk라는 이름의 외래키를 삭제
-- postgreSQL에서는 DROP CONSTRINT
ALTER TABLE dogs
DROP FOREIGN KEY owner_fk;

-- breeds 테이블의 breed_id가 사라지면 NULL로 채움
ALTER TABLE dogs
ADD CONSTRAINT owner_fk FOREIGN KEY (owner_id) REFERENCES owners (owner_id) ON DELETE SET NULL;

❗️ 주인 테이블에서 레코드가 삭제 될 시 참조하고 있는 테이블에서 어떤 액션을 취할지 지정

• ON DELETE CASCADE: 주인 테이블의 데이터가 삭제되면 참조하고 있는 테이블의 레코드가 삭제 (그 반대는 아님)
• ON DELETE SET NULL: 주인 테이블의 데이터가 삭제되면 참조하고 있는 테이블의 레코드는 남아 있고 해당 레코드는 NULL (해당 컬럼에 NOT NULL 제약조건 있으면 안됨)
• ON DELETE SET DEFAULT: 주인 테이블의 데이터가 삭제되면 defalut로 정한 값으로 설정

owners (주인 정보)

-- owners 테이블 관점에서 dogs 테이블과 1:N 관계
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    
);

breeds (품종 정보)

-- breeds 테이블 관점에서 dogs 테이블과 1:N 관계
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
);

pet_passports (강아지 여권 정보)

-- pet_passports과 dogs 테이블은 1:1 관계
CREATE TABLE pet_passports (
	pet_passport_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  	blood_type VARCHAR(10),
  	allergies TEXT,
  	last_checkup_date DATE,
  	dog_id BIGINT UNSIGNED UNIQUE, -- 강아지와 강아지 여권을 1:1 관계로 설정 
    
  	-- pet_passports 테이블의 dog_id를 외래키로 지정하여 dogs의 dog_id를 참조
    -- dogs 테이블의 dog_id가 사라지면 pet_passports 테이블의 레코드도 같이 삭제
  	FOREIGN KEY (dog_id) REFERENCES dogs (dog_id) ON DELETE CASCADE
);

tricks (특기 정보)

-- 순수 특기에 대한 정보
-- 이를 dogs와 바로 N:N 관계로 표현할 수 없음
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'
);

dog_tricks (강아지와 특기를 이어주는 브릿지 정보)

-- 브릿지 테이블이 있어야만 N:N 관계를 표현할 수 있음
-- dog_id, trick_id를 외래키로 지정하여 dogs, tricks 테이블에서 각각 dog_id, trick_id를 참조
-- dog_id, trick_id 두 개 컬럼을 동시에 pk로 지정 (복합키)
CREATE TABLE dog_tricks (
	dog_id BIGINT UNSIGNED,
  	trick_id BIGINT UNSIGNED,
  	proficiency ENUM('beginner', 'intermediate', 'expert') NOT NULL DEFAULT 'beginner',
  	date_learned TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  	
  	PRIMARY KEY (dog_id, trick_id),
  	FOREIGN KEY (dog_id) REFERENCES dogs (dog_id) ON DELETE CASCADE,
  	FOREIGN KEY (trick_id) REFERENCES tricks (trick_id) ON DELETE CASCADE
);