How do I write a SQL statement that proves the candidate key ACD holds given a relation with attributes ABCD and the functional dependency A → B ? I know there's something similar here: SQL statement to prove that A->B in a R(ABCD), but can't figure out how to write the query for this constraint.
\i tmp.sql
create table abcd(
a integer not null
,b integer not null
,c integer not null
,d integer not null
-- , PRIMARY KEY (a,b,c,d)
);
INSERT INTO abcd(a,b,c,d)
select (s/4)%4, (s/4)%2,(s/2)%2,s%2
from generate_series(0,15) s
;
select *from abcd;
ALTER TABLE abcd ADD UNIQUE (a,b,c,d); --succeeds
ALTER TABLE abcd ADD UNIQUE (a,c,d); --succeeds
ALTER TABLE abcd ADD UNIQUE (b,c,d); --fails
Results:
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 16
a | b | c | d
---+---+---+---
0 | 0 | 0 | 0
0 | 0 | 0 | 1
0 | 0 | 1 | 0
0 | 0 | 1 | 1
1 | 1 | 0 | 0
1 | 1 | 0 | 1
1 | 1 | 1 | 0
1 | 1 | 1 | 1
2 | 0 | 0 | 0
2 | 0 | 0 | 1
2 | 0 | 1 | 0
2 | 0 | 1 | 1
3 | 1 | 0 | 0
3 | 1 | 0 | 1
3 | 1 | 1 | 0
3 | 1 | 1 | 1
(16 rows)
ALTER TABLE
ALTER TABLE
ERROR: could not create unique index "abcd_b_c_d_key"
DETAIL: Key (b, c, d)=(0, 0, 0) is duplicated.
B is functionally dependent on A here, but multiple As can point to the same B value.
BTW: IMO it is impossible to prove something in SQL (it depends in the current data in the table(s)), but it may be possible to reject it. (by composing an example)