snippetMinor
How to express this constraint in a database schema?
Viewed 0 times
thisexpresshowdatabaseconstraintschema
Problem
I have the following functional dependencies which are in BCNF:
With the additional constraint, that no
Example:
The first row in
This additional constraint can have two effects on my data. For each
How can my schema reflect this additional constraint?
a,b -> c
a -> d
b -> dWith 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 | 7The 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.