Kuidas jõustada MS Accessis kitsendus, et kui üks ja sama töötaja omab ühte ja sama rolli mitu korda, siis ei tohi rolli omamise ajaperioodid kattuda?

Saan jõustada nõrgema kitsenduse, mille kohaselt andmete registreerimise hetke seisuga ei tohi ühel ja samal töötajal olla ühe ja sama rolli kohta rohkem kui üks kehtiv rolli omamine. Kehtiv rolli omamine on selline, mille puhul hetke aeg jääb rolli omamise alguse ja lõpu aja vahele. Seega, kui töötajal 1 on 01.01.2022 kuni 01.01.2023 roll koodiga 3, siis ei saa 06.01.2022 lisada sellele töötajale teist rolli 3 omamist vahemikuks 01.05.022 kuni 01.05.2023.

ALTER TABLE Tootaja_rolli_omamine
ADD CONSTRAINT chk_tootaja_rolli_omamine_unikaalsus
CHECK (NOT EXISTS
(SELECT isik_id, tootaja_roll_kood
FROM Tootaja_rolli_omamine
WHERE Now() BETWEEN alguse_aeg AND lopu_aeg
OR (alguse_aeg<=Now() AND lopu_aeg IS NULL)
GROUP BY isik_id, tootaja_roll_kood
HAVING Count(*)>1));

MS Accessis, erinevalt paljudest teistest andmebaasisüsteemidest, saab CHECK-kitsenduses kasutada alampäringut. SQL-standard ei luba CHECK-kitsendustes kasutada mittedeterministlikku funktsiooni. Selline funktsioon tagastab samade argumentidega väljakutsudes erinevatel hetkedel erineva tulemuse. Now() on mittedeterministlik funktsioon.

MS Access (aga ka näiteks PostgreSQL) lubavad CHECK-kitsendustes selliseid funktsioone kasutada.

Võrdluseks - PostgreSQLis saab küsimuses nimetatud piirangu jõustada täielikult. Selleks tuleb luua EXCLUDE kitsendus. EXCLUDE kitsendus on unikaalsuse kitsenduse edasiarendus, mille puhul saab väärtuste unikaalsuse e kordumatuse kontrollimiseks kasutada ka mõnda muud operaatorit kui võrdsuse kontrolli operaator (=), mida kasutab UNIQUE kitsendus. Vaadake täpsemat näidet slaidikomplektist. Otsige sealt fraasi "Kattuvate perioodide vältimine".