Insuring unique rows

Hace un par de días alguien me comento que no eran importantes las llaves en las tablas relacionales, y casi me voy como condorito(tm) para atrás!.

Después de argumentar, desde varios puntos de vista en donde la posición de mi interlocutor era que no eran necesarias y que se podía hacer lo mismo usando varias columnas para identificar de forma única una tupla en la tabla, me comento que lo único que vale la pena de las llaves era que quizás era mas rápida así la tabla.

Y pues hoy me di a la tarea de investigar a detalle al respecto para la próxima vez que nos veamos bueno solo recordar el incidente y sonreír :)

Ignorancia o divino tesoro.

He aquí una trascripción de un estudio hecho por:

Tom Jewett
Department of Computer Engineering and Computer Science, Emeritus
California State University, Long Beach
www.cecs.csulb.edu/~jewett/

Insuring unique rows

Since each row in a table must be unique, no two rows can have exactly the same values for every one of their attributes. Therefore, there must be some set of attributes (it might be the set of all attributes) in each relation whose values, taken together, guarantee uniqueness of each row. Any set of attributes that can do this is called a super key (SK). Super keys are a property of the relation (table), filled in with any reasonable set of real-world data, even though we show them in the relation scheme drawing for convenience.

The database designer picks one of the possible super key attribute sets to serve as the primary key (PK) of the relation. (Notice that the PK is an SK, but not all SKs are PKs!) The PK is sometimes also called a unique identifier for each row of the table.

This is not an arbitrary choice—we’ll discuss it in detail on a later page. For our customers table, we’ll pick the customer’s first name, last name, and phone number. We are likely to have at least two customers with the same first and last name, but it is very unlikely that they will both have the same phone number.

In SQL, we specify the primary key with a constraint on the table that lists the PK attributes. We also give the constraint a name that is easy for us to remember later (like “customers_pk” here).

ALTER TABLE customers
ADD CONSTRAINT customers_pk
PRIMARY KEY (cfirstname, clastname, cphone);

We also can specify the primary key when we create the table. The NOT NULL constraint prevents the PK attributes from being left empty, since NULL is a special constant in database systems that means “this field doesn’t have any value assigned to it.” It’s not the same as a zero length string or the number zero.

CREATE TABLE customers (
cfirstname VARCHAR(20) NOT NULL,
clastname VARCHAR(20) NOT NULL,
cphone VARCHAR(20) NOT NULL,
cstreet VARCHAR(50),
czipcode VARCHAR(5)),
CONSTRAINT customers_pk
PRIMARY KEY (cfirstname, clastname, cphone);

Aqui pueden verlo el estudio completo.
http://tinyurl.com/49lh2b