Laravel - klucz obcy 2

Laravel tylko z nazwy, kontynuujemy poznawanie baz danych w kursie Laravela, bo dobra ich znajomość będzie nam dalej potrzebna. Do dzieła.

Ok, idziemy do addresses, structure, widok relacyjny i co widzimy:

addresses_ibfk_1 (name)
on delete restrict
on update restrict
column person_id
table people
table column: id

Pora wyjaśnić sobie co to wszystko znaczy. Mamy adres, który należy do osoby o id 3. Czy to oznacza (on update restrict), że nie możemy robić update na tę osobę?

UPDATE `people`
SET age = 35
WHERE id = 3;

Nie! Oczywiście, że możemy. Natomiast update na id tej osoby jest zakazany, bo wtedy person_id adresu pokazywałoby id, który nie istnieje w people.

Ok, czyli update restrict znaczy nie wolno zmieniać pól w people, które są powiązane kluczem obcym w addresses i mają swoje odzwierciedlenie. No id w people i person_id w addresses, jak zmienisz id osoby to person_id będzie pokazywać id, które nie istnieje.

Tego nie chcemy. Ok, a usuwanie?

DELETE FROM `people` WHERE id = 3;

Jakbym usunął osobę o id 3, to ta osoba i całe jej id zniknie. A person_id w adresach nie może pokazywać id, które nie istnieje, dlatego mamy faila, to jest zakazane.

Ok, poprzednio poznaliśmy komendę truncate (usuń dane i zresetuj auto_incrementy), teraz poznamy komendę drop table:

DROP TABLE `addresses`;

Ok, teraz tworzymy z palca naszą tabelkę:

CREATE TABLE `addresses` ( 
    id BIGINT  UNSIGNED NOT NULL AUTO_INCREMENT,
    person_id BIGINT UNSIGNED NOT NULL,
    city VARCHAR(255) NOT NULL,
    street VARCHAR(255) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (person_id) REFERENCES people(id) ON DELETE CASCADE ON UPDATE CASCADE
   );

Do tej tabelki, do structure, do widoku relacyjnego. Patrzymy i co widzimy?

Po pierwsze nadal nie kontrolujemy nazwy naszego fk contraint. Po drugie on delete cascade on update cascade czyli usuń osobę, usuniesz i adres, zmień osobie id, adres też ginie.

Zmieńmy sobie „klikaczem” (dropdown) on update z cascade na set null i ustawmy „go”, phpMyAdmin zrobi wszystko za nas…

I mamy error. I nauczkę, aby nigdy nie robić tak, że coś sobie wyklikujemy, nie wiedząc co jest grane. Teraz nic, tylko rwać włosy z głowy i rozpaczać, że to nie dla nas.

Albo możemy przeczytać error i ogólnie zastanowić się co tu jest nie tak. Może to, że person_id jest ustawione na not null? Więc jak niby na nulla ma się zamienić gdy zrobimy update id na person?

Dropniemy tabelkę raz jeszcze i teraz już porządnie ją utworzymy:

CREATE TABLE `addresses` ( 
    id BIGINT  UNSIGNED NOT NULL AUTO_INCREMENT,
    person_id BIGINT UNSIGNED,
    city VARCHAR(255) NOT NULL,
    street VARCHAR(255) NOT NULL,
    PRIMARY KEY(id),
    CONSTRAINT FK_PersonAddr
    FOREIGN KEY (person_id) REFERENCES people(id) ON DELETE CASCADE ON UPDATE SET NULL
   );

Jak wejdziemy do relacyjnego to już wszystko będziemy rozumieć. Wszystko ustawiliśmy. Pytanie, dlaczego on update ma być na set null?

No pomyślmy, z jakichś powodów (idiotycznych, ale jakichś) robimy update id danego rekordu. Cała reszta (imię, nazwisko, wiek, jakiekolwiek dane) zostaje, tylko id się zmieniło.

Jako że id się zmieniło, adres nie może wskazywać na id, które nie istnieje, ale dlaczego ma zostać usunięty? Możemy przecież wcześniej id adresu sobie zapisać gdzieś w zmiennej, potem zrobić update osoby, adres będzie mieć przez chwilę person_id ustawione na null, ale nie potrwa to długo, bo później dopiszemy poprawny id do person_id.

Ok, tak czy inaczej, pora zrobić kilka ćwiczeń.

Po pierwsze usuniemy pana, który adresu nie ma, mieliśmy to zrobić wcześniej, zrobimy to teraz wprowadzając operator AND:

DELETE FROM `people` 
WHERE id = 4 
AND 
name = "Tim Homeless";

Ok, dodajmy jakiś adresik:

INSERT INTO `addresses` 
(person_id, city, street) 
VALUES
(2, "Warsaw", "Warsaw Street");

Widzimy, adresik jest, wszystko gra. Teraz sobie zmienimy ID osoby, na którą adres wskazuje:

UPDATE `people` 
SET id = 40
WHERE id = 2;

Osobie zmieniliśmy id, zaś adresik ma teraz null w person_id. A jaki ma ID adres? U mnie 1, dopiszmy go (spróbujmy) do nieistniejącego id:

UPDATE `addresses` 
SET person_id = 42
WHERE id = 1;

Nie da rady, nie ma osoby o id 42. Ale jest o id 40:

UPDATE `addresses` 
SET person_id = 40
WHERE id = 1;

Jest już adresik z nowym id. Teraz usuniemy osobę o id 40:

DELETE FROM `people` 
WHERE id = 40;

Osoba o id 40 znika, adres z person_id 4 również znika z bazy danych. Podsumujmy:

Nie potrzebujemy klucza obcego, aby robić joiny, wystarczy, że person_id będzie dokładnie tego samego typu co id w people
Jeżeli person_id jest tego samego typu co id w people i użyjemy FOREIGN KEY (person_id) REFERENCES people(id) to:
- tworzy się jakiś constraint o jakieś dziwnej nazwie
- on delete restrict czyli nie możemy usunąć osoby, której id jest powiązane z person_id w adresach
- on update restrict czyli jak person_id pokazuje na jakiś id w people to w people tego id nie możemy zmienić
- nie wpiszemy do addresses rekordu z person_id takim, który w people nie ma numeru id o takiej samej liczbie
CONSTRAINT FK_PersonAddr czyli nazwa constrainta
ON DELETE czyli co robić, gdy usuniemy rekord, na który ta tabela pokazuje w person_id
- restrict – nie pozwalać na usuwanie people, jeżeli person_id w addresses na nie pokazuje
- cascade – jak usuniesz w people rekord o id na który person_id pokazuje w addresses to ten adres też usuń
- set null – person_id musi być nullable. Jeżeli usuniesz person o takim id, to person_id ustaw na null w addresses, ale zachowaj ten adres
ON UPDATE – określa co mamy robić z addresses jeżeli person_id pokazuje na person, ale ktoś tej person próbuje zrobić update na id

Może i trudne, ale bez zrozumienia tego z Laravelem i ORMem będzie jeszcze trudniej.

Laravel – klucz obcy 2