C, PHP, VB, .NET

Дневникът на Филип Петров


* CHECK constraint

Публикувано на 25 февруари 2010 в раздел Бази от Данни.

В статията за вложен SELECT представихме едно допълнение към ER диаграмата за база от данни на университет. Да припомним – проблемът беше, че в оригиналния ER модел връзката между студенти и факултети минаваше през записани учебни предмети. Така ние нямаше възможност да разберем от кой факултет е даден студент, ако той не е записал нито един учебен предмет. Предложеното решение беше да пазим допълнителен външен ключ от таблицата „студенти“ към таблицата „факултети“:

Това обаче не решава друг основен проблем – какво гарантира, че дадения студент ще запише само и единствено предмети от този факултет? Отговорът е, че все още нищо не го гарантира! Нека демонстрираме това като построим същата диаграма в опростен вид (без таблици университети, преподаватели и задочници, а директна връзка факултети -< предмети >-< студенти):

CREATE TABLE faculties(
   `id` INT UNSIGNED AUTO_INCREMENT,
   PRIMARY KEY(id),
   `name` VARCHAR(255) NOT NULL UNIQUE
) ENGINE=INNODB;

CREATE TABLE subjects(
   `id` INT UNSIGNED AUTO_INCREMENT,
   PRIMARY KEY(id),
   `name` VARCHAR(255) NOT NULL,
   `faculty_id` INT UNSIGNED NOT NULL,
   FOREIGN KEY(faculty_id) REFERENCES faculties(id),
   UNIQUE(name, faculty_id)
) ENGINE=INNODB;

CREATE TABLE students(
   `id` INT UNSIGNED AUTO_INCREMENT,
   PRIMARY KEY(id),
   `name` VARCHAR(255) NOT NULL,
   `faculty_id` INT UNSIGNED NOT NULL,
   FOREIGN KEY(faculty_id) REFERENCES faculties(id)
) ENGINE=INNODB;

CREATE TABLE subjects_students(
   `student_id` INT UNSIGNED NOT NULL,
   `subject_id` INT UNSIGNED NOT NULL,
   PRIMARY KEY(student_id, subject_id),
   FOREIGN KEY(student_id) REFERENCES students(id),
   FOREIGN KEY(subject_id) REFERENCES subjects(id)
) ENGINE=INNODB;

Сега да вмъкнем информация в таблиците и да демонстрираме проблема:

INSERT INTO faculties(`name`)
      VALUES ("FKSU"),("FET");

INSERT INTO subjects(`name`, `faculty_id`)
VALUES ("Bazi Danni", (SELECT id FROM faculties WHERE name="FKSU")),
       ("Sapromat", (SELECT id FROM faculties WHERE name="FET"));

INSERT INTO students(`name`, `faculty_id`)
VALUES ("Ivan Ivanov", (SELECT id FROM faculties WHERE name="FKSU"));

Така имаме два факултета (FKSU и FET), които имат по един учебен предмет всеки:

SELECT * FROM faculties;
+----+------+
| id | name |
+----+------+
|  2 | FET  |
|  1 | fksu |
+----+------+

SELECT * FROM subjects;
+----+------------+------------+
| id | name       | faculty_id |
+----+------------+------------+
|  1 | Bazi Danni |          1 |
|  2 | Sapromat   |          2 |
+----+------------+------------+

mysql> SELECT * FROM students;
+----+-------------+------------+
| id | name        | faculty_id |
+----+-------------+------------+
|  1 | Ivan Ivanov |          1 |
+----+-------------+------------+

Ние сме сигурни, но нека все пак се убедим, че проблема е налице – ще запишем Иван Иванов едновременно и за двата предмета, които са от различни факултети:

INSERT INTO subjects_students(subject_id, student_id)
VALUES ((SELECT id FROM subjects WHERE name="Bazi Danni"),
              (SELECT id FROM students WHERE name="Ivan Ivanov")),
       ((SELECT id FROM subjects WHERE name="Sapromat"),
              (SELECT id FROM students WHERE name="Ivan Ivanov"));

Проблемът вече е налице и имаме невалидна информация в базата от данни:

SELECT students.name AS student, faculties.name AS faculty
FROM students JOIN faculties ON students.faculty_id = faculties.id
WHERE students.name="Ivan Ivanov";
+-------------+---------+
| student     | faculty |
+-------------+---------+
| Ivan Ivanov | FKSU    |
+-------------+---------+

SELECT students.name AS student, faculties.name AS faculty
FROM students
JOIN subjects_students ON students.id = subjects_students.student_id
JOIN subjects ON subjects.id = subjects_students.subject_id
JOIN faculties ON faculties.id = subjects.faculty_id
WHERE students.name = "Ivan Ivanov";
+-------------+---------+
| student     | faculty |
+-------------+---------+
| Ivan Ivanov | FKSU    |
| Ivan Ivanov | FET     |
+-------------+---------+

