기록

노마드코더 SQL 마스터클래스 강의 기록 05 - computed column, Foreign key

als982001 2025. 3. 23. 17:39

1. 배운 것

-- drop column
ALTER TABLE users DROP COLUMN profile_picture;

-- rename column
ALTER TABLE users CHANGE COLUMN about_me bio TINYTEXT;
ALTER TABLE users CHANGE COLUMN about_me about_me TEXT;

-- change the column type
ALTER TABLE users MODIFY COLUMN about_me TINYTEXT;

-- rename database
ALTER TABLE users RENAME TO customers;
ALTER TABLE customers RENAME TO users;

-- drop constraints
ALTER TABLE users DROP CONSTRAINT uq_email;
ALTER TABLE users
	DROP CONSTRAINT username, -- username의 UNIQUE
  DROP CONSTRAINT chk_age,
  DROP CONSTRAINT uq_email;
  
-- adding constraints
ALTER TABLE users
	ADD CONSTRAINT uq_email UNIQUE (email),
  ADD CONSTRAINT uq_username UNIQUE (username),
  ADD CONSTRAINT chk_age CHECK (age < 100);
  
ALTER TABLE users MODIFY COLUMN bed_time TIME NULL; -- NULL을 추가하는 것으로, NULL도 허용 가능
ALTER TABLE users MODIFY COLUMN bed_time TIME NOT NULL; -- NOT NULL을 추가하는 것으로, NULL 허용 안하게 변경

SHOW CREATE TABLE users;
  • column
    • 삭제: ALTER TABLE 테이블_이름 DROP COLUMN 컬럼이름 
    • 이름, 타입 변경: ALTER TABLE 테이블_이름 CHANGE COLUMN 원래_이름 변경할_이름 변경할_타입
    • 타입만 변경: ALTER TABLE 테이블_이름 MODIFY COLUMN 컬럼_이름 변경할_타입
  • column database 이름 변경: ALTER TABLE 테이블_이름 RENAME TO 변경할_이름
  • 제한 조건 
    • 삭제: ALTER TABLE 테이블_이름 DROP CONSTRAINT 제한_조건/컬럼_이름
    • 추가: ALTER TABLE 테이블_이름 ADD CONSTRAINT 제한_조건_이름 제한
  • 마이그레이션 방법 중 컬럼 새로 생성 후 적용
    • 위 코드에서 'add column graduation_date'와 같이 처음부터 NOT NULL을 설정하지 않은 경우
      • 데이터베이스에 추가하는 새로운 컬럼을 NOT NULL로 하려면 기존의 모든 row에 들어갈 기본값을 넣어줘야 하는데, 원래는 모든 row에 새로운 컬럼이 생길 때, 기본적으로 NULL이 들어감. 그래서 실행이 정상적으로 안 됨. 대신 'DEFAULT 기본값' 작성하면 됨

 

-- Computed Column 예시
CREATE TABLE employees (
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  full_name VARCHAR(100) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED
);
  • Computed Column (Generated Column)
    • 데이터베이스에서 다른 컬럼의 값을 기반으로 자동 계산되는 컬럼
    • 위 예시 코드에서 full_name은 first_name과 last_name을 합쳐서 만들어지는 컬럼
    • 장점
      • 중복 계산을 줄일 수 있음
      • 데이터 일관성을 높일 수 있음
      • 쿼리 작성이 쉬워짐
    • 주의할 점
      • 자동으로 계산되는 컬럼이기에 INSERT, UPDATE로 값을 직접 넣을 수 없음.
      • 사용 가능한 연산이나 함수에 제한이 있을 수 있음 (특히 MySQL)
종류 설명 저장 방식
VIRTUAL 요청할 때 계산됨 (저장 X) → 디스크 공간 절약 저장 안 함
STORED 미리 계산해서 저장됨 → 쿼리 성능은 더 빠르지만 디스크 사용 증가 저장함

 

use movies;

CREATE TABLE movies (
	movie_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(300),
    original_title VARCHAR(300),
    original_language CHAR(2),
    overview TEXT,
    release_date SMALLINT,
    revenue BIGINT,
    budget BIGINT,
    homepage TEXT,
    runtime SMALLINT,
    rating TINYINT CHECK (rating BETWEEN -1 AND 10),
    status ENUM (
		'Canceled',
        'In Production',
        'Planned',
        'Post Productuon',
        'Released',
        'Rumored' ),
	country TINYTEXT,
    genres TINYTEXT,
    director TINYTEXT,
    spoken_languages TINYTEXT
);
    
DROP table movies;

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),
  CONSTRAINT breed_fk FOREIGN KEY (breed_id) REFERENCES breeds (breed_id)
);

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
);
  • Database Normalization
    • Normal form에 따라 관계형 데이터베이스를 구조화하는 과정
    • Normalization은 데이터베이스에서 중복되는 data를 제거할 수 있게 해주고, 해당 data가 한 곳에만 저장될 수 있게 함. 만약 data가 중복되지 않았다면 데이터 수정/추가/삭제를 쉽고 빠르게 만들고, 데이터를 일관되게 유지할 수 있음
  • Foreign Key (외래 키)
    • 한 테이블의 컬럼(column)이 다른 테이블의 기본 키(primary key)를 참조하도록 만드는 제약조건
    • 목적
      • 데이터 무결성: 존재하지 않는 값을 막음
      • 테이블 간 관계 표현: 여러 테이블이 서로 어떻게 연결되어 있는지 보여줌
      • 자동 삭제/수정: ON DELETE/ON UPDATE 옵션을 통해 연겨된 데이터도 같이 처리 가능
-- 자주 쓰는 옵션들 예시 코드
FOREIGN KEY (dept_id)
  REFERENCES departments(dept_id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

 

  • 자주 쓰는 옵션들
    • ON DELETE CASCADE: 참조 대상이 삭제되면, 연결된 데이터도 삭제
    • ON UPDATE CASCADE: 참조 대상의 키가 바뀌면, 연결된 값도 자동으로 업데이트