ID: 7599

Название работы: Обмеження. Postgre SQL.

Категория: Лекция

Предметная область: Информатика, кибернетика и программирование

Описание: Обмеження Postgre SQL має декілька варіантів обмеження даних (constraint), які впливають на операції вставки і оновлення. Розглянемо один із них, який полягає в установці обмеженьдля таблиць і полів.Обмеженням є особливий атри...

Язык: Украинкский

Дата добавления: 2013-01-26

Размер файла: 60 KB

Работу скачали: 2 чел.

Обмеження

Postgre SQL має декілька варіантів обмеження даних (constraint), які впливають на  операції вставки і оновлення. Розглянемо один із них, який полягає в установці обмежень для таблиць і полів. Обмеженням є особливий атрибут таблиці, який встановлює критерії допустимості для вмісту її полів. Дотримання цих правил допомагає запобігти заповненню бази помилковими або невідповідними даними. Обмеження задаються в секції CONSTRAINT при створенні таблиці командою CREATE TABLE. Обмеження полів завжди стосуються лише одного поля, тоді як обмеження таблиць можуть встановлюватися як для одного, так і для декількох полів. У команді CREATE TABLE обмеження полів задаються відразу ж після визначення поля, тоді як обмеження таблиці встановлюється в спеціальному блоці, виділеному комами від усіх визначень полів.  

        Опис обмеження поля виглядає так:

         [ CONSTRAINT обмеження]

        { NOT NULL| UNIQUE | PRIMARY KEY | DEFAULT значення CHECK (умова )|

        REFERENCES таблиця [ ( поле) ]

        [ MATCH FULL | MATCH PARTIAL ]

        [ ON DELETE операція]

        [ ON UPDATE операція]

        [ DEFERRABLE | NOT DEFERRABLE ]

        [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]}

        Визначення обмеження слідує в команді CREATE TABLE відразу ж за типом обмежуваного поля і передує комі, яка відокремлює його від наступного поля. Обмеження можуть встановлюватися для будь-якої кількості полів, а ключове слово CONSTRAINT і ідентифікатор обмеження не обов’язкові.

        Існує шість типів обмежень полів, які задаються за допомогою спеціальних ключових слів. Деякі з них побічно встановлюються при створені обмежень іншого типу. Типи обмежень полів перераховані нижче, вони такі:

•  NOT NULL. Поле не може містити псевдозначення NULL. Обмеження NOT NULL еквівалентно обмеженню CHECK (поле NOT NULL);
•  UNIQUE. Поле не може містити значення, які повторюються. Слід враховувати, що обмеження UNIQUE допускає багатократне входження псевдозначень NULL, оскільки формально NULL не збігається ні з яким іншим значенням;
•  PRIMARY KEY. Автоматично встановлює обмеження UNIQUE і NOT NULL, а для заданого поля створюється індекс. У таблиці може встановлюватися тільки одне обмеження первинного ключа;
•  DEFAULT значення. Пропущені значення поля замінюються заданою величиною. Тип значення за замовчуванням повинно відповідати типу поля;
•  CHECK умова. Команда INSERT або UPDATE завершується успішно лише при виконанні заданої умови (виразу, що повертає логічний результат). При установці обмеження поля в секції CHECK може використовуватися тільки поле, для якого встановлюється обмеження;
•  REFERENCES. Це обмеження складається з таких секцій:

REFERENCES таблиця [(поле)]. Вхідні значення обмежуваного поля порівнюються із значеннями іншого поля в заданій таблиці. Якщо збіги відсутні, то команда INSERT або UPDATE завершується невдачею. Якщо параметр поле не вказаний, то перевірка виконується за первинним ключем. Обмеження REFERENCES подібне до обмеження таблиці FOREIGN KEY, яке описане в наступному пункті цього підрозділу. Дійсно, між цими обмеженнями є багато спільного. Приклад таблиці, створеної з обмеженням FOREIGN KEY, приведений у лістингу 7.8;

MATCH FULL | MATCH PARTIAL. Секція MATCH указує, чи дозволяється змішувати значення NULL і звичайні значення при вставці в таблицю, у якої зовнішній ключ посилається на декілька полів. Таким чином, на практиці секція MATCH приносить користь лише в обмеженнях таблиць, хоча формально вона може використовуватися і при обмеженні полів. Конструкція MATCH FULL забороняє вставку даних, у яких частина полів зовнішнього ключа містить псевдозначення NULL (крім випадку, коли NULL міститься у всіх полях). У PostgreSQL 7.1.x конструкція MATCH PARTIAL не підтримується. Якщо секція MATCH відсутня, то вважається, що поля з псевдозначепиями NULL задовільняють обмеження. Також буде доречно нагадати, що обмеження полів стосується лише одного поля, тому секція MATCH використовується лише в обмеженнях таблиць;

ON DELETE операція. При виконанні команди DELETE для заданої таблиці з обмежуваним полем виконується одна з таких операцій:

 N0 ACTION (якщо видалення спричиняє порушення цілісності посилань, то відбувається помилка; використовується за замовчуванням, якщо операція не вказана),

RESTRICT (аналогічно N0 ACTION),

CASCADE (видалення всіх записів, що містять посилання на запис, який видаляється),

SET NULL (поля, що містять посилання на запис, який видаляється, замінюються псевдозначеннями NULL),

SET DEFAULT (полям, що містять посилання на запис, який видаляється, привласнюється значення за замовчуванням);

ON UPDATE операція. При виконанні команди UPDATE для заданої таблиці виконується одна з вищеописаних операцій. За замовчуванням використовується значення N0 ACTION. Якщо вибрана операція CASCADE, всі записи, які містять посилання на оновлюваний запис, оновлюються новим значенням (замість видалення, як у випадку з ON DELETE CASCADE);

