Моргунов 5 глава

Моргунов 5 глава

Black Andromeda

Виды ограничений

CHECK

может иметь 2 вида:

  • Уровня атрибута
  • Уровня таблицы

Каждое ограничение имеет имя. Мы можем его задать с помощью ключевого слова CONSTRAINT, иначе оно будет автоматически сгенерировано

Примеры

CREATE TABLE progress
( ...
  term numeric( 1 ) CHECK ( term = 1 OR term = 2 ),
  mark numeric( 1 ) CHECK ( mark >= 3 AND mark <= 5 ),
  ...
); 



CREATE TABLE progress
( ...
  mark numeric( 1 ),
  CONSTRAINT valid_mark CHECK ( mark >= 3 AND mark <= 5 ),
  ...
); 

NOT NULL

Означает,что в столбце должны быть только определенные значения

UNIQUE

Такое ограничение означает,что все значения в данном столбце должны быть уникальны и не должны повторяться

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

Примеры

CREATE TABLE students
( record_book numeric( 5 ) UNIQUE,
... ); 



CREATE TABLE students
( record_book numeric( 5 ),
  ...
  CONSTRAINT unique_record_book UNIQUE ( record_book ),
  ...
); 



CREATE TABLE students
( ...
  doc_ser numeric( 4 ),
  doc_num numeric( 6 ),
  ...
  CONSTRAINT unique_passport UNIQUE ( doc_ser, doc_num ),
  ...
); 


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

Первичный ключ

Этот ключ является уникальным идентификатором строк в таблице

Ключ может быть:

  • простым(включать один атрибут)
  • составным (включать несколько атрибутов)

такие атрибуты не могут иметь значений NULL в отличие от атрибутов с ограничение UNIQUE


Примеры

CREATE TABLE students
( record_book numeric( 5 ) PRIMARY KEY,
... ); 




CREATE TABLE students
( record_book numeric( 5 ),
  ...
  PRIMARY KEY ( record_book )
);


Составной первичный ключ

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

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

первичный ключ может быть только один.


Foreign key

являются средством поддержания ссылочной целостности между связанными таблицами

таблица на которую ссылаются называется ссылочной (главная)

таблица, которая ссылается называется ссылающейся (подчиненная)

Примеры:

CREATE TABLE progress
( record_book numeric( 5 ) REFERENCES students ( record_book ),
... 
); 


сокращенная форма, идентичная предыдущей

CREATE TABLE progress
( record_book numeric( 5 ) REFERENCES students,
... ); 



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

Политики работы ссылками при удалении строк в главной таблице

  1. ON DELETE CASCADE  - в ссылающей таблице удалятся все строки, которые ссылались на удаленную строку в главной таблице
  2. ON DELETE RESTRICT - запрет на удаление строк в главной таблице,если на нее кто-то ссылается, операция удаления будет отменена с ошибкой(ошибка произойдет немеделенно) ( эта политика стоит по дефолту,если не указывать явно другие)
  3. ON DELETE NO ACTION - запрет на удаление строк в главной таблице,если на нее кто-то ссылается, операция удаления будет отменена с ошибкой (ошибка произойдет отложенно в рамках транзакции)
  4. SET NULL - в ссылающуюся строку проставить NULL
  5. DEFAULT - в ссылающийся столбец проставить DEFAULT


политика обновления работает аналогично политике удаления

DDL

Примеры

Выставить нужную схему

SET search_path TO <имя схемы>; 

вот так можно описать коммент для столбца зачем он нужен

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


Увидеть описание столбцов через psql

\d+ airports

удалить таблицу aircrafts и все зависящие от нее таблицы

DROP TABLE aircrafts CASCADE;

сделать проверку на существование перед попыткой удаления

DROP TABLE IF EXISTS aircrafts CASCADE;

Добавить колонку:

ALTER TABLE airports
  ADD COLUMN speed integer NOT NULL CHECK( speed >= 300 );


добавить ограничение NOT NULL:

ALTER TABLE aircrafts ALTER COLUMN speed SET NOT NULL;


добавить ограничение CHECK

ALTER TABLE aircrafts ADD CHECK( speed >= 300 );

пример как удалить ограничение

ALTER TABLE aircrafts ALTER COLUMN speed DROP NOT NULL;
ALTER TABLE aircrafts DROP CONSTRAINT aircrafts_speed_check;

Удаление колонки

ALTER TABLE aircrafts DROP COLUMN speed;


Способ привести тип с кастомной логикой

ALTER TABLE seats
  ALTER COLUMN fare_conditions SET DATA TYPE integer
  USING ( CASE WHEN fare_conditions = 'Economy'  THEN 1
               WHEN fare_conditions = 'Business' THEN 2
               ELSE 3
 END ); 


Добавить внешний ключ

ALTER TABLE seats
  ADD FOREIGN KEY ( fare_conditions )
        REFERENCES fare_conditions ( fare_conditions_code );


Переименовать колонку

ALTER TABLE seats
  RENAME COLUMN fare_conditions TO fare_conditions_code;

Переименовать ограничение

ALTER TABLE seats
    RENAME CONSTRAINT seats_fare_conditions_fkey
    TO seats_fare_conditions_code_fkey;


Представления

Чтобы облегчить формирование запросов можно обернуть их во вьюху

Пример

CREATE VIEW seats_by_fare_cond AS
  SELECT aircraft_code,
         fare_conditions,
         count( * )
    FROM seats
    GROUP BY aircraft_code, fare_conditions
    ORDER BY aircraft_code, fare_conditions;


CREATE OR REPLACE VIEW позволяет модицифировать вьюху,но он не заработает если переименуем в запросе какой-то столбец и придется тогда вьюху придется явно удалять

параметры колонок во вьюхе позволяют зафиксировать имена столбцов при запросе данных из вьюхи.

CREATE MATERIALIZED VIEW sales_summary AS
SELECT
 date_trunc('month', order_date) AS month,
SUM(total_amount) AS total_sales
FROM orders
GROUP BY
 date_trunc('month', order_date);

мат. вьюха , это вьюха которая кроме формирования запроса также сохраняет данные физически, если не указали явно WITH NO DATA

вот так можно обновить данные

REFRESH MATERIALIZED VIEW sales_summary

то есть мы получим старые данные, если запросим без рефреша

это позволяет нам экономить время для долгих запросов,если нам не нужны постоянно новые данные

Например это удобно для кеширования

или формирования еженедельных сводных отчетов

плюс использования вьюх:

  • можно разграничить полномочия и сделать разные вьюхи для разных пользаков без изменения схемы таблицы
  • упрощение запросов
  • мат вьюхи помогут снизить время запроса
  • вьюха зависит только от интерфейса таблицы, что означает,если интерфейс таблицы не меняется,то сама вьюха не нуждается от модификации.Пример если тип столбца изменился с INT на VARCHAR, то вьюху менять не придется

Схема базы данных

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

 При создании базы данных в ней автоматически создается схема с именем public. 

имена объектов базы данных должны быть уникальны в пределах одной схемы

пример

посмотреть список схем в psql

\dn 

сделать схему текущей

SET search_path = bookings;


показать путь поиска имен в схемах

SHOW search_path;

один факт если схема называется именем пользователя, то имена в ней будут всегда искать первыми

Получить текущую схему

SELECT current_schema;
Report content on this page

Report Page

Please submit your DMCA takedown request to dmca@telegram.org