SQL. Синтастические тонкости и table constraints

Спасибо неутомимому @darkgenius за деталь:

Колонка в БД называется поле. Применять слово "колонка" в приличном обществе - значит прослыть деревенщиной! :D

Table constraits - это дополнительные определения поля. То есть, полям можно задать определение или даже поведение, которые помогают избегать ошибок и систематизируют информацию.

Примеры table constrainnts:

PRIMARY KEY. Указывает, что значения в поле - уникальны и могут использоваться, например, как ID строки.

AUTOINCREMENT. Только для числовых значений. Задает автоматический инкремент (аналог ++ в JS) значения при создании новой строки.

UNIQE. Указывает, что каждое значение в поле должно быть уникальным: вставить строку с дублем не удастся. От PRIMARY KEY отличается тем, что такое поле нельзя использовать в качестве хранилища ключей.

NOT NULL. В поле не должно быть NULL.

CHECK (expression). Проверяет строку на соответствие выражению - математическому, соответствую массиву и т.д.

FOREIGN KEY REFERENCES field(name). Последовательно проверяет, чтобы PRIMARY KEY соответстволо указанное поле внешней таблицы. Значения в поле, указанном как FOREIGN KEY, не обязательно должны быть уникальными. Пример - соотношение таблиц авторов и книг по уникальному ID автора и повторяющимся ключам книг.

#SQL

SQL. ALTER TABLE

Оператор ALTER TABLE меняет существующие таблицы, а конкретно - добавлять или удалять новые поля, переименовывать таблицы и так далее.

Синтаксис похож на CREATE TABLE:

ALTER TABLE films
ADD new_column или...
DROP some_column или...
RENAME TO old_films

У разных БД - разные методы. Подробности в документации.

#SQL

SQL. Проектирование БД. Общие моменты.

- Cхема БД - это ее представление в виде взаимосвязи таблиц, в виде картинки или текста. По сути - проект. По схеме разработчик понимает структуру БД независимо от ее размера.

- Реляционные БД - значит, БД, построенные на отношениях (relation). Отношения - это просто таблицы.

- Домены. Это множества возможных значений атрибута. Например, домен атрибута пола состоит из множества {M, Ж}. Не следует путать домены с типами данных.

- Кортежи. То же, что и строки.

- Первичный ключ. Уникальный идентификатор кортежа. Может быть составным, потенциальным, несоставным. В третьей форме первичный ключ не может быть составным.

- Внешний ключ. Ключ к главной внешней таблице. Используется в дочерних таблицах.

- Аномалии. Возникают при неправильном проектировании после операций над таблицами.
Бывают аномалии вставки, модификации, удаления.
Пример: из главной таблицы удалена строка, на которую продолжает ссылаться внешний ключ в дочерней.
Решение: при проектировании закладывается либо каскадное удаление всех зависимых строк по внешнему ключу. Либо, если допустимо присутствие строки без связи с главной таблицей, задается дефолтное значение NULL.

- Теорема Хита:

R (A, B, C) = r1(A, B) join r2(A, C)

Что она означает:
Если в таблице с атрибутами A, B, C имеется зависимость A -> B, то декомпозиция этой таблицы обратима.

То есть, правильная нормализация проводится так, чтобы не потерять данные. Если после операции JOIN выполняется равенство по теореме, значит, декомпозиция проведена правильно.

#SQL

SQL. Пара слов о SQLite

Это легковесный движок реляционной БД. Он хранит БД в одном файле - что сводит миграцию базы до передачи этого файла. Его недостатки:
- Усеченная функциональность;
- Сложнее организовать разграничение доступа к таблицам;
- Только один пользователь может обновлять БД в определенный момент времени, а значит, высоконагруженные сервисы - в пролёте;
- Движок не чекает типы данных. Их можно указать в схеме - и все равно запихнуть неверные.

#SQL

SQL. Switch...case в SQL

В реляционных БД есть и эта знакомая всем логическая конструкция. Выражена она так:

SELECT column,
CASE
WHEN genre = 'romance'
OR genre = 'comedy'
THEN 'Chill'
ELSE 'Intense'
END AS 'Mood'
FROM movies;

Можно заметить, что CASE понимает операторы типа OR. Это полезно для построения логики.

#SQL