Functional Dependencies and Primary Keys

Question

Question

Does a Primary Key functionally determine every other attribute in the table?

My thought

Surely it has to doesn't it? Isn't that the point of primary keys?

Re "the point of primary keys", see my answer & its link. – philipxy Feb 21 '16 at 05:27 — philipxy, Feb 21 '16 at 05:27

score 5 · Accepted Answer · answered Feb 15 '16 at 15:59

5

In a table that is at least in 1st normal form, the primary key determines every attribute in the table. As you say, that is the point of primary keys (and candidate keys in general).

answered Feb 15 '16 at 15:59

reaanb

9,806
2
23
37

1

@SwaroopJoshi Normal forms describe relations, and as philipxy explained, primary keys play no role in relational theory. To clarify my point, in a table that has a primary key and represents a relation (which is equivalent to being in at least 1NF), that primary key will necessarily functionally determine every attribute in the table. In a table that does not represent a relation, we can't necessarily make such guarantees. As other respondents pointed out, in any relation, all superkeys will determine all attributes of the relation. – reaanb Apr 27 '20 at 16:40

score 5 · Answer 2 · answered Feb 15 '16 at 20:50

5

There is by definition a functional dependency between all superkeys (not just the primary key) of a relation and all attributes in that relation (not just non-key attributes).

answered Feb 15 '16 at 20:50

nvogel

24,981
1
44
82

score 5 · Answer 3 · edited May 23 '17 at 11:59

A table's superkeys (UNIQUE NOT NULL sets of columns) are the column sets that "functionally determine every other attribute in the table". A table's candidate keys (superkeys containing no smaller superkeys) are important to normalization. A primary key is just a distinguished candidate key.

Why distinguish one?

Primary keys play no role in relational theory. The main practical role is for consistency in identifying rows/entities/associations by foreign keys in other tables.
Codd (contrary to theory) allowed NULL in candidate key columns. (As does SQL.) From his "Understanding Relations" articles:

A basic integrity principle associated with candidate keys is that, for every base relation, at least one of the candidate keys is prohibited from taking on null values.

Normally, it will not be necessary to prohibit null values in more than one candidate key -- hence, the common practice of designating precisely one such key as the primary key: i.e., the only candidate key for which null values are prohibited.
(DBMSs, CASE tools and ORMs often use primary keys for defaults associated with candidate keys, often physical/implementation ones. But this begs the question.)

So superkeys play the identification role, candidate keys are the special superkeys, and primary keys aren't particularly special candidate keys.

Functional Dependencies and Primary Keys

3 Answers3