#295 Support assertions and / or 'select' in check constraints

open
nobody
engine (144)
5
2013-08-14
2013-08-14
Richard Thomas
No

I know it's a big ask and nobody else supports it, but it has been in the SQL standard since 1999....
Supporting select statements in check constraints e.g.
MY_DATE DATE CONSTRAINT NO_HOL CHECK (MY_DATE NOT IN(SELECT DATE FROM HOLIDAY_DATES))
or
CREATE ASSERTION NO_HOL CHECK NOT EXISTS (SELECT blah blah blah)
would mean we wouldn't have to use triggers to do anything other than trivial integrity constraints.

Discussion

  • Fred Toussi
    Fred Toussi
    2013-08-14

    A limited, one way implementation shouldn't be more difficult than the existing trigger support. However, this type of constraint must be checked each time the contents of the referenced tables change, making it rather inefficient to support unless those tables are generally read only.

     
  • Richard Thomas
    Richard Thomas
    2013-08-15

    I'm not sure what you mean by one way, but It would be more efficient for the DBMS to handle this than the user having to use a combination of triggers and a stored procedure. At the moment I have to manually check which attributes of which tables contribute to the assertions and ensure the triggers are only fired for these attributes and these tables, which adds a maintenance burden.
    The other advantage is that the constraint could be set to be deferred to the end of the transaction, which is not possible with triggers.