DEFERRABLE | NOT DEFERRABLE. Значення DEFERRABLE дозволяє відкласти виконання обмеження до кінця транзакції (замість негайного виконання після завершення команди). Значення NOT DEFERRABLE означає, що обмеження завжди перевіряється відразу ж після завершення чергової команди. В цьому випадку користувач не може відкласти перевірку обмеження до кінця транзакції. За замовчуванням вибирається саме цей варіант;

INITIALLY DEFERRED | INITIALLY IMMEDIATE. Секція INITIALLY задається лише для обмежень, визначених з ключовим словом DEFERRED. Значення INITIALLY DEFERRED відкладає перевірку обмеження до кінця транзакції, а при установці значення INITIALLY IMMEDIATE перевірка проводиться після кожної команди. За відсутності секції INITIALLY за замовчуванням використовується значення INITIALLY IMMEDIATE. У прикладі нижче показано створення таблиці gazpr з декількома обмеженнями.

         CREATE TABLE gazpr

         (kod_g integer PRIMARY KEY CHECK (kod_g  > 0),

          naz_g CHARACTER(80) NOT NULL,

          kilnyt INTEGER CHECK (kilnyt > 1),

          diam INTEGER);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'gazpr_pkey'  for table 'gazpr'

        Тут створюється поле kod_g типу integer, для якого встановлюються обмеження PRIMARY KEY і CHECK. Обмеження PRIMARY KEY також має на увазі обмеження NOT NULL і UNIQUE і зумовлює автоматичне створення індекса gazpr_pkey для обмежуваного поля. Обмеження CHECK гарантує, що значення поля kod_g завжди буде додатнє, а поля kilnyt – більше за одиницю. Це означає, що будь-які спроби вставки або оновлення в таблиці gazpr записів, поле kod_g яких менше або дорівнює 0, завершуються невдачею.

        Таблиця gazpr також містить текстове поле naz_g, для якого встановлено обмеження NOT NULL. Воно забороняє мати в таблиці записи, поле naz_g  яких містить NULL. Інакше кажучи, це поле обов’язкове для заповнення.

        Обмеження таблиць подібні до обмежень полів, але на відміну від них, можуть стосуватися відразу декількох полів таблиці.

       Нижче створюються дві таблиці: gazpr і oblik з трьома обмеженнями:

        CREATE TABLE gazpr

        (kod_g INTEGER,

         naz_g CHARACTER(80),

         kilnyt  INTEGER,

         diam INTEGER,

         CONSTRAINT aaa PRIMARY KEY (kod_g),

         CONSTRAINT bbb CHECK (kilnyt  IS NOT NULL  AND diam IS NOT NULL);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pkey' for table 'gazpr'

        CREATE TABLE oblik

        (kod_g INTEGER,

         kod_p INTEGER,

         data DATE,

         kilk REAL,

         CONSTRAINT ccc  FOREIGN KEY (kod_g)

         REFERENCES gazpr(kod_g)

         ON DELETE CASCADE

         ON UPDATE CASCADE);

NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)

 

        Перше обмеження, aaa належить до типу PRIMARY KEY і встановлюється для поля kod_g. Воно практично не відрізняється від обмеження PRIMARY KEY для поля, оскільки список у даному прикладі складається всього з одного поля.

        Обмеження bbb гарантує, що поля kilnyt і diam не буде містити псевдозначення NULL.

        Нарешті, обмеження ccc таблиці oblik за допомогою конструкцій FOREIGN KEY і REFERENCES гарантує, що значення поля kod_g таблиці oblik зустрічаються і в полі kod_g таблиці gazpr. Більш того, оскільки в секціях ON DELETE і ON ACTION зустрічається ключове слово CASCADE, будь-які модифікації поля kod_g в таблиці gazpr спричинять каскадні зміни записів у таблиці oblik, а при видаленні записів з таблиці gazpr будуть видалені відповідні записи таблиці oblik.

        Для цих обмежень в базі даних автоматично будується індекс gazpr_pkey для поля kod_g, а також створюється тригер. Індекс забезпечує виконання обмеження PRIMARY KEY таблиці gazpr, а тригер належить до обмеження FOREIGN KEY таблиці oblik. Про застосування тригерів буде сказано пізніше у відповідному розділі.

        Зауважимо, що деякі версії PostgreSQL забезпечують лише додавання обмежень CHECK і FOREIGN KEY. 

        Вибір режиму перевірки обмежень забезпечує команда:

SET CONSTRAINTS { ALL | режим [.... ] } { DEFERRED | IMMEDIATE }

        Параметри:

ALL означає, що вказаний режим повинен стосуватися всіх обмежень;

режим – ім’я обмеження, для якого встановлюється режим перевірки;

DEFERRED – перевірка обмежень (або конкретного обмеження) вікладається до

                       момента фіксації транзакції, тобто до виконання команди COMMIT;

IMMEDIATE – всі або конкретне обмеження перевіряються наприкінці кожної команди.

        Команда SET CONSTRAINTS задає режим перевірки для всіх обмежень або одного обмеження в поточному транзакційному блоці. Існує два режими перевірки: негайна (IMMEDIATE) і відкладена (DEFERRED) перевірка обмежень. У режимі IMMEDIATE всі обмеження перевіряються після виконання кожної команди транзакції, а в режимі DEFERRED обмеження перевіряються лише після виконання команди COMMIT.

        Зауважимо, що деякі версії PostgreSQL забезпечують зміну режиму перевірки тільки для обмеження FOREIGN KEY. Команда SET CONSTRAINTS не разповсюджується на обмеження CHECK і UNIQUE

        Нижченаведена команда вибирає режим негайної перевірки всіх обмежень.

        SET  CONSTRAINTS  ALL  IMMEDIATE;