How to express this constraint in a database schema

constraintrelational-theory

I have the following functional dependencies which are in BCNF:

a,b -> c
a -> d
b -> d

With the additional constraint, that no a and b should be combined with a c, where a and b have different ds.

Example:

a | d   b | d   a | b | c
-----   -----   ---------
1 | 3   5 | 3   1 | 5 | 6
2 | 4           2 | 5 | 7

The first row in a,b,c is allowed (1->3,5->3), but the second row is forbidden, since (2->4,5->3) 4 != 3.

This additional constraint can have two effects on my data. For each a,b,c there are two redundant ways of determining the d. There can be data which violates the constraint.
How can my schema reflect this additional constraint?

Best Answer

In a nutshell, create an ASSERTION to ensure that at no time can the business rule be violated e.g. Full Standard SQL-92 syntax:

CREATE TABLE T1
(
 a INTEGER NOT NULL, 
 d INTEGER NOT NULL, 
 UNIQUE (a, d)
);

CREATE TABLE T2
(
 b INTEGER NOT NULL, 
 d INTEGER NOT NULL, 
 UNIQUE (b, d)
);

CREATE TABLE T3
(
 a INTEGER NOT NULL,
 b INTEGER NOT NULL, 
 c INTEGER NOT NULL, 
 UNIQUE (a, b, c)
);

CREATE ASSERTION no_a_and_b_should_be_combined_with_a_c_where_a_and_b_have_different_ds
   CHECK (
          NOT EXISTS (
                      SELECT *
                        FROM T3
                       WHERE NOT EXISTS (
                                         SELECT T1.d
                                           FROM T1
                                          WHERE T1.a = T3.a 
                                         INTERSECT        
                                         SELECT T2.d
                                           FROM T2
                                          WHERE T3.b = T3.b 
                                        )
                     )
         );

The bad news is that no commercial (or otherwise?) SQL product supports CREATE ASSERTION.

Most industrial-strength SQL products support triggers: one could implement the above in a trigger on each applicable table. MS Access is the only commercial product I know of that supports subqueries in CHECK constraints but I don't consider it to be industrial-strength. There are further workarounds e.g. forcing users to update tables only via stored procedures that can be shown to never leave the database in an illegal state.

Related Question