Множественные ограничения и ограничения на уровне таблицы

Обеспечение корректности данных в базе данных на самом базовом уровне («целостность данных») имеет основополагающее значение. Если вы пропишете безопасность только в приложении Node.js, ошибки могут привести к противоречивым данным в БД. Ограничения на уровне DDL — это последняя, ​​непреодолимая линия защиты.

Основные ограничения

Помимо PRIMARY KEY (который делает столбец уникальным, а не NULL) и классических NOT NULL и UNIQUE, существуют две фундаментальные категории логической согласованности данных:

1. ВНЕШНИЙ КЛЮЧ (Ссылочная целостность)

Ограничение REFERENCES гарантирует, что идентификатор связи действительно существует в «родительской» таблице, на которую он указывает. Например, заказ должен быть связан с существующим клиентом.

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id)
);

Что делает внешний ключ?

• Это не позволяет вам вставить customer_id, которого нет в customers.
• Не позволяет DELETE (удалить) клиента из customers, если на него есть указывающие заказы (защищает от "бесхозных" записей).

Мы также можем решить, что должно произойти, когда «родитель» будет удален:

customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE

При использовании ON DELETE CASCADE, если мы удалим клиента, Postgres автоматически удалит все связанные orders! (Используйте с большой осторожностью; обычно предпочтительнее использовать настройку по умолчанию, которая блокирует операцию.)

2. ПРОВЕРЬТЕ ограничения

Ограничение CHECK проверяет строку, проверяя логическое выражение, прежде чем приступить к сохранению.

CREATE TABLE contratti (
  id SERIAL PRIMARY KEY,
  stipendio NUMERIC(10,2) CHECK (stipendio > 0),
  data_inizio DATE,
  data_fine DATE,
  CHECK (data_fine > data_inizio)
);

Первое CHECK — это ограничение столбца (оно относится только к stipendio). Второй CHECK внизу — это ограничение таблицы (оно может пересекать значения из нескольких столбцов и должно быть объявлено внизу).

Если INSERT или UPDATE попытается сохранить конец перед началом, Postgres отклонит его с ошибкой!

Именование ограничений

Часто рекомендуется давать ограничениям явное имя (используя ключевое слово CONSTRAINT). Таким образом, когда Postgres блокирует запись, ошибка сообщит вам, например, «ограничение 'stipendio_positivo' нарушено», что гораздо проще для отладки!

CREATE TABLE prodotti (
  id SERIAL PRIMARY KEY,
  nome VARCHAR(50),
  CONSTRAINT nome_univoco UNIQUE(nome)
);

Попробуйте это

Ограничения с пользовательской логикой