[PostgreSQL] 테이블 생성

▪️ CREATE TABLE

SERIAL은 아이디를 순서대로 증가시켜준다. PRIMARY KEY는 기본키

-- 테이블 생성
-- CREATE TABLE 'table_name' (column...);

CREATE TABLE actors (
	actor_id SERIAL PRIMARY KEY, 
	first_name VARCHAR(150),
	last_name VARCHAR(150) NOT NULL,
	gender CHAR(1),
	date_of_birth DATE,
	add_date DATE,
	update_date Date
);

CREATE TABLE directors (
	director_id SERIAL PRIMARY KEY,
	first_name VARCHAR(150),
	last_name VARCHAR(150) NOT NULL,
	date_of_birth DATE,
	nationality VARCHAR(20),
	add_date DATE,
	update_date DATE
);

👀 테이블 생성 후, 구조 확인하기 SELECT * FROM directors;

---

▪️ CREATE TABLE (참조)

REFERENCES 키워드를 사용하여 movie 테이블의 director_id는 directors 테이블의 director_id를 참조한다.

-- 'column_name' 'dataType' REFERENCES 'table_name(column_name)'
director_id INT REFERENCES direcors (director_id)

CREATE TABLE movies (
	movie_id SERIAL PRIMARY KEY,
	movie_name VARCHAR(100) NOT NULL,
	movie_length INT,
	movie_lang VARCHAR(20),
	age_certificate VARCHAR(10),
	release_date DATE,
	director_id INT REFERENCES direcors (director_id)
);

CREATE TABLE movies_revenue (
	revenue_id SERIAL PRIMARY KEY,
	movie_id INT REFERENCES movies (movie_id),
	revenues_domestic NUMERIC (10,2),
	revenues_international NUMERIC (10,2)
);

▪️ 연결테이블

-- creating a movies_actor junction table

CREATE TABLE movies_actors (
	movie_id INT REFERENCES movies (movie_id),
	actor_id INT REFERENCES actors (actor_id),
	PRIMARY KEY (movie_id,actor_id)
);