Инструменты пользователя

Инструменты сайта


examination:bd:question19

Ограничение целостности данных

Целостность (от англ. integrity – нетронутость, неприкосновенность, сохранность, целостность) – понимается как правильность данных в любой момент времени.

Ограничения целостности можно определить как специальные средства в базах данных, главное назначение которых - не дать попасть в базу недопустимым данным (например, предупредить ошибки пользователей при вводе данных).

Все ограничения целостности можно разделить на три большие категории:

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

· вторая категория - сущностная целостность. Главная задача здесь - сделать так, чтобы данные об одной сущности не попали в базу данных два раза. Обеспечивается ограничением уникальности и первичным ключом;

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

Еще две большие категории, на которые можно поделить средства обеспечения целостности - средства декларативного и процедурного характера. Средства декларативного характера создаются как составные части объектов при их определении в базе данных (например, условие на значение при определении таблицы в базе данных). Средства процедурного характера (триггеры и хранимые процедуры) реализуются как отдельные программные модули. В общем случае декларативные ограничения менее функциональны, но более экономны с точки зрения ресурсов и наоборот.

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

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

(на примере языка SQL)

Ограничения Check

Ограничение целостности check является наиболее часто используемым видом ограничения. Оно позволяет задать для определённой колонки, выражение, которое будет осуществлять проверку, помещаемого в эту колонку значения. Если значение удовлетворяет, заданному ограничению, то выражение должно возвращать Логическое значение (истина). Например, если требуется, чтобы в колонке были только положительные цены товаров, вы можете использовать:

CREATE TABLE products (

  product_no integer,
  name text,
  price numeric CHECK (price > 0)

);

Ограничения не-Null

Ограничение не-null просто говорит, что колонка не должна содержать значение null. Пример синтаксиса:

CREATE TABLE products (

  product_no integer NOT NULL,
  name text NOT NULL,
  price numeric

); Ограничение не-null всегда записывается как ограничение на колонку. Ограничение не-null функционально эквивалентно созданию ограничения CHECK (column_name IS NOT NULL).

Разумеется, на колонку может накладываться более одного ограничения. Вы просто пишете другое ограничение, в след за первым:

CREATE TABLE products (

  product_no integer NOT NULL,
  name text NOT NULL,
  price numeric NOT NULL CHECK (price > 0)

); Порядок записи не имеет значения. Также нет необходимости определять в каком порядке выполняется проверка ограничений.

Ограничение NOT NULL имеет свою противоположность: ограничение NULL. Это не означает, что колонка должна содержать значение null, которое является бесполезным. Это просто означает, что при выборе значения по умолчанию для данной колонки может использоваться значение null.

Ограничения уникальности

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

CREATE TABLE products (

  product_no integer UNIQUE,
  name text,
  price numeric

);

Если ограничение уникальности ссылается на группу колонок, эти колонки перечисляются через запятую:

CREATE TABLE example (

  a integer,
  b integer,
  c integer,
  UNIQUE (a, c)

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

Добавление ограничения уникальности автоматически создаст уникальный btree индекс на колонку или группу колонок, использующих данное ограничение.

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

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

CREATE TABLE products (

  product_no integer UNIQUE NOT NULL,              
  name text,                                       
  price numeric                                    

);

CREATE TABLE products (

  product_no integer PRIMARY KEY,
  name text,name text,
  price numeric

);

Первичные ключи также могут ограничивать более чем одну колонку; синтаксис сходен с ограничением уникальности.

Добавление первичного ключа автоматически создаст уникальный btree индекс на колонку или группу колонок, используемых как первичный ключ.

Таблица может иметь не более одного первичного ключа. (В ней может быть любое количество ограничений уникальности и ограничений не-null, которые функционально являются тем же, но только одно из этих ограничений может быть индентифицировано как первичный ключ.)

Внешние ключи

Ограничение внешнего ключа говорит, что значения в колонке (или группе колонок) должно совпадать с значениями, которые существуют в некоторых строках другой таблицы. Мы говорим, что внешний ключ обслуживает ссылочную целостность между двумя относящимися друг к другу таблицами.

Например, пусть у нас есть таблица товаров product, которую мы уже использовали несколько раз:

CREATE TABLE products (

  product_no integer PRIMARY KEY,
  name text,
  price numeric

); Также пусть у нас есть таблица orders, хранящая порядок этих товаров. Мы хотим иметь уверенность, что таблица с порядком товаров содержит только те товары, которые уже есть. Для этого мы определяем ограничение внешнего ключа в таблице orders, которое ссылается на таблицу products:

CREATE TABLE orders (

  order_id integer PRIMARY KEY,
  product_no integer REFERENCES products (product_no),
  quantity integer

); Теперь в таблице orders невозможно создать строку, в которой значение product_no не соответствует одному из значений в таблице products.

Мы говорим, что в данной ситуации, таблица orders является ссылающейся (referencing) таблицей, а таблица products является ссылочной (referenced) таблицей. Похожим образом, колонки являются ссылающейся и ссылочной.

Таблица может содержать более одного ограничения внешнего ключа. Это используется при реализации между таблицами отношений многие-ко-многим. Пусть у вас есть таблицы products и orders, но теперь вы хотите позволить таблице orders содержать несколько товаров (что не позволяет делать ранее использованная структура таблицы). Для этого можно использовать следующую структуру этих таблиц:

CREATE TABLE products (

  product_no integer PRIMARY KEY,
  name text,
  price numeric

);

CREATE TABLE orders (

  order_id integer PRIMARY KEY,
  shipping_address text,
  ...

);

CREATE TABLE order_items (

  product_no integer REFERENCES products,
  order_id integer REFERENCES orders,
  quantity integer,
  PRIMARY KEY (product_no, order_id)

);

Заметим, что в последней таблице первичный ключ пересекается со внешним ключом.

Мы знаем, что внешние ключи запрещают создание строк в orders, которые не относятся к какому-либо существующему товару. Но если нужно удалить товар из таблицы products после того как в orders создана запись, которая ссылается на него? SQL позволяет вам управлять возможными действиями в этой ситуации.

Напрашивается несколько вариантов:

  • Запретить удаление ссылочного продукта
  • Всё-равно удалить строку
  • Что-то другое?

Чтобы проиллюстрировать это, реализуем следующую политику отношения многим-ко-многим в предыдущем примере: когда кто-то хочет удалить товар, на который ссылается строка в таблице orders (через order_items), мы запрещаем это. Если кто-то удаляет строку в orders, элменты в order_items также удаляются.

CREATE TABLE products (

  product_no integer PRIMARY KEY,
  name text,
  price numeric

);

CREATE TABLE orders (

  order_id integer PRIMARY KEY,
  shipping_address text,
  ...

);

CREATE TABLE order_items (

  product_no integer REFERENCES products ON DELETE RESTRICT,
  order_id integer REFERENCES orders ON DELETE CASCADE,
  quantity integer,
  PRIMARY KEY (product_no, order_id)

); Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY.

  • CASCADE - выполняется удаление строки из родительской таблицы, сопровождающееся автоматическим удалением всех ссылающихся на нее строк дочерней таблицы;
  • SET NULL - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL ;
  • SET DEFAULT - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;
  • NO ACTION - операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена.

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

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

examination/bd/question19.txt · Последние изменения: 2014/01/15 12:11 (внешнее изменение)