obsidian/hardcore web development/11. SQL/Создание таблиц.md

Создаем таблицу авторов:

create table author (
	author_id bigint generated always as identity primary key,
	name varchar(150) not null check (length(name) >= 3),
	description text check (length(description) >= 30)
);

Создаем таблицу для хранения книжных категорий:

create table book_category (
	category_id int generated always as identity primary key,
	name varchar(150) not null check (length(name) >= 2)
);

Создаем таблицу для книг:

create table book (
	book_id bigint generated always as identity primary key,
	name varchar(255) not null check (length(name) >= 2),
	author_id bigint not null references author(author_id),
	description text check (length(description) >= 30),
	cover varchar(255),
	category_id int not null references book_category(category_id)
);

Создание временной таблицы:

=> psql create temp table something (id serial, name text) 

-- Вставляем данные
=> insert into something (name) values ('hello')
=> insert into something (name) values ('draturi')

-- Получаем данные
=> select * from something;

~={cyan}Создание таблиц в базе bird из задания=~

create table family (
	family_id bigint generated always as identity primary key,
	family_name varchar(150) not null,
	description text
);

create table genus (
	genus_id bigint generated always as identity primary key,
	genus_name varchar(150) not null,
	description text,
	family_id bigint not null references family(family_id)
);

create table species (
	species_id bigint generated always as identity primary key,
	species_name varchar(150) not null,
	genus_id bigint not null references genus(genus_id),
	description text,
	average_length smallint not null,
	average_weight smallint not null,
	primary_color varchar(50) not null
);