Очевидно, за да се справим с този проблем ние трябва да НЕ позволяваме в таблицата subjects_students да се записват предмети, които са от „чужд“ факултет. Това може да се постигне чрез ограничение CHECK. Ето как можем да поправим предишната CREATE TABLE заявка, за таблица „subjects_students“:

CREATE TABLE subjects_students(
   `student_id` INT UNSIGNED NOT NULL,
   `subject_id` INT UNSIGNED NOT NULL,
   PRIMARY KEY(student_id, subject_id),
   FOREIGN KEY(student_id) REFERENCES students(id),
   FOREIGN KEY(subject_id) REFERENCES subjects(id),
   CHECK (subject_id = (SELECT subjects.id FROM subjects 
                        WHERE subjects.faculty_id=( 
                            SELECT students.faculty_id FROM students 
                            WHERE students.id = student_id 
                        ) 
                       ) 
         )
) ENGINE=INNODB;

Така ние въвеждаме ограничение, чрез което забраняваме добавянето на предмет от различен факултет от този на студента. За съжаление тази функционалност все още не работи в СУБД MySQL. До сега (най-новата версия е MySQL 5.1.41) CHECK ограниченията се приемат от MySQL като валидни записи, но в последствие не се проверяват при изпълнение на заявки INSERT (тоест вие можете да продължите да въвеждате невалидни данни). Очаква се подобна функционалност да бъде въведена скоро, но за сега не е налична. Все пак е добре да я познавате, защото в други СУБД като Oracle например функционалността CHECK работи отлично. За момента, при използване на MySQL, е в ръцете на програмиста да се проверяват тези циклични зависимости през софтуера достъпващ базата от данни.

Затова при използване на MySQL засега бъдете внимателни – интегритета на данните може да бъде нарушен дори когато ограниченията са уж написани перфектно – различните СУБД имплементират функционалности по различен начин (а както виждате понякога въобще не се имплементират). Ако все пак желаете да реализирате споменатата по-горе функционалност и контрола все пак да бъде на ниво база от данни с MySQL, а не при приложението – тогава забранете заявки INSERT и въведете алтернатива чрез съхранени процедури.

Задача: Начертайте опростената ER диаграма на примерната база от данни

Задача: „Поправете“ цикличната зависимост в базата от данни banks, която беше разгледана в предишни статии, чрез въвеждане на ограничение/я CHECK.

Задача: Създайте съхранена процедура за „записване на студент към предмет“, при която не се позволява записването към предмет от „чужд“ факултет.

 



8 коментара


  1. Ами то затова е UNIQUE(name, faculty_id) – така КОМБИНАЦИЯТА от двете е уникална. Няма проблеми да има двама студенти с едно и също име, но с различни факултетни номера.

  2. Владимир каза:

    He били трябвало в таблицата students да няма unique(name, faculty_id)? Все пак е възможно да има двама студенти с едно и също име в един факултет?

  3. Да – това е обръщение към таблица faculties и колона id.

  4. Dimitrov каза:

    А това faculties(id) не е ли номера на факултета (1 за ФКСУ, 2 за ФЕТТ ит.н.)?

  5. Dimitrov каза:

    Тогава UNIQUE(name, faculty_id)няма ли да забранява точно това: Да няма 2ма Иван Василев (name) във ФКСУ (faculty_id), освен ако не съм разбрал нещо грешно и редът „FOREIGN KEY(faculty_id) REFERENCES faculties(id)“ не означава че faculty_id = faculties(id)

  6. Точно това забранява. Но не познаваш кое за какво се използва:

    1. PRIMARY KEY се използва за „уникален идентификатор на обект от класа“. Това означава, че по PK ще различаваме редовете;

    2. FOREIGN KEY се използва за да покажем връзка между един клас обекти с друг.

    3. UNIQUE се използва за да покажем в коя колона/и не може да има повторения.

    PRIMARY KEY е едновременно UNIQUE и NOT NULL.

  7. Мишо каза:

    Според мен Dimitrov и Владимир са прави. Мисля, че в таблица students трябва да бъде UNIQUE(id, faculty_id). Както е написано в момента UNIQUE(name, faculty_id) наистина излиза, че не може да има 2-ма студенти с едно и също име в един и същи факултет.

  8. Да, сега като го гледам, наистина е грешка. Объркал съм „faculty_id“ с „факултетен номер“, а то не е. Ще редактирам статията.

Добави коментар

Адресът на електронната поща няма да се публикува


*