PostgreSQLでリレーション関係のあるテーブルを作りたい

create table person (
	id int,
	first_name varchar(50),
	last_name varchar(50),
	gender varchar(7),
	birth_day date
);

testdb=# \d person
Table “public.person”
Column | Type | Collation | Nullable | Default
————+———————–+———–+———-+———
id | integer | | |
first_name | character varying(50) | | |
last_name | character varying(50) | | |
gender | character varying(7) | | |
birth_day | date | | |

### テーブルに制約をつける

create table person (
	id BIGSERIAL not null primary key,
	first_name varchar(50) not null,
	last_name varchar(50) not null,
	gender varchar(7) not null,
	birth_day date not null,
	email varchar(150)
);

bigserial 整数を自動決定
primary key キーとして扱う
not null 空白を許可しない

### テーブルにデータを挿入

insert into person (
	first_name,
	last_name,
	gender,
	birth_day
) values ('Anne', 'Smith', 'Femail', DATE '1988-01-09');

insert into person (
	first_name,
	last_name,
	gender,
	birth_day,
	email
) values ('Jake', 'Jone', 'Male', DATE '1990-01-10', 'jake@gmail.com');

testdb=# select * from person;
id | first_name | last_name | gender | birth_day | email
—-+————+———–+——–+————+—————-
1 | Anne | Smith | Femail | 1988-01-09 |
2 | Jake | Jone | Male | 1990-01-10 | jake@gmail.com
(2 rows)

データの自動生成
https://mockaroo.com/

create table person (
	id BIGSERIAL not null primary key,
	first_name varchar(50) not null,
	last_name varchar(50) not null,
	gender varchar(7) not null,
	email varchar(150),
	date_of_birth date not null,
	country varchar(50) not null
);

$ \i /home/vagrant/dev/app/person.sql
$ select * from person;

create table person (
	id BIGSERIAL not null primary key,
	first_name varchar(50) not null,
	last_name varchar(50) not null,
	gender varchar(7) not null,
	email varchar(150),
	date_of_birth date not null,
	country varchar(50) not null,
	car_id BIGINT REFERENCES car (id),
	unique(car_id)
);

create table car (
	id BIGSERIAL not null primary key,
	make varchar(50) not null,
	model varchar(50) not null,
	price varchar(50) not null
);

BIGINT REFERENCESで関連づけるのか。なるほど、勉強になるね。