How to express this constraint in a database schema?

Question

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?

onedaywhen · Answer 1 · 2011-12-15T08:53:19.117

4

In a nutshell, introduce d into the third table to enable vanilla foreign key constraints e.g. Transitional 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, 
 d INTEGER NOT NULL, 
 UNIQUE (a, b, c),
 FOREIGN KEY (a, d) REFERENCES T1 (a, d), 
 FOREIGN KEY (b, d) REFERENCES T2 (b, d)
);

edited Dec 15 '11 at 08:53

answered Dec 14 '11 at 17:51

onedaywhen

2,612
1
19
21

This gives me unwanted redundency. This schema would not be in BCNF anymore. – johannes Dec 15 '11 at 00:12
@johannes - It's a small price to pay for enforcing your constraints in an efficient and simple manner. In practical terms, the only down-side to this approach is the extra cost of storage for the repeated d column in T3, which is a negligible cost honestly. BCNF is not the end-all and be-all of database design, especially when you are implementing and not just designing in the abstract. – Nick Chammas Dec 15 '11 at 04:04

score 3 · Answer 2 · answered Dec 14 '11 at 23:19

" So is your answer "it's not possible"? "

Many things are possible. In your very particular case, it looks to me like enforcing your 'additional' constraint can be achieved by keeping the database single-table (4-column). This will guarantee you that any combined a,b will always correspond to the same d (because there can only ever be one single d). The price you pay is that there is no longer a "natural" way (i.e. one that is an immediate consequence of the very logical structure of the database itself) that will enforce your a->d and b->d FDs "automatically".

It is a well-known fact that the classical process of normalization-through-decomposition, sometimes requires that certain FDs be reinstated as a database constraint, because in the decomposed design the rule can no longer be stated as an FD. Your particular case seems to be one such, where you have the choice between a design that "automatically" enforces a->d and b->d, but where you have to do extra effort to enforce your additional constraint, or a design that "automatically" enforces your additional constraint, but where you have to do extra work to enforce [the constraints corresponding to] your a->d and b->d FDs.

Having ALL the constraints you mention enforced merely by database structure, is possible, in your particular case, using onedaywhen's solution. However, (a) that is only a solution for particular cases such as your example, (b) the price you pay is increased redundancy, and therefore additional complexity in updating your database (and certain updates might be impossible to achieve !!!), and (c) it still remains a fact that not all conceivable database constraints are expressible as an FD.

(Sorry for posting a second answer. My Stackoverflow login doesn't allow me to comment here.)

score 3 · Accepted Answer · answered Dec 15 '11 at 08:52

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.

The check constraint can also call a UDF that execute that query. However, this is not a practical solution. As you noted, most RDBMS platforms either don't support this solution, or support it with many caveats. — Nick Chammas, Jan 10 '12 at 17:57

How to express this constraint in a database schema?

3 Answers3