SQL: Enforce cardinality with assertions

Question

To explain the diagram.

Two entities: E1 & E2

Cardinality:

-from E1 to E2 (through relation R): (6,50)

-from E2 to E1 (through relation R): (3,5)

 ______               ^              ______
|  E1  |___(6,50)____/R\____(3,5)___|  E2  |
|______|             \ /            |______|
                      v

I'm supposed to enforce cardinality but the book doesn't dive too much into ASSERTION details. Would this be a correct way to enforce said cardinality?

CREATE ASSERTION assert
CHECK ( (SELECT COUNT(E1.ID) FROM E1) >= 6  AND 
        (SELECT COUNT(E1.ID) FROM E1) <= 60 AND 
        (SELECT COUNT(E2.ID) FROM E2) >= 3  AND 
        (SELECT COUNT(E2.ID) FROM E2) <= 5 );

Can't see how this can be enforced, since you cannot simultaneously insert into 2 tables — RichardTheKiwi, Oct 11 '12 at 17:55
@RichardTheKiwi: This is one of the common uses of deferred constraints and transactions. — beldaz, Jul 24 '13 at 05:04

ypercubeᵀᴹ · Accepted Answer · 2012-10-11T18:52:53.093

4

I don't know of any DBMS that has implemented assertions. The syntax can be checked at Mimer online SQL-92 syntax validator:

CREATE ASSERTION assertion_R
  CHECK ( NOT EXISTS
            ( SELECT * 
              FROM E1 
                LEFT JOIN R
                  ON R.e1_id = E1.id
              GROUP BY E1.id
              HAVING COUNT(R.e1_id) NOT BETWEEN 6 AND 60
            )
       AND NOT EXISTS
            ( SELECT * 
              FROM E2 
                LEFT JOIN R
                  ON R.e2_id = E2.id
              GROUP BY E2.id
              HAVING COUNT(R.e2_id) NOT BETWEEN 3 AND 5
            ) 
        ) ;

edited Oct 11 '12 at 18:52

answered Oct 11 '12 at 18:16

ypercubeᵀᴹ

113,259
19
174
235

_"I don't know of any DBMS that has implemented assertions"_ - SQL Server's indexed-views cover a good number (but not all) of constraint scenarios - just something to consider. – Dai Oct 08 '22 at 07:33
@Dai yes, some scenarios can be done with indexed views. – ypercubeᵀᴹ Oct 08 '22 at 10:14

SQL: Enforce cardinality with assertions

1 Answers1