Доброго здоровья читателям моего канала programmer's notes. Поддерживаем мой канал.
Создание таблицы
Столбцы
С командой создания таблицы мы уже знакомились в предыдущей статье.
create table public.sotrudnik(
fio text,
telefon text,
nomer int
);
Создаётся таблица в схеме public со столбцами fio, telefon, nomer. Напомню, что если не создавать других схем, то схему можно и не указывать - все объекты будут помещать туда.
Соответственно после создания таблица можно менять её содержимое, например добавлять данные.
insert into sotrudnik (fio, telefon, nomer)
values ('Иванов И.И.', '123456789', 34);
и
select * from sotrudnik;
Столбцы это, что определяется при создании таблицы (см. выше). Но используя alter table столбец можно добавить.
alter table sotrudnik add column otdel text;
или удалить
alter table sotrudnik drop column otdel;
Наконец, можно изменить тип столбца
alter table sotrudnik alter column nomer type bigint;
При создании столбцов следует учитывать две важных характеристики: разрешение использовать в столбце NULL и значение по умолчанию. Например
create table nomera (
nomer integer not null default -1
);
Т.е. в столбце nomer запрещено использовать NULL, а значение по умолчанию равно -1. Отметим, что по умолчанию использовать NULL допускается.
Первичный ключ
Первичный ключ, с точки зрения сервера баз данных, представляет собой объект, связанный с конкретной таблицей.
create table telefony (
id bigint,
telefon text
);
А теперь добавим первичный ключ
alter table telefony add primary key (id);
Первичный ключ можно определить сразу при создании таблицы
create table telefony (
id bigint primary key,
telefon text
);
При создании первичного ключа автоматически создаётся объект-индекс с именем telefony_pkey. Индекс выполняет две функции: гарантирует уникальность значений в столбце id и ускоряет поиск по id.
Если таблица уже существует, то добавить туда объект — первичный ключ можно также и так
alter table telefony add constraint id1 primary key (id);
В скобках мы указали имя столбца, который будет выступать в качестве первичного ключа. Теоретически можно создавать первичные ключи, состоящие из нескольких столбцов. Нужно просто перечислить их через запятую. Но мы рассматриваем такие первичные ключи в силу их ошибочности с точки зрения построения базы данных.
Уникальный ключ
Есть ещё один объект, связанный с индексированием. Это уникальный ключ. Можно создать такой объект, привязав его одному или нескольким столбцам. При этом автоматически создаётся индекс, который гарантирует уникальность такого ключа.
create table telefony(
id bigint unique,
telefon text
);
Создаётся объект уникальный ключ с именем telefony_id_key (см. раздел Ограничения) и соответственно индекс с таким же именем.
Удалить уникальный ключ можно командой
alter table telefony drop constraint telefony_id_key;
Ещё один способ создания уникального ключа, позволяющий создавать составные уникальные ключи и задавать им своё имя.
create table telefony(
id bigint,
telefon text,
constraint id1 unique (id, telefon)
);
Будет создан уникальный ключ с именем id1, состоящий из двух столбцов id и telefon.
Если таблица уже существует, то добавить туда уникальный ключ можно командой
alter table telefony add constraint id1 unique (id, telefon);
Индексы
Индексы в базе данных играют двоякую роль. Во-первых, они позволяют значительно ускорить поиск по индексированным столбцам. Во-вторых, с помощью индекса можно гарантировать уникальность по индексированному набору столбцов.
Пусть дана таблицам
create table fio (
fm text,
im text,
ot text
);
Создадим уникальный индекс, гарантирующий, что в таблице не будет двух одинаковых строк.
create unique index on fio (fm, im, ot);
Будет создан индекс с именем fm_im_ot_idx, который можно увидеть в разделе Индексы. Отметим, что с практической точки зрения нет различия между созданием уникального ключа и созданием индекса с атрибутом уникальности, хотя во втором случае не появляется объект уникальный ключ (в разделе Ограничения). И в том и другом случае СУБД гарантирует невозможность появления в таблице двух одинаковых строк.
Индексы играют важную роль в оптимизации работы с большими и сверхбольшими базами данных, значительно ускоряя выполнение команд, связанных с извлечением данных из таблиц. В приложении 1 можно найти более подробную информацию о команде создания индексов.
Триггеры обыкновенные
В отличие от событийных триггеров, которые функционируют в пределах всей базы данных, обычные триггеры привязываются к конкретной таблице и командам изменения данных: insert, delete, update. Такие триггеры называют ещё DML-триггеры. DML — Data Manipulation Language, т. е. язык манипулирования данных. Этому подмножеству языка SQL будет посвящён большой раздел нашего пособия.
Создание триггера состоит из двух этапов: 1. Создание функции, которая будет выполняться при наступлении некоторого события. 2. Создание триггера, который для данного вида операций над данной таблицей будет запускать указанную функцию.
create trigger trig1 before insert on student
for each row execute procedure inform();
В результате выполнения данной команды будет создан триггер с именем trig1, который будет привязан к таблице student. Если в таблицу student с помощью команды insert будут добавлять одна или несколько строк, то при добавлении каждой строки будет вызываться хранимая функция inform(). При этом вызываемая функция получит всю информацию о выполняемой операции. Триггерам будут посвящены отдельные статьи.
Правила
Правило можно назвать упрощённым триггером. Правила создаются командой create rule. Правило можно привязать к конкретной таблице или представлению. Оно позволяет заменить указанное действие на альтернативное.
create rule "vst" as
on insert to fio
do instead
delete from fio
В результате данной команды будет создано правило с именем vst. При попытке добавление в таблицу новой строки все строки в таблице будут удалены.
При использовании правил важно не допустить повторного вызова данного правила (или другого правила), что может привести к бесконечному вызову правил (бесконечно рекурсии).
Секции
Объект секция позволяет разбить таблицу на несколько связных частей. Секционирование позволяет оптимизировать работу с таблицей. Конечно, если речь идёт о реально больших таблицах. Можно говорить, что таблица разбивается на более мелкие таблицы, которые, однако логически связаны в единое целое. Такое разбиение позволяет оптимизировать запросы к таблице, повышая производительность запросов. Кроме того, разные секции таблицы могут располагаться на разных носителях. Это весьма актуально, если база данных становится слишком большой.
Секционирование таблицы происходит в два этапа. На первом этапе создаётся секционированная таблица. На втором, создаётся каждая из секций.
Определять секции можно, указав диапазон, определив список для конкретных столбцов, а также используя механизм хеширования.
create table fio (
nomer integer,
fio text
) partition by range (nomer);
Теперь создадим три секции следующими командами
create table fio1 partition of fio for values from (1) to (1000);
create table fio2 partition of fio for values from (1000) to (10000);
create table fio3 partition of fio for values from (10000) to (100000);
Далее при добавлении строк в таблицу fio, они будут попадать в ту или иную секцию, в зависимости от значения поля nomer.
Внешний ключ
Внешний ключ представляет собой ссылку из одной таблицы на первичный ключ другой таблицы. Т.е. в паре двух связанных таблиц должна быть создана в начале главная таблица, потом подчинённая, из которой будет ссылка первичный ключ главной.
create table fio (
id bigint primary key,
fio text
);
И так, fio будет являться главной таблицей. Создадим таблицу с внешним ключом, т. е. подчинённую таблицу.
create table telefony (
id bigint primary key,
telefon text,
id_fio integer,
foreign key (id_fio) references fio(id)
);
В результате получаем две связанные таблицы (см. Рисунок 1)
Пока всё!
Хорошего программирования. Оставляйте свои комментарии, не забывайте про лайки и подписывайтесь на мой канал programmer's notes.
