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

```sql
drop table if exists rroom_user, email cascade;

create table rroom_user(
    user_id bigint generated always as identity primary key,
    birth_date DATE
);

create table email (
    email varchar(320),
    user_id bigint references rroom_user(user_id),
    is_verified boolean not null default false,
    primary key (email, user_id)
);
```

Обратите внимание, как здесь создан первичный ключ — он составной, из двух колонок `email` и `user_id`. Так можно делать. В данном случае это оправдано, потому что мы хотим, чтобы была уникальная связка `email` и `user_id`, чтобы эта связка не дублировалась. В такой схеме у нас один мейл может быть у разных пользователей, но при этом только у одного он может быть верифицирован:

```sql
create unique index unique_verified_email_per_user
on email (email)
where is_verified = true;
```

Об индексах мы поговори позже, но сейчас можно проверить, что оно работает. Создадим пару пользователей:

```sql
insert into rroom_user(birth_date) values ('1988-07-29') returning user_id; -- 1
insert into rroom_user(birth_date) values ('1996-01-01') returning user_id; -- 2
```

Обратите внимание на конструкцию `returning`. С её помощью можно одним запросом как вставить данные, так и вернуть какие-то данные этой вставленной записи, в данном случае поле `user_id` генерируется последовательностью, поэтому очень удобно его значение вернуть в одном запросе с `insert`.

Теперь создадим два одинаковых мейла у каждого:

```sql
insert into email (email, user_id) values ('sterx@rl6.ru', 1);
insert into email (email, user_id) values ('sterx@rl6.ru', 2);

select * from email;
```

Видим, что у обоих записей `is_verified=false`. Это окей. У нас может быть мейл, принадлежащий сначала одному пользователю, а потом другому — какой-то рабочий мейл, например. То есть в нашей схеме нет жесткой связки один email один аккаунт. Это особенно актуально для телефонов, когда один и тот же номер телефона может менять разных владельцев и в разные периоды времени принадлежать разным пользователям, что совершенно нормально. Для мейлов возможно это в меньшей степени актуально, но пусть будет.

Так вот, попробуем верифицировать email сразу у обоих пользователей:

```sql
update email set is_verified=true where email='sterx@rl6.ru'; -- ошибка
select * from email;

update email set is_verified=true where email='sterx@rl6.ru' and user_id=1;
select * from email;

update email set is_verified=true where email='sterx@rl6.ru' and user_id=2; -- ошибка
select * from email;
```

Видим, что может быть только один пользователь с верифицированным мейлом! Это благодаря нашему индексу. Повторюсь, об индексах подробнее поговорим дальше.

Кстати, выше я упоминал, но подчеркну ещё раз подход с колонкой `is_deleted`. Зачастую мы не удаляем данные их БД, а просто помечаем их удалёнными, проставляя в колонке `is_deleted` значение `TRUE`. И тогда при каждой выборке активных записей надо будет смотреть, что поле `is_deleted` имеет значение `FALSE`. Тогда у нас все исторические данные будут доступны в таблице и мы сможем при необходимости их использовать в будущем.

```sql
create temp table some_entity(
    entity_id bigint generated always as identity primary key,
    value text not null,
    is_deleted boolean not null default false
);

insert into some_entity (value) values ('some value');
insert into some_entity (value) values ('some another value');

select * from some_entity where is_deleted=false;

update some_entity set is_deleted=true where entity_id=2;
select * from some_entity where is_deleted=false;
select * from some_entity;
```

Другим подходом может быть перенос удалённых значений в отдельную таблицу, например, так:

```sql
drop table if exists some_entity;
drop table if exists some_entity_deleted;

create temp table some_entity(
    entity_id bigint generated always as identity primary key,
    value text not null
);

create table some_entity_deleted (like some_entity including defaults including constraints including indexes);

insert into some_entity (value) values ('some value');
insert into some_entity (value) values ('some another value');

select * from some_entity;

begin;
insert into some_entity_deleted select * from some_entity where entity_id=2;
delete from some_entity where entity_id=2;
commit;
```

Здесь мы создаём таблицу `some_entity` такую же как и раньше, но без поля `is_deleted`, так как в этом случае вся таблица будет хранить в себе только актуальные записи. Затем мы создаём таблицу `some_entity_deleted`, которая копирует структуру, индексы, ограничения и значения колонок по умолчанию такие же как в `some_entity`. И затем мы вставляем в `some_entity` две строки, одну из которых я затем удалю.

Операция удаления должна быть атомарной — то есть мы в 1 операции должны вставить запись в таблицу удаленных записей и удалить запись из основной таблицы. Иначе если операция будет неатомарной, то вставка записи может пройти, а удаление не пройти, например. Поэтому мы оборачиваем это в транзакцию — о транзакциях мы поговорим позже.

Ну и обращаю ваше внимание, что `insert into таблица select` работает. То есть можно вставлять записи в таблицу из какой-то выборки.

Аналогично можно сделать и без явного определения транзакции с помощью CTE, Common Table Expression, который мы изучим дальше:

```sql
insert into some_entity (value) values ('some another value');

with deleted_rows as (
    delete from some_entity
    where entity_id=3
    returning *
)
insert into some_entity_deleted select * from deleted_rows;

select * from some_entity;
select * from some_entity_deleted;
```

Преимуществом такого подхода без колонки `is_deleted` может быть то, что не надо помнить, что в каждый запрос надо вставлять это условие where `is_deleted=false`. Таблицы для хранения исторических данных можно при желании вынести в отдельную схему, чтобы они не были видны в основной рабочей схеме.

Отдельно здесь хочу отметить 2 момента, которые здесь промелькнули в этом уроке. Во-первых, как создать таблицу такую же, как уже имеющаяся таблица:

```sql
create table some_entity_deleted (like some_entity including defaults including constraints including indexes);
```

Можно также создать таблицу на основе результатов запроса вместе с результатами этого запроса:

```sql
create table some_tmp_table as select * from book;
drop table some_tmp_table;
```

Здесь структура создаваемой новой таблицы, то есть типы данных и их размеры, определяется автоматически на основе структуры и типов данных столбцов, которые возвращаются `SELECT`-запросом.

И можно вставить в существующую таблицу результаты какого-то запроса:

```sql
insert into some_entity_deleted select * from some_entity where entity_id=2;
```

Не то чтобы вы будете этим часто пользоваться, но это довольно удобный механизм в процессе изучения баз данных — дублировать таблицу, вставить туда данные из какого-то запроса и так далее.