A primary key is an attribute or set of attributes that uniquely identifies a row in a table. But a primary key has not only to be unique, but also minimal. Why is that necessary?
+1
A:
Primary keys should be minimal since they only have to be large enough to uniquely identify the row. Anything else is a waste in terms on index space used.
In other words, if I have a unique column username along with another unique column student_id, a primary key built from both of those is a waste. The normal way to handle that would be to use one as the primary key and then have a unique constraint/index on the other.
paxdiablo
2010-08-16 07:03:20
+2
A:
The reason why it's important to identify minimal keys is to ensure that there is no redundancy in dependencies on those keys. Redundancy can cause anomalies and incorrect results.
Minimality is essentially a semantic matter rather than purely a structural feature so it isn't necessarily required by the database implementation. For instance SQL allows you to create a "PRIMARY KEY" on any superkey, which may not be an irreducible superkey.
Minimality has nothing to do with storage size because minimal means irreducible, it does not mean smallest.
dportas
2010-08-16 10:16:54
"instance SQL allows you to create a PRIMARY KEY on any superkey, which may not be an irreducible superkey." As a matter of fact, SQL *cannot* prevent you from doing this, because the minimality is a theoretical property, whose definition refers to all possible values. A DBMS cannot know what you intend to store in a table, so it cannot possible tell you if your unique key is minimal or not - it can only tell you if it is not unique.
sleske
2010-08-16 10:29:37
sleske: the DBMS could in principle tell when a superkey is not minimal if there are additional constraints involved. For example, in the following case the supposed "primary" key (a,b) is clearly NOT minimal: CREATE TABLE t1 (a INT NOT NULL UNIQUE, b INT NOT NULL, PRIMARY KEY (a,b));
dportas
2010-08-16 10:38:16