Ограничения (CONSTRAINTS). Заявки INSERT, UPDATE, DELETE

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

Ограниченията в езика SQL спомагат за запазване интегритета на базата, за достоверност на данните и за неналичие на някои аномалии.  Най –общо правят проверка за валидност на данните в определена колона. Например  вие вече се запознахте с NOT NULL – ограничение, което забранява празни стойности в дадена колона.  Съществуват още, нека ги обобщим:

  • NULL/NOT NULL – разрешава/забранява празни стойности
  • UNIQUE – задължава една колона да има уникални стойности (връзка 1:1)
  • DEFAULT – задава стойност по подразбиране.

Например:

group INT DEFAULT 1;  -задава стойност по подразбиране 1.

  • CHECK –  В повечето езици означава проверка за определена стойност. В MySQL съществува синтактично, но не работи функционално.

Например ще проверяваме дали възрастта е повече от 18 години:

years INT check(years  > 18); – ако проверката не е успешна, няма да се вмъкне записът.

Следващите три ограничения се използват върху колони, които са външен ключ към друга таблица/колона:

  • ON DELETE SET NULL – при изтриване на сочения ред, текущата ст-ст на ключа става NULL
  • ON DELETE RESTRICT – забранява изтриване на референцирани редове, докато все още някои ги референцира (по подразбиране).
  • ON DELETE CASCADE – при изтриване на референциран ред се изтрива и референциращия.

След малко ще дадем и по -конкретен пример.

Заявки INSERT:

Заявките INSERT, както им подсказва името, се използват за вмъкване на данни в таблица.

Синтаксис:

INSERT INTO <таблица>(колона1,колона2,колона N,…)
VALUES(стойност1,стойност2,стойностN,…);

Например нека да вмъкнем 2 спорта в таблицата sports:

USE school_sport_clubs;
INSERT INTO `sports` (`name`) VALUES ('Football');
INSERT INTO `sports` (`name`) VALUES ('Volleyball');

Апострофите при изписването на имената на колоните или на таблиците не са задължителни, но единичните кавички при изписване на стрингове – да. Също така може да вмъквате и повече от един записи на един път. Например нека вмъкнем няколко треньора:

INSERT INTO `coaches` (`name`, `egn`) 
VALUES ('Иван Петков', '7509041245'),
('Георги Иванов Тодоров', '8010091245'),
('Илиян Тодоров Георгиев', '8407106352');

Защо не посочихме колона id и стойност за нея? Защото при декларацията на колоната сме я дефинирали като AUTO_INCREMENT –тоест сървърът ще се грижи да я увеличава с единица всеки път, когато правим нов запис в нея. Съответно id на ‘футбол’ е 1, на ‘волейбол’ е 2 и тн.

Ето как изглеждат последните записи в базата:

1

Заявки UPDATE:

А сега сме се сетили, че сме допуснали правописна грешка в името на първия треньор. Дали да изтрием целия ред или да редактираме само името? Естествено, че ще редактираме само името. Става чрез заявки UPDATE. Те позволяват обновяване (редактиране) на стойностите на колоните за вече направени записи.

Синтаксис:

UPDATE <таблица>
SET <колона1> = <нова стойност>,
<колона2> = <нова стойност>
WHERE <условие>;

<условие> – логическо условие, с което да се определи кой ред ще се обновява.

Ето как ще ъпдейтнем треньора:

UPDATE coaches 
SET name = 'Иван Тодоров Петров'
WHERE id= 1;

Заявки DELETE:

За да изтрием съществуващ ред използваме следния синтаксис:

DELETE FROM <таблица>
WHERE <условие>;

Нека изтрием треньор с ЕГН 8407106352:

DELETE FROM coaches
WHERE EGN = '8407106352';

Преди да продължим нататък, нека да вкараме малко треньори в нашата база:

INSERT INTO `coaches` (`name`, `egn`) VALUES ('Петър Славков Йорданов', '7010102045');
INSERT INTO  `coaches` (`name`, `egn`) VALUES ('Слави Петков Петков', '7106041278');

Нека да вкараме малко ученици в базата:

INSERT INTO students (name, egn, address, phone, class) 
VALUES ('Iliyan Ivanov', '9401150045', 'Sofia-Mladost 1', '0893452120', '10'),
('Ivan Iliev Georgiev', '9510104512', 'Sofia-Liylin', '0894123456', '11'),
('Elena Petrova Petrova', '9505052154', 'Sofia-Mladost 3', '0897852412', '11'),
('Ivan Iliev Iliev', '9510104542', 'Sofia-Mladost 3', '0894123457', '11'),
('Maria Hristova Dimova', '9510104547', 'Sofia-Mladost 4', '0894123442', '11'),
('Antoaneta Ivanova Georgieva', '9411104547', 'Sofia-Krasno selo', '0874526235', '10');

Да запишем и някои студенти в различни спортни групи:

INSERT INTO `school_sport_clubs`.`student_sport` (`student_id`, `sportGroup_id`) VALUES (1, 1),
 (2, 1),
 (3, 1),
 (4, 2),
 (5, 2),
 (6, 2);

Ето какво имаме в таблиците:
2

След като вече знаем как се вмъкват редове, как се променят и как се трият, нека създадем една малка база. Нека имаме отдели и работници в тях. Искаме когато се закрие(изтрие) даден отдел, то да не се изтриват и работниците в него, а да отделът им да остане празен. Когато някой отдел си смени id-то, това да се случи автоматично и в таблицата с работниците и всеки работник да се „пренасочи“ към новия номер на отдел.

Например:

CREATE DATABASE emp_depts;
USE emp_depts;
CREATE TABLE departments(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE employees(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
egn VARCHAR(10),
address varchar(250) NULL DEFAULT NULL,
department_id INT,
CONSTRAINT FOREIGN KEY (department_id) REFERENCES departments(id)
ON DELETE SET NULL ON UPDATE CASCADE 
);
INSERT INTO departments (`name`) 
VALUES ('Човешки ресурси'),
('Финансов анализ'),
('Кредитна администрация');

INSERT INTO employees (name, egn, address, department_id) 
VALUES ('Иван Райнов', '7412051425', 'София-Младост', '1'),
 ('Георги Георгиев', '7512032154', 'София - Изток 23', '1'),
 ('Елеонора Петрова', '8405051245', 'София- Лагера бл. 34', '2');

Нека изтрием отдел „Човешки ресурси“:

DELETE FROM 
departments
WHERE ID = 1; 

Автоматично department_id ще стане NULL заради ограничението, което сме наложили при създаването на таблицата и в частност на външния и ключ.
3

Нека променим id на Финансов анализ да бъде 4:

UPDATE departments SET id=4 
WHERE id=2;

Автоматично се променя и department_id на Елеонора Петрова:

4

Даниел Джолев

 

Leave a Reply

Your email address will not be published. Required fields are marked *