Ако притежавате предприятие, трябва да сте се сблъскали със стойността и нуждата от данни във вашия бизнес. Наличието на средства за съхранение и манипулиране на бази данни добавя повече стойност към бизнеса.
Базите данни са организирани по определена конвенция и ви позволяват да структурирате данни във връзки, което ни води до релационни бази данни, които са възприети като форма на управление на данни от 1970 г. насам. А на днешния пазар релационните бази данни са предпочитани заради техните възможности при манипулиране на данни.
Въпреки че има много налични релационни бази данни, MySQL стигна до водещата позиция, класирайки се като номер две в света, според Statista, към януари 2022 г.
В SQL сървъра ограниченията са предварително дефинирани правила и ограничения, наложени или в една, или в множество колони; те са обвързани със стойностите в колоната и помагат за поддържане на целостта, точността и надеждността на данните в посочените колони.
Просто казано, само данни, които отговарят на правилото за ограничение, се вмъкват успешно в колоната. Операцията за вмъкване се прекратява, ако данните не отговарят на критериите.
Тази публикация предполага, че сте се сблъскали с релационни бази данни, по-специално – MySQL, и с нетърпение очакваме да укрепите знанията си в областта. В крайна сметка ще споделя някои съвети за взаимодействие с ограниченията на външния ключ.
Съдържание
Основни ключови ограничения – Обобщение
Една таблица в SQL включва колона или няколко, съдържащи ключови стойности, които точно определят всеки ред в системите. Колоната или колоните, озаглавени първичен ключ (PK) на таблицата, имат ролята на налагане на целостта на обекта на таблицата. Ограниченията на първичния ключ гарантират уникални данни и често се дефинират в колона за идентичност.
При указване на ограниченията на първичния ключ за вашата таблица, машината на базата данни автоматично налага уникалност на данните чрез генериране на уникални индекси за всяка от основните колони. Първичните ключове предлагат изключително предимство, когато се използват в заявки, като осигуряват бърз достъп до данни.
Ако ограниченията на първичния ключ са дефинирани в множество колони, това се нарича съставен или съставен първичен ключ. И в този случай всяка колона с първичен ключ може да съдържа дублирани стойности. Комбинираните стойности от всички колони в първичния ключ обаче трябва да бъдат уникални.
Добър пример е случай, в който имате таблица с колони `id`, `names` и `age`. Когато дефинирате неговото ограничение за първичен ключ върху комбинацията от `id` и `names`, можете да имате дублиращи се екземпляри на стойностите `id` или `names`. Все пак всяка комбинация трябва да е уникална, за да се избегнат дублиращи се редове. Така че можете да имате записи с `id=1` и `name=Walter` и `age-22` и `id=1`, `name=Henry` и `age=27`, но не можете да имате други записи с `id=1` и `name=Walter`, защото комбинацията не е уникална.
Ето някои важни аспекти, които трябва да знаете:
Ограничения на външни ключове – обобщение
Външният ключ (FK) включва колона или комбинация от няколко, използвани за създаване и свързване на връзка между две таблици и управлява данните, които да се съхраняват в таблица с външен ключ.
Препратка към външен ключ включва създаване на връзка между две таблици; когато колона или колони, съдържащи първичния ключ за друга таблица, се препращат от колона или колони в различна таблица.
В сценария за препратка към външен ключ се създава връзка между две таблици, когато колона или колони, съдържащи първични ключове в таблица, се препращат от колони в друга.
В случай на практическа употреба можете да имате таблица, Sales.SalesOrderHeader, с външен ключ, свързващ друга таблица, Sales.Person, тъй като има логична връзка между продавачи и поръчки за продажба.
Тук SalesPersonID в колоната SalesOrderHeader се смесва с колоната с първичен ключ на таблицата SalesPerson. Външният ключ на таблицата SalesPerson е колоната SalesPersonID в SalesOrderHeader.
Тази връзка дефинира правило: стойност на SalesPersonID не може да бъде във вашата таблица SalesOrderHeader, ако не съществува в таблицата SalesPerson.
Една таблица може да препраща до 253 други колони и таблици като външни ключове, алтернативно наричани изходящи препратки. От 2016 г. SQL сървърът увеличи броя на таблиците и колоните, които можете да препращате в една таблица, известни също като входящи препратки, от 253 на 10 000. Увеличението обаче идва с някои ограничения:
Какви са предимствата на външните ключове?
Както беше споменато по-горе, ограниченията на външния ключ играят съществена роля за запазването на целостта и съгласуваността на данните в релационната база данни. Ето разбивка на причините, поради които ограниченията на външния ключ са от съществено значение.
Индекси на външни ключови ограничения
Ограниченията на външния ключ не създават автоматично съответни индекси като основния. Можете ръчно да създавате индекси за ограничения на външен ключ; това е полезно поради следните причини.
- Колоните с външен ключ често се използват в критериите за свързване, когато се комбинират данни от свързани таблици в заявки чрез съвпадение на колоните, свързани с ограничението. Индексите помагат на базата данни при намирането на свързани данни в чужда таблица.
- Ако промените ограниченията на първичния ключ, те се проверяват с чуждите в свързаните таблици.
Създаването на индекси не е задължително. Все още можете да комбинирате данни от две таблици, без да указвате ограниченията на първичния и външния ключ. Добавянето на ограничения на външни ключове обаче оптимизира таблиците и ги комбинира в заявка, която отговаря на нейните критерии за използване на ключовете. Ако промените ограниченията на първичния ключ, те се проверяват с чуждите в свързаните.
Съвети за създаване на ограничения за външен ключ в SQL
Вече отделихте значително време за спекулациите; отговори защо. Нека изместим фокуса си и го стесним до тактиката за създаване на ограничения на външен ключ; отговори на въпроса как.
Поле „Външен ключ“ в таблица препраща към „Първичен ключ“ на друга таблица. Таблицата с първичен ключ е вашата родителска таблица. А таблицата с външния ключ се нарича дъщерна таблица. Нека се потопим.
Създаване на външен ключ при създаване на таблица
Когато създавате таблица, можете също да създадете ограничение за външен ключ, за да поддържате референтната цялост. Ето как да го направите:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
Кодът по-горе създава таблица, наречена „поръчки“, с първичен ключ „order_id“, друго цяло число „customer_id“ и датата „order_date“. В този случай ограничението FOREIGN KEY се добавя към колоната „customer_id“ и препраща към „customer_id“ във вашата таблица „customers“.
Създаване на външен ключ след създаване на таблица
Да предположим, че вече сте създали таблица и искате да добавите ограничение за външен ключ; използвайте оператора `ALTER TABLE` във вашия код. Вижте кодовия фрагмент по-долу.
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
В този случай сте добавили ограничение за външен ключ „customer_id“ колона в таблицата „orders“ за препратка към колоната „customer_id“ в таблицата „customers“.
Създаване на външен ключ без проверка за съществуващи данни
Когато добавите ограничение на външен ключ към таблица, базата данни автоматично проверява за съществуващи данни, за да осигури съответствие с ограничението. Ако обаче знаете, че данните са последователни и искате да добавите ограничение без проверката за последователност, ето как да го направите.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) NOT VALIDATE;
Командата NOT VALIDATE казва на базата данни да не проверява за съществуващи данни. Този конкретен случай е полезен в конкретни случаи. Например, когато имате масивни данни и искате да завършите процеса на валидиране.
Създаване на външен ключ чрез DELETE/UPDATE
Докато създавате ограничения на външен ключ, можете да насочите действието, което да се предприеме в случаите, когато посоченият ред се актуализира или изтрие. В този случай вие използвате каскадни ограничения за референтна цялост, за да диктувате действията, които трябва да бъдат предприети. Те включват:
#1. НЕ СЕ ПРЕДПРИЕМАТ ДЕЙСТВИЯ
Както при много други бази данни, правилото „БЕЗ ДЕЙСТВИЕ“ е поведението по подразбиране, когато създавате ограничение за външен ключ. Това означава, че не се предприемат действия, когато посоченият ред бъде изтрит или актуализиран.
Database Engine поражда грешка, ако ограничението за външен ключ е нарушено. Това обаче не се препоръчва, защото може да доведе до проблеми с референтния интегритет, тъй като ограничението на външния ключ трябва да бъде наложено. Ето пример как да го направите:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE NO ACTION ON UPDATE NO ACTION;
#2. КАСКАДА
Правилото ‘CASCADE’ е друга опция за действията ‘ON DELETE’ и ‘ON UPDATE’ при създаване на ограничения на външен ключ. Когато се постави на място, това означава, че всеки път, когато ред се актуализира или изтрие в родителските таблици, референтните редове се актуализират или изтриват съответно. Тази техника е мощна при поддържане на референтна цялост. Ето един пример:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE ON UPDATE CASCADE;
Трябва да внимавате, когато използвате това правило, тъй като може да доведе до нежелани последствия, ако не се използва внимателно. Бихте искали да избегнете случайно изтриване на твърде много данни или създаване на кръгови препратки. Затова използвайте тази опция само ако е необходимо и с повишено внимание.
Има някои правила за използването на CASCADE:
- Не можете да посочите CASCADE, ако колона с клеймо за време е част от външния или референтния ключ.
- Ако вашата таблица има тригер INSTEAD OF DELETE, не можете да посочите ON DELETED CASCADE.
- Не можете да посочите ON UPDATE CASCADE, ако вашата таблица има тригер ВМЕСТО UPDATE.
#3. SET NULL
Когато изтриете или актуализирате съответен ред в родителската таблица, всички стойности, съставляващи външния ключ, се задават на null. Това правило за ограничение изисква колоните с външен ключ да могат да се изпълняват с нулеви стойности и не могат да бъдат посочени за таблици, имащи тригери INSTEAD OF UPDATE. Ето пример как да го направите.
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET NULL ON UPDATE SET NULL
В този случай сте задали колоната с външен ключ „customer_id“ в таблицата „поръчки“ да бъде нула, ако съответният ред в таблицата „клиенти“ бъде изтрит или актуализиран.
#4. ЗАДАВАНЕ ПО ПОДРАЗБИРАНЕ
Тук задавате всички стойности, които правят външния ключ по подразбиране, при условие че посоченият ред в родителската таблица е актуализиран или изтрит.
Това ограничение се изпълнява, ако всички колони с външен ключ имат дефиниции по подразбиране. Ако дадена колона е nullable, нейната стойност по подразбиране е зададена на NULL. Имайте предвид, че тази опция не може да бъде указана за таблици с тригери ВМЕСТО АКТУАЛИЗИРАНЕ. Ето един пример:
ALTER TABLE orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE SET DEFAULT ON UPDATE SET DEFAULT;
В случая по-горе вие сте задали стойността по подразбиране за ‘customer_id’ в таблицата „поръчки”, което се случва, когато съответният ред в таблицата „клиенти” бъде изтрит или актуализиран.
Заключителни думи
В това ръководство сте опреснили ограниченията на първичния ключ и сте се заровили в ограниченията на външния ключ. Също така сте срещали няколко техники за създаване на ограничения на външен ключ. И въпреки че има много начини за създаване на ограничения на външен ключ, тази публикация разкри методите.
И се надявам, че сте усвоили нови техники; не сте ограничени да ги комбинирате. Например методите за ограничения CASCADE, SET NULL, SET DEFAULT и NO ACTION могат да се комбинират в таблици с референтни релации.
Ако вашата таблица срещне NO ACTION, тя се връща към други правила за ограничения. В други случаи действието DELETE може да задейства комбинация от тези правила и правилото NO ACTION ще бъде изпълнено като последно.
След това вижте SQL cheat sheet.