what is an Assert in Database world?

I searched for Assert and found general explanations:

Assertion (software development)


In computer programming, an assertion is a statement that a predicate
(Boolean-valued function, a true–false expression) is expected to
always be true at that point in the code. If an assertion evaluates to
false at run time, an assertion failure results, which typically
causes the program to crash, or to throw an assertion exception.

and

What does the “assert” keyword do?


Assertions are generally used primarily as a means of checking the
program's expected behavior. It should lead to a crash in most cases,
since the programmer's assumptions about the state of the program are
false. This is where the debugging aspect of assertions come in. They
create a checkpoint that we simply can't ignore if we would like to
have correct behavior.

As a database operatior I only saw asserts when data files got corrupt (repair needed, sometimes data loss). Any more examples and definition specific for database systems (both SQL and NoSQL)?

I have zero to none experience in the world of NoSQL so I'll add some perspective from a RDBM:s side of view.

I believe assertions where introduced in SQL92, the following is an example from: https://mariadb.com/kb/en/sql-99/create-assertion-statement/

CREATE ASSERTION constraint_1 
    CHECK ((SELECT AVG(column_1) FROM Table_1 >40) NOT DEFERRABLE;

Meaning that CONSTRAINT_1 is violated if the average of the TABLE_1.COLUMN_1 values is less than 41.

However, I'm not sure how many RDBM:s that actually implements ASSERTION. Two substitutes often used is CHECK constraints and validation triggers. Example:

ALTER TABLE Table_1 
     ADD CONSTRAINT C1 CHECK (column_1 >= 0);

Such a constraint will prevent any negative values of column_1.

SELECT statements are allowed in CHECK constraints according to standard, but likewise ASSERTIONS I don't think that this is widely implemented. Triggers are commonly use to implement set assertions. Here is one example from the DB2 catalog:

CREATE TRIGGER SYSTOOLS.POLICY_DR  
NO CASCADE  BEFORE DELETE  ON SYSTOOLS.POLICY  
REFERENCING OLD AS OLD_ROW  
FOR EACH ROW  
MODE DB2SQL  
SECURED  
BEGIN ATOMIC    
    IF ( ( SELECT COUNT(*) FROM SYSTOOLS.POLICY 
           WHERE OLD_ROW.MED = MED 
             AND OLD_ROW.DECISION = DECISION ) = 1 ) THEN      
        SIGNAL SQLSTATE '85101' ('At least one policy is required.');
    END IF;  
END

When we try to delete the last policy an exception is thrown.

Other constraints such as UNIQUE or FOREIGN KEY could also be thought of as ASSERTIONS of the state of the universe.

From a philosophical point an "ASSERTION/CONSTRAINT" in a DBMS world is a stronger "rule" than an ASSERTION in a procedural/oo world. Whereas the latter verifies the state for a particular action. Example from:

https://wiki.python.org/moin/UsingAssertionsEffectively

class MyDB:
[...]
    def by_name(self, name):
        id = self._name2id_map[name]
        assert self._id2name_map[id] == name
        return id

The assertion alone not sufficient to protect MyDB. In addition we also need OO-concepts such as encapsulation, meaning that we must ensure that all possible ways to change the state of MyDB is protected by similar ASSERTIONS. A CONSTRAINT on the other hand guarantees the state no matter what action we take.

ASSERTIONS in RDBM:S and OO both have there merits, but are slightly different concepts and there is not a 1-1 mapping between the two.

StackExchange Database Administrators Q#171709, answer score: 2