O'Reilly logo

SQL and Relational Theory, 2nd Edition by C.J. Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

MORE ON CANDIDATE KEYS

I explained the basic idea of candidate keys in Chapter 1, but now I want to make the concept more precise. Here first is a definition:

Definition: Let K be a subset of the heading of relvar R. Then K is a candidate key (or just key for short) for R if and only if it possesses both of the following properties:

  1. Uniqueness: No valid value for R contains two distinct tuples with the same value for K.

  2. Irreducibility: No proper subset of K has the uniqueness property.

If K consists of n attributes, then n is the degree of K.

Now, the uniqueness property is self-explanatory, but I need to say a little more about the irreducibility property. Consider relvar S and the set of attributes—let’s call it SC—{SNO,CITY}, which is certainly a subset of the heading of S that has the uniqueness property (no relation that’s a valid value for relvar S ever has two distinct tuples with the same SC value). But it doesn’t have the irreducibility property, because we could discard the CITY attribute and what’s left, the singleton set {SNO}, would still have the uniqueness property. So we don’t regard SC as a key, because it’s “too big.” By contrast, {SNO} is irreducible, and it’s a key.

Why do we want keys to be irreducible? One important reason is that if we were to specify a “key” that wasn’t irreducible, the DBMS wouldn’t be able to enforce the proper uniqueness constraint. For example, suppose we told the DBMS (lying!) that SC was a key for relvar S. Then the DBMS couldn’t enforce ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required