Küsimus
Anonüümne
(22.11.2024 14:45)
Kas CHECK kitsendustes saab kasutada mittedeterministlikke funktsioone?
Vastus (22.11.2024 15:23):
Deterministlik funktsioon tagastab samade argumentidega (sisendandmetega) alati samasuguse väärtuse. Mittedeterministlik funktsioon võib tagastada samade argumentidega erinevatel ajahetkedel erineva tulemuse. Mittedeterministlikud funktsioonid on näiteks CURRENT_DATE (tagastab hetke kuupäeva), CURRENT_TIMESTAMP (tagastab hetke kuupäeva ja kellaaja koos ajavööndiga) ja LOCALTIMESTAMP (tagastab hetke kuupäeva ja kellaaja ilma ajavööndita).
CHECK kitsendus on kujul CHECK(loogikaavaldis). Iga tabelisse lisatava rea korral peab selle loogikaavaldise väärtuse arvutamise tulemus olema TRUE või UNKNOWN (kui reas mõnes väljas väärtus puudub e on NULL).
SQL:2011 standardi kohaselt:
"Certain <boolean value expression>s are identified as “retrospectively deterministic”. A retrospectively deterministic
<boolean value expression> has the property that if it is True at one point time, then it is True for all
later points in time if re-evaluated for the identical SQL-data by an arbitrary user with the identical set of
privileges.
No integrity constraint shall be defined using a <search condition> that is not retrospectively deterministic.
Without Feature F672, “Retrospective check constraints”, conforming SQL language shall not contain a
<check constraint definition> that generally contains CURRENT_DATE, CURRENT_TIMESTAMP, or LOCALTIMESTAMP."
Järgnevas tabeli loomise lauses:
Järgnevas tabeli loomise lauses:
- kitsendus chk_pakkumine_alguse_aeg on retrospektiivselt deterministlik
- Kui lisada tabelisse rida, mis lisamise hetkel vastas sellele kitsendusele, siis see rida vastab sellele kitsendusele ka tulevikus.
- kitsendus chk_pakkumine_lopu_aeg ei ole retrospektiivselt deterministlik
- Kui lisada tabelisse rida, mis lisamise hetkel vastas sellele kitsendusele, siis mingil hetkel tulevikus see rida enam sellele kitsendusele ei vasta. Nii tekib kahetsusväärne olukord, kus tabelis on andmed, mis ei ole kooskõlas tabelile defineeritud kitsendusega.
CREATE TABLE Pakkumine (pakkumine_id INTEGER NOT NULL,alguse_aeg DATE NOT NULL,lopu_aeg DATE NOT NULL,CONSTRAINT pk_pakkumine PRIMARY KEY (pakkumine_id),CONSTRAINT chk_pakkumine_alguse_aeg CHECK (alguse_aeg<=CURRENT_DATE),CONSTRAINT chk_pakkumine_lopu_aeg CHECK (lopu_aeg>=CURRENT_DATE));Eelnevalt viidatud SQL standardi lõik ütleb, et kitsendusi, mis pole retrospektiivselt deterministlikud, ei tohiks saada defineerida. Kui andmebaasisüsteem ei toeta omadust F672, “Retrospective check constraints”, siis ei peaks CHECK kitsendustes saama üldse CURRENT_DATE, CURRENT_TIMESTAMP ja LOCALTIMESTAMP kitsendusi kasutada.
Milline on tegelik olukord andmebaasisüsteemides? Kasutasin katsetamiseks keskkondi DB Fiddle, SQL Fiddle ja db<>fiddle.
Andmebaasisüsteemid jagunesid kaheks:
- CHECK kitsendustes ei saa üldse CURRENT_DATE, CURRENT_TIMESTAMP ja LOCALTIMESTAMP funktsioone kasutada (sulgudes veateade eelneva CREATE TABLE lause käivitamisel).
- MySQL 9 (Query Error: An expression of a check constraint 'chk_pakkumine_alguse_aeg' contains disallowed function: curdate.)
- MariaDB 10.7 (ERROR 1901 (HY000) at line 1: Function or expression 'curdate()' cannot be used in the CHECK clause of `chk_pakkumine_alguse_aeg`)
- Oracle 23ai (ORA-02436: date or system variable wrongly specified in CHECK constraint)
- IBM DB2 Developer-C 11.1 (SQL0546N The check constraint "CHK_PAKKUMINE_ALGUSE_AEG" is invalid.)
- CHECK kitsendustes saab kasutada CURRENT_DATE, CURRENT_TIMESTAMP ja LOCALTIMESTAMP funktsioone piiranguteta, st saab luua ka kitsendusi, mis ei ole retrospektiivselt deterministlikud.
- PostgreSQL 17
- SQLite 3.46
- MS SQL Server 2022 (CURRENT_DATE tuli asendada GETDATE())
- MS Access 2019 (CURRENT_DATE tuli asendada DATE())
- Firebird 4
- HSQLDB 1.8 (LibreOffice Base)
Süsteemides, mis lubavad kitsendusi, mis pole retrospektiivselt deterministlikud, tuleb olla kitsenduste defineerimisel ise tähelepanelik!