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

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


examination:bd:question28

Создание ограничений

По соображениям безопасности не каждому пользователю прикладной системы может быть разрешено получать информацию из какой-либо таблицы, а тем более изменять в ней данные. Для определения прав пользователей относительно объектов базы данных (таблицы, представления, индексы) в SQL определена пара команд GRANT и REVOKE. Синтаксис операции передачи прав на таблицу:

  GRANT <тип_права_на_таблицу>
    ON  <имя_таблицы> [<список_столбцов>]
    TO  <имя_пользователя>

Права пользователя на уровне таблицы определяются следующими ключевыми словами (как мы увидим чуть позже эти ключевые слова совпадают с командами выборки и изменения данных):

  • SELECT - получение информации из таблицы
  • UPDATE - изменение информации в таблице
  • INSERT - добавление записей в таблицу
  • DELETE - удаление записей из таблицы
  • INDEX - индексирование таблицы
  • ALTER - изменение схемы определения таблицы
  • ALL - все права

В поле <тип_права_на_таблицу> может быть указано либо ключевое слово ALL или любая комбинация других ключевых слов. Например, предоставим все права на таблицу publishers пользователю andy:

 GRANT ALL ON publishers TO andy;

Пользователю peter предоставим права на извлечение и дбавление записей на эту же таблицу:

 GRANT SELECT INSERT ON publishers TO peter;

В том случае, когда одинаковые права надо предоставить сразу всем пользователям, вместо выполнения команды GRANT для каждого из них, можно вместо имени пользователя указать ключевое слово PUBLIC:

 GRANT SELECT ON publishers TO PUBLIC;

Отмена прав осуществляется командой REVOKE:

 REVOKE <тип_права_на_таблицу>
    ON  <имя_таблицы> [<список_столбцов>]
    FROM  <имя_пользователя>

Все ключевые слова данной команды эквивалентны оператору GRANT.

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

  GRANT <тип_права_на_базу_данных>
    ON  <имя_базы данных>
    TO  <имя_пользователя>

К сожалению, способы задания прав на базу данных различны для разных СУБД, и точную их формулировку нужно уточнять в документации. В качестве примера приведем список прав на базу данных, поддерживаемых СУБД Informix:

  • CONNECT - права на доступ к данным и их модификацию, если это разрешено на уровне таблицы;
  • RESOURCE - права на управление ресурсами. Все перечисленное выше плюс права на создание новых объектов (таблиц, индексов и т.д.) и удаление и изменение тех объектов, которыми данный пользователь владеет;
  • DBA - права на администрирование. Все права на управление ресурсами плюс права на удаление базы данных, удаление любых объектов, назначение и отмена прав других пользователей.

Отмена прав на базу данных осуществляется командой:

 REVOKE <тип_права_на_базу_данных> FROM  <имя_пользователя>
 
 

—-

Ограничения 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/question28.txt · Последние изменения: 2014/01/15 12:11 (внешнее изменение)