Vastus (19.08.2023 12:58):
ON DELETE/ON UPDATE RESTRICT tähendab, et viidete terviklikkust kontrollitakse, enne andmemuudatuse tegemist. Kui muudatuse tulemus rikuks viidete terviklikkuse reeglit, siis seda muudatust ei hakata tegema.
ON DELETE/ONUPDATE NO ACTION tähendab, et andmemuudatus tehakse ära ja kui tulemuseks on viidete terviklikkuse viga, siis rullitakse muudatus tagasi.
RESTRICT on vea tekkimist ennetav tegevus, NO ACTION on tegevusetus, millele järgneb andmebaasisüsteemi poolt vea tekkimisele reageerimine.
Seda kirjeldab SQL standard (2011. aasta redaktsioon), kus on RESTRICT ja NO ACTION kohta kirjas järgnev.
Even if constraint checking is not deferred, ON UPDATE/ON DELETE RESTRICT is a stricter condition than ON UPDATE/ON DELETE NO ACTION. ON UPDATE/ON DELETE RESTRICT prohibits an update/a delete to a particular row if there are any matching rows; ON UPDATE/ON DELETE NO ACTION does not perform its constraint check until the entire set of rows to be updated/deleted has been processed.
SQL lubab lükata kitsenduste täidetuse kontrolli transaktsiooni e tehingu lõppu (deferred constraint checking). See tähendab, et transaktsiooni käigus võivad tehtud muudatused minna selle kitsendusega vastuollu, kuid kui transaktsiooni lõpuks on andmed kitsendusega kooskõlas, siis võtab andmebaasisüsteem transaktsiooni käigus tehtud andmemuudatused vastu. Välisvõtme kitsenduse puhul ei saa kompenseeriva tegevuse läbiviimist transaktsiooni lõppu lükata. Teiste sõnadega, kui kasutada ON UPDATE/ON DELETE RESTRICT, siis kontrollitakse ikkagi koheselt seda, kas viidete terviklikkuse reegel oleks muudatuse tulemusel täidetud või mitte ja kui ei ole, siis lükatakse muudatus tagasi. PostgreSQLis ebaõnnestub sellisel juhul kogu transaktsioon, mis seda muudatust sisaldab.
SQL standard (2011. aasta versioon) kirjutab järtgnevat: "Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the <search condition> of the constraint (a <match predicate>) but does not defer the referential actions of the referential constraint."
PostgreSQL dokumentatsioon ütleb: "NO ACTION means that if any referencing rows still exist when the constraint is checked, an error is raised; this is the default behavior if you do not specify anything. (The essential difference between these two choices is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not.)"
Näide PostgreSQLis.
CREATE TABLE Vanem (vanem_id INTEGER,
CONSTRAINT pk_vanem PRIMARY KEY (vanem_id));
INSERT INTO Vanem(vanem_id) VALUES (1);
CREATE TABLE Laps (laps_id INTEGER,
vanem_id INTEGER NOT NULL,
CONSTRAINT pk_laps PRIMARY KEY (laps_id),
CONSTRAINT fk_laps_vanem FOREIGN KEY (vanem_id) REFERENCES Vanem(vanem_id) ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED);
INSERT INTO Laps (laps_id, vanem_id) VALUES (1,1);
START TRANSACTION;
DELETE FROM Vanem;
/*ERROR: update or delete on table "vanem" violates foreign key constraint "fk_laps_vanem" on table "laps"
DETAIL: Key (vanem_id)=(1) is still referenced from table "laps".*/
ROLLBACK;
/*Transaktsioon ebaõnnestus*/
DROP TABLE Laps;
CREATE TABLE Laps (laps_id INTEGER,
vanem_id INTEGER NOT NULL,
CONSTRAINT pk_laps PRIMARY KEY (laps_id),
CONSTRAINT fk_laps_vanem FOREIGN KEY (vanem_id) REFERENCES Vanem(vanem_id) ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED);
INSERT INTO Laps (laps_id, vanem_id) VALUES (1,1);
START TRANSACTION;
DELETE FROM Vanem;DELETE FROM Laps;
COMMIT;
/*Transaktsioon õnnestus, sest transaktsiooni lõpuks oli eemaldatud ka kustutatud vanemtabeli reaga seotu lapstabeli rida.*/
Kui Te ei kasuta viidete terviklikkuse reegli täidetuse kontrollimise transaktsiooni lõppu lükkamise võimalust, siis on RESTRICT/NO ACTION lõpptulemus sama - viidete terviklikkust rikkuvaid andmemuudatusi teha ei saa. Soovitan sellisel juhul läbivalt kasutada kas ühte või teist,
kuid mitte neid läbisegi, sest see võib tekitada asjatut segadust selles mõttes, et kas andmebaasi tegija on tahtnud konkreetse kitsenduse juures just NO ACTION või RESTRICT kasutamisega midagi öelda (ilmselt ei ole).
Kui Te kompenseerivat tegevust ei määra, siis vaikimisi käitumine on NO ACTION.
Siin on veel üks näide NO ACTION ja RESTRICT erinevuste kohta, mis tuleb välja käitumises seoses tabelitega, mis on üksteisega välisvõtmete kaudu kokku "aheldatud". RESTRICT korral kustutamine ebaõnnestub, sest viidete terviklikkust kontrollitakse enne vanemtabelist rea kustutamist ja selleks ajaks pole teise välisvõtmete ahela kaudu kustutamine lapstabelisse kohale jõudnud. Vastusele lisatud failis on selle näite SQL kood ja seda saab katsetada kasvõi näiteks
DB Fiddle keskkonnas.
NB! Oracles
ei saa välisvõtme deklareerimisel RESTRICT määrata. Välisvõtme kitsenduse definitsioonis
ei saa ka kirjutada NO ACTION - kuid kui jätate kompenseeriva tegevuse täpsustamata, siis NO ACTION on vaikimisi määrang.
Näide Oracles, kus järgmise lausega loodud tabelis oleval välisvõtme kitsendusel on ON UPDATE NO ACTION ja ON DELETE NO ACTION omadus.
CREATE TABLE Laps (laps_id NUMBER(10),vanem_id NUMBER(10) NOT NULL,CONSTRAINT pk_laps PRIMARY KEY (laps_id),CONSTRAINT fk_laps_vanem FOREIGN KEY (vanem_id) REFERENCES Vanem(vanem_id)); 
Kiirvalik
