opredelenie_dannyh_v

Определение данных в Postgresql

Значение по умолчанию:

...
mark numeric( 1 ) DEFAULT 5,

При вставке значения по умолчанию можно использовать значения переменных окружения

....
who_adds_row text DEFAULT current_user,
...

CHECK

check - ограничение, бывает двух видов: уровня атрибута и уровня таблицы.
Каждое ограничение имеет имя, которое задается с помощью ключевого слова CONSTRAINT, иначе имя сформируется автоматически
Пример ограничения:
В следующем примере задается ограничение для атрибутов term (значение может быть = 1 или 2) и mark (значение должно быть больше 2 и не больше 5).
Для ограничения mark указано имя valid_mark:

term numeric( 1 ) CHECK ( term = 1 OR term = 2 ),
mark numeric( 1 ), CONSTRAINT valid_mark CHECK ( mark >= 3 AND mark <= 5 )

Следующее ограничение говорит что значение в атрибута должно быть уникальным в пределах таблицы

record_book numeric( 5 ) UNIQUE

Второй вариант синтаксиса, где для ограничения указывается имя:

record_book numeric( 5 ),
....
CONSTRAINT unique_record_book UNIQUE ( record_book )
....

Определение сразу двух уникальных атрибутов:

doc_ser numeric( 4 ),
doc_num numeric( 6 ),
...
CONSTRAINT unique_passport UNIQUE ( doc_ser, doc_num ),

При определении уникальности атрибутов, создается индекс B-дерева, для поддержки этого ограничения

Primary Key

Первичный ключ может быть только в одном экземпляре для одной таблицы
Первичный ключ может быть простым или составным(из нескольких атрибутов)
Если первичный ключ состоит из одного атрибута:

record_book numeric( 5 ) PRIMARY KEY,

или так:

PRIMARY KEY ( record_book )

Если первичный ключ состоит из нескольких атрибутов:

PRIMARY KEY ( имя-столбца1, имя-столбца2, ...)

При добавлении первичного ключа автоматически создается индекс на основе B-дерева для поддержки этого ограничения.

Foreign Key

Внешний ключ необходим для поддержания так называемой ссылочной целостности(referential integrity)
Создание внешнего ключа в ссылающейся таблице на ссылочную таблицу students:

record_book numeric( 5 ) REFERENCES students ( record_book ),....

Теперь не получится вставить строку в ссылочной таблице , если значения ее внешнего ключа еще нет в соответствующем атрибуте ссылочной таблицы.
Если внешний ключ ссылается на соответствующий первичный ключ ссылочной таблицы, можно использовать более упрощенный синтаксис:

record_book numeric( 5 ) REFERENCES students,...

Создание внешнего ключа в форме ограничения:

FOREIGN KEY ( record_book )
REFERENCES students ( record_book )

Каскадное удаление

Каскадное удаление - удаление строк в других таблицах, связанных внешним ключем с атрибутом текущей таблицы.
Для реализации каскадного удаления добавить ON DELETE CASCADE при определении ограничения внешнего ключа:

CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON DELETE CASCADE
);

При удалении студента, удалятся связанные с ним строки в таблице progress
Просто так удалить таблицу aircrafts не получится, так как на нее ссылаются внешние ключи из других таблиц. А при каскадном (использование CASCADE) удалении aircrafts, удаляется сама таблица, а также внешние ключи из других таблиц, которые ссылаются на эту таблицу.

DROP TABLE aircrafts CASCADE;

Можно добавить к команде удаления команду IF EXISTS

DROP TABLE aircrafts IF EXISTS CASCADE;

Запрет на удаление

Запрет на удаление из ссылочной таблицы, если в ссылающейся таблице есть хотя бы одна строка с внешним ключем на атрибут из ссылочной таблицы, можно реализовать путем добавления ON DELETE RESTRICT или ON DELETE ACTION при определении внешнего ключа. ON DELETE ACTION используется если при определении внешнего ключа не используется какое либо действие, отклоняет операцию удаления или обновления для родительской таблицы.
Указание RESTRICT (или NO ACTION) - это то же самое, что опустить предложение ON DELETE или ON UPDATE
В случае NO ACTION можно отложить проверку ограничению на другое время, RESTRICT выполняет проверку немедленно

CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON DELETE RESTRICT
);

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

CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON DELETE SET NULL
);

Еще один способ, который позволит удалить строку с внешним ключем:
Присваивание атрибутам внешнего ключа таблицы ограничения default:

CREATE TABLE progress
( record_book numeric( 5 ) DEFAULT 12345,
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON DELETE SET DEFAULT
);

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

Каскадное обновление

При использовании каскадного обновления значения ссылочных атрибутов копируются в ссылающиеся строки ссылающейся таблицы:

CREATE TABLE progress
( record_book numeric( 5 ),
...
FOREIGN KEY ( record_book )
REFERENCES students ( record_book )
ON UPDATE CASCADE
);

Создание и удаление таблиц

Схемы базы данных - это своего рода пространства имен в которых создаются таблицы и др. объекты
Выбор текущей схемы

SET search_path TO bookings ;

Сначала создаются ссылочные таблицы, а потом ссылающееся.
Таблица может ссылаться на другую таблицу, т.е. быть ссылающейся и быть ссылочной, т.е. на нее ссылается другая таблица, таким образом образуется иерархия.
Комментарии в SQL обозначаются двумя символами дефиса
Команда COMMENT позволяет создавать комментарии (описания) к различным объектам базы данных. Эти комментарии будут сохраняться в базе данных.
Например, для создания описания столбца city таблицы airports нужно сделать так:

COMMENT ON COLUMN airports.city IS 'Город';

Где airports - таблица, city - атрибут, 'Город' - описание атрибута
Чтобы увидеть описание таблицы, добавить плюс к команде \d:

demo=# \d+ airports
                                                View "bookings.airports"
    Column    |     Type     | Collation | Nullable | Default | Storage  |                 Description                  
--------------+--------------+-----------+----------+---------+----------+----------------------------------------------
 airport_code | character(3) |           |          |         | extended | Airport code
 airport_name | text         |           |          |         | extended | Airport name
 city         | text         |           |          |         | extended | City
 coordinates  | point        |           |          |         | plain    | Airport coordinates (longitude and latitude)
 timezone     | text         |           |          |         | extended | Airport time zone
View definition:
 SELECT ml.airport_code,
    ml.airport_name ->> lang() AS airport_name,
    ml.city ->> lang() AS city,
    ml.coordinates,
    ml.timezone
   FROM airports_data ml;

Пример создания таблицы рейсы для аэропорта

create table flights
(
flight_id serial NOT NULL, --идентификатор рейса
flight_no char(6) NOT NULL, -- номер рейса 
scheduled_departure timestamptz NOT NULL, -- время вылета по расписанию
scheduled_arrival timestamptz NOT NULL, -- время прилета по расписанию
departure_airport char(3) NOT NULL, -- аэропорт отправления
arrival_airport char(3) NOT NULL, -- аэропорт прибытия
status varchar( 20 ) NOT NULL, -- статус рейса
aircraft_code char( 30 ) NOT NULL, -- код самолета, IATA
actual_departure timestamptz, -- фактическое время вылета
actual_arrival timestamptz, -- фактическое время прилета
CHECK ( scheduled_arrival > scheduled_departure ),
CHECK ( status IN ( 'On Time', 'Delayed', 'Departed', 'Arrived', 'Scheduled', 'Cancelled' )),
CHECK ( actual_arrival IS NULL OR
 ( actual_departure IS NOT NULL AND
   actual_arrival IS NOT NULL AND 
   actual_arrival > actual_departure
 )
),
PRIMARY KEY ( flight_id ),
UNIQUE ( flight_no, scheduled_departure ),
FOREIGN KEY ( aircraft_code )
  REFERENCES aircrafts ( aircraft_code ),
FOREIGN KEY ( arrival_airport )
  REFERENCES airports ( airport_code ),
FOREIGN KEY ( departure_airport )
  REFERENCES airports ( airport_code )
);

При создании таблицы, создаются:
Три внешних ключа FOREIGN KEY
Первичный ( суррогатный ключ - уникальный ) где тип serial значение целого типа, присваивается из последовательности
Уникальный ключ UNIQUE из двух атрибутов
Для фиксирования даты и времени используется тип timestamtz с указанием часового пояса
Первое ограничение CHECK ( scheduled_arrival > scheduled_departure ) говорит, что время прилета должна быть больше даты вылета
Второе ограничение CHECK ( status IN ( 'On Time', 'Delayed', 'Departed', 'Arrived', 'Scheduled', 'Cancelled' )) говорит, что status может быть только одним из указанных Третье ограничение:

CHECK ( actual_arrival IS NULL OR
( actual_departure IS NOT NULL AND
actual_arrival IS NOT NULL AND
actual_arrival > actual_departure
)
Где в первой части указано, что атрибут actual_arrival может иметь значение null или не null , но при при условии, что actual_arrival больше actual_departure и actual_departure также непустое
Полезно посмотреть, что получилось:

\d flights

После вывода видно, что атрибут flight_id имеет тип integer, а не serial как было указано при создании таблицы.
О том что для flight_id будут последовательно создаваться значения, говорит фраза в колонке default:
nextval('flights_flight_id_seq'::regclass)
При выполнении команды \d эту последовательность будет видно в списке отношений Можно посмотреть описание для типа последовательность:

Did not find any relation named "flights_flight_id_seq".
demo=# \d flights_flight_id_seq 
                  Sequence "bookings.flights_flight_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache 
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: bookings.flights.flight_id

Рассмотрим следующий пример вывода:

demo=# CREATE TABLE ticket_flights
( ticket_no
char( 13 )
NOT NULL, -- Номер билета
flight_id
integer
NOT NULL, -- Идентификатор рейса
fare_conditions varchar( 10 )
NOT NULL, -- Класс обслуживания
amount
numeric( 10, 2 ) NOT NULL, -- Стоимость перелета
CHECK ( amount >= 0 ),
CHECK ( fare_conditions IN ( 'Economy', 'Comfort', 'Business' ) ),
PRIMARY KEY ( ticket_no, flight_id ),
FOREIGN KEY ( flight_id )
REFERENCES flights ( flight_id ),
FOREIGN KEY ( ticket_no )
REFERENCES tickets ( ticket_no )
);
ERROR:  relation "ticket_flights" already exists

demo=# \d ticket_flights
                     Table "bookings.ticket_flights"
     Column      |         Type          | Collation | Nullable | Default 
-----------------+-----------------------+-----------+----------+---------
 ticket_no       | character(13)         |           | not null | 
 flight_id       | integer               |           | not null | 
 fare_conditions | character varying(10) |           | not null | 
 amount          | numeric(10,2)         |           | not null | 
Indexes:
    "ticket_flights_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
Check constraints:
    "ticket_flights_amount_check" CHECK (amount >= 0::numeric)
    "ticket_flights_fare_conditions_check" CHECK (fare_conditions::text = ANY (ARRAY['Economy'::character varying::text, 'Comfort'::character varying::text, 'Business'::character varying::text]))
Foreign-key constraints:
    "ticket_flights_flight_id_fkey" FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
    "ticket_flights_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
Referenced by:
    TABLE "boarding_passes" CONSTRAINT "boarding_passes_ticket_no_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES ticket_flights(ticket_no, flight_id)

В созданной таблице хранится информация о перелете.
Класс обслуживания fare_conditions подлежит проверке на одно из допустимых значений:

.....CHECK ( fare_conditions IN ( 'Economy', 'Comfort', 'Business' ) ),......

Стоимость перелет имеет тип numeric (для значений денежных сумм) и должно быть больше или = 0 :

...CHECK ( amount >= 0 ),... Атрибуты: ticket_no, flight_id являются первичным ключем и одновременно внешним ключем

Создание автоинкремента для первичного ключа

# CREATE TABLE users
(
id_user bigserial PRIMARY KEY,
login_user VARCHAR(20) NOT NULL,
hash_user VARCHAR(100) NOT NULL,
date_create timestamp without time zone DEFAULT NOW()
);
CREATE TABLE