HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to express this constraint in a database schema?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thisexpresshowdatabaseconstraintschema

Problem

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?

Solution

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)
);

Code Snippets

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)
);

Context

StackExchange Database Administrators Q#9155, answer score: 4

Revisions (0)

No revisions yet.