Vastus (01.12.2022 13:40): Järgnevad koodinäited on tehtud PostgreSQL jaoks. Oracle andmebaasi kasutamise korral tekiks samasugune probleem ja ka lahendusvariandid oleksid samasugused.

Olgu andmebaasis tabelid: [Meiliaadress]-1-----0..1-[Kasutajakonto]

CREATE TABLE Meiliaadress (e_meil VARCHAR(254) NOT NULL,
CONSTRAINT pk_meiliaadress PRIMARY KEY (e_meil),
CONSTRAINT chk_meiliaadress_e_meil CHECK (e_meil LIKE '%@%'));

CREATE TABLE Kasutajakonto (e_meil VARCHAR(254) NOT NULL,
on_aktiivne BOOLEAN NOT NULL DEFAULT TRUE,
CONSTRAINT pk_kasutajakonto PRIMARY KEY (e_meil),
CONSTRAINT fk_kasutajakonto_meiliaadress FOREIGN KEY (e_meil) REFERENCES Meiliaadress (e_meil) ON UPDATE CASCADE ON DELETE CASCADE);

INSERT INTO Meiliaadress(e_meil) VALUES ('mart.mets@hot.ee');
INSERT INTO Kasutajakonto(e_meil) VALUES ('mart.mets@hot.ee');

Soovin tabelis Meiliaadress veerul e_meil kontrollida unikaalsust tõstutundetult (st kui on näiteks registreeritud meiliaadress mart.mets@hot.ee, siis ei saa registreerida meiliaadressi Mart.Mets@hot.ee). Selleks pean kustutama veerule e_meil viitama välisvõtme kitsenduse ja siis primaarvõtme kitsenduse.

ALTER TABLE Kasutajakonto DROP CONSTRAINT fk_kasutajakonto_meiliaadress;

ALTER TABLE Meiliaadress DROP CONSTRAINT pk_meiliaadress;

Proovin jõustada tõstutundetut unikaalsust kasutades unikaalset funktsioonil põhinevat indeksit. See ebaõnnestub, sest välisvõtme kitsendust ei saa tagasi panna.

CREATE UNIQUE INDEX pk_meiliaadress ON Meiliaadress(Upper(e_meil));

ALTER TABLE Kasutajakonto ADD CONSTRAINT fk_kasutajakonto_meiliaadress FOREIGN KEY (e_meil) REFERENCES Meiliaadress (e_meil) ON UPDATE CASCADE ON DELETE CASCADE;

ERROR:  there is no unique constraint matching given keys for referenced table "meiliaadress"

Proovin jõustada tõstutundetut unikaalsust kasutades EXCLUDE kitsendust (UNIQUE kitsenduse üldistus, mis on spetsiifiline PostgreSQLile). See ebaõnnestub, sest välisvõtme kitsendust ei saa tagasi panna.

DROP INDEX pk_meiliaadress;

ALTER TABLE Meiliaadress ADD CONSTRAINT
pk_meiliaadress EXCLUDE
(Upper(e_meil) WITH =);

ALTER TABLE Kasutajakonto ADD CONSTRAINT fk_kasutajakonto_meiliaadress FOREIGN KEY (e_meil) REFERENCES Meiliaadress (e_meil) ON UPDATE CASCADE ON DELETE CASCADE;

ERROR:  there is no unique constraint matching given keys for referenced table "meiliaadress"

Mida teha?

Variant 1: Jõustada välisvõtme kitsenduste poolt tagatav viidete terviklikkus kasutades trigereid.

Miks ma seda ei soovita?

Idee on selles, et teha viidete terviklikkuse kontrolli trigeritega. Kui kustutan rea või muudan rida tabelis Meiliaadress, siis kontrollitakse, kas vastav rida on tabelis Kasutajakonto ja vastavalt siis muudetakse või kustutatakse see rida. See triger oleks seotud tabeliga Meiliaadress. Kui lisatakse rida tabelisse Kasutajakonto või muudetakse tabelis Kasutajakonto meiliaadressi, siis kontrollitakse, kas vastav meiliaadress on tabelis Meiliaadress. See triger oleks seotud tabeliga Kasutajakonto. Keerukust lisab see, et PostgreSQLis ei blokeeri andmete lugemine nende andmete samaaegset muutmist (tänu multiversioon konkurentsjuhtimisele) ja seega tuleb trigerites kasutada SELECT .. FOR UPDATE või luua kitsenduste trigerid, mille kontroll on transaktsiooni lõpus.

Andmebaasisüsteemi poolt läbiviidav tabeli elimineerimise teisendus eeldab deklaratiivsete (st mitte trigerite abil realiseeritud) välisvõtme kitsenduste olemasolu.

Variant 2: Lisada tabelisse Meiliaadress surrogaatvõtme veerg meiliaadress_id, kus on süsteemi genereeritud unikaalsed täisarvud. Kasutada tabelis Kasutajakonto välisvõtmena meiliaadress_id, mitte e_meil.

Variant 3:

Taastan algse olukorra.

ALTER TABLE Meiliaadress DROP CONSTRAINT pk_meiliaadress;

ALTER TABLE Meiliaadress ADD CONSTRAINT pk_meiliaadress PRIMARY KEY (e_meil);

ALTER TABLE Kasutajakonto ADD CONSTRAINT fk_kasutajakonto_meiliaadress FOREIGN KEY (e_meil) REFERENCES Meiliaadress (e_meil) ON UPDATE CASCADE ON DELETE CASCADE;

Võtan kasutusele kasutajanime veeru (täiendada tuleks ka andmebaasi disaini mudeleid).

ALTER TABLE Meiliaadress ADD COLUMN kasutajanimi VARCHAR(254);

Kasutajanimi on meiliaadress.

UPDATE Meiliaadress SET kasutajanimi=e_meil;

Kasutajanimi on kohustuslik.

ALTER TABLE Meiliaadress ALTER COLUMN kasutajanimi SET NOT NULL;

Kasutajanimi on tõstutundetult unikaalne.

ALTER TABLE Meiliaadress ADD CONSTRAINT
ak_meiliaadress_kasutajanimi EXCLUDE
(Upper(kasutajanimi) WITH =);

Kasutajanimi on meiliaadress.

ALTER TABLE Meiliaadress ADD CONSTRAINT chk_meiliaadress_e_meil_kasutajanimi CHECK (Upper(e_meil)=Upper(kasutajanimi));

Triger, mis reageerib andmete lisamisele ja uuendamisele tabelis Meiliaadress.

CREATE OR REPLACE FUNCTION f_meiliaadress_kasutajanimi() RETURNS trigger AS $$
BEGIN
NEW.kasutajanimi:=NEW.e_meil;
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER
SET search_path = public, pg_temp;

COMMENT ON FUNCTION f_meiliaadress_kasutajanimi() IS 'See trigeri funktsioon määrab enne uue rea
tabelisse lisamist kasutajanimeks meiliaadressi.
Kasutaja peaks registreerima meiliaadressi, mitte kasutajanime.';

CREATE OR REPLACE TRIGGER trig_meiliaadress_kasutajanimi BEFORE INSERT OR UPDATE OF e_meil, kasutajanimi ON
Meiliaadress
FOR EACH ROW EXECUTE FUNCTION f_meiliaadress_kasutajanimi();

Katsetan, kas lahendus töötab nagu vaja.

Sama meiliaadressi ei saa mitu korda kasutada.

INSERT INTO Meiliaadress(e_meil) VALUES ('mart.mets@hot.ee');
ERROR:  duplicate key value violates unique constraint "pk_meiliaadress"

Sama meiliaadressi ei saa mitu korda kasutada isegi siis, kui kasutada teistsugust suur- ja väiketähtede kombinatsiooni.

INSERT INTO Meiliaadress(e_meil) VALUES ('Mart.Mets@hot.ee');
ERROR:  conflicting key value violates exclusion constraint "ak_meiliaadress_kasutajanimi"

Sellist meiliaadressi ei olnud registreeritud.

INSERT INTO Meiliaadress(e_meil) VALUES ('Martin.Mets@hot.ee');
--OK

Ka olemasolev meiliaadressi või kasutajanime uuendamine ei anna tulemuseks reeglitele mittevastavaid andmeid.

UPDATE Meiliaadress SET e_meil='Mart.Mets@hot.ee' WHERE e_meil='Martin.Mets@hot.ee';
ERROR:  conflicting key value violates exclusion constraint "ak_meiliaadress_kasutajanimi"

UPDATE Meiliaadress SET kasutajanimi='midagi' WHERE e_meil='Martin.Mets@hot.ee';
--Tänu trigerile andmed ei muutunud.