Tutorial de SQL de clave principal: cómo definir una clave principal en una base de datos

Toda gran historia comienza con una crisis de identidad. Luke, el gran Maestro Jedi, comienza inseguro: "¿Quién soy yo?" - ¿Y cómo podría ser alguien importante? Se necesita a Yoda, el que tiene la Fuerza, para enseñarle cómo aprovechar sus poderes.

Hoy, déjame ser tu Yoda.

Comenzaremos con cómo elegir una clave principal, combatir una crisis de identidad y luego terminaremos con ejemplos de código para crear una clave principal en una base de datos.

Cómo elegir una clave principal

Puede pensar que Luke es el único con una crisis de identidad, pero eso no es cierto. Al crear una base de datos, todo está en crisis de identidad. Y es exactamente por eso que necesitamos claves primarias: resuelven la crisis. Nos dicen cómo encontrar a todos.

Imagina que eres el gobierno y quieres identificar a cada uno de tus ciudadanos digitalmente. Entonces, creas esta base de datos con todo sobre ellos:

First Name Last Name Passport Number

Eliges el número de pasaporte como clave principal: la identidad para todos. Imaginas que es todo lo que necesitas, ya que el pasaporte tiene la dirección y todo lo demás. Usted sabe que los números de pasaporte son únicos, por lo que se siente bien e implementa este sistema.

Luego, unos años después, descubres una fea verdad: todo el país se enfrenta a una crisis de identidad.

Cada vez que el pasaporte de alguien caduca, recibe uno nuevo. Su identidad cambia. Otros sistemas siguen usando los antiguos números de pasaporte, por lo que ahora apuntan a personas fantasmas.

La singularidad no es suficiente. El valor no debe cambiar durante la vida útil de la fila.

Y luego, descubres que hay algunas personas que ni siquiera tienen pasaportes. No puede ingresarlos en su sistema, ya que las claves primarias no pueden serlo NULL. ¿Cómo puedes identificar a alguien con una NULLllave?

Cada fila debe tener un identificador. NULL no permitidos.

La siguiente iteración significa encontrar un identificador que no cambie con el tiempo y uno que todos tengan. En India, esta resulta ser la Tarjeta Adhaar. En EE. UU., El número de seguro social.

Si está creando una base de datos, conviértalas en sus claves principales.

A veces, no tienes ninguna de esas claves. Considere un país que aún no tiene un número de seguro social y desea crear un registro digital de cada ciudadano. Podrían crear un nuevo SSN, o simplemente podrían aprovechar el poder de las bases de datos y usar una clave sustituta.

Una clave sustituta no tiene equivalente en el mundo real. Es solo un número dentro de una base de datos. Entonces, tienes esta tabla en el nuevo país:

userID First Name Last Name Passport Number

Los números de pasaporte son únicos. Siempre que desee obtener el identificador de un usuario, puede obtenerlo a través del Número de pasaporte.

El ID de usuario nunca cambia. El número de pasaporte puede cambiar, pero siempre es único, por lo que siempre obtiene el usuario correcto. El ID de usuario es un sustituto de un Número de Seguro Social que no existe en este país.

Dato curioso: el número de pasaporte aquí también es una clave de candidato. Podría haber sido la clave principal, si nunca hubiera cambiado. Ésta es una distinción de lógica empresarial.

La conclusión principal es la siguiente: siempre que elija una clave principal, piense en una crisis de identidad . ¿Es posible que alguien cambie su identificador en el futuro? ¿Podemos entrar en un estado en el que varias personas tengan el mismo identificador?

Utilizo a las personas como ejemplo, porque aclara la identidad: sabemos que se supone que cada persona tiene una identidad. Transfiera este pensamiento a sus bases de datos. Todo tiene una identidad, que es exactamente la razón por la que necesita claves primarias.

Nota: A veces, es posible y deseable utilizar varias columnas juntas como clave principal. Esta es una clave compuesta.

Ahora intentemos definir claves primarias con ejemplos de código real. Hay dos cosas que hacer aquí: primero, identificará la clave principal. Luego, aprenderá la sintaxis para definirlo en una base de datos.

Un ejemplo del mundo real

Digamos que tiene una empresa de envío, muy parecida a Flexport. Tiene paquetes que necesitan ir de un lugar a otro y barcos que los transportan. Además, tiene clientes que solicitan estos paquetes.

Calcula que necesitará una mesa para los clientes, una para los paquetes y otra para el transporte, que muestre qué paquete se encuentra en ese momento.

Piense qué columnas necesitará y cuál debería ser la clave principal. Si fuera ingeniero en Flexport, esta es una pregunta real que tendría que resolver. No se da nada, todo se descubre en el mundo real.

Dada esta información, diseñaría estas tablas así:

Customers: first_name, last_name, email, address (for deliveries to their location) Packages: weight, content Transportation: , Port, time

Nos faltan las claves primarias. Piense en ellos antes de seguir leyendo.

Para el paquete, elegiré un PackageID sustituto . Podría haber intentado enumerar todos los atributos del paquete: peso, volumen, densidad, edad. Identificarían de forma única el paquete, pero esto es muy difícil de hacer en la práctica. A las personas no les importa esto, solo les importa que el paquete llegue de un lugar a otro.

Por lo tanto, tiene sentido crear un número aleatorio y usarlo como ID. Esta es exactamente la razón por la que ve que FedEx, UPS y todos los servicios de entrega utilizan códigos de barras e identificaciones. Estas son claves sustitutas generadas para rastrear paquetes.

Para el cliente, elegiré un CustomerID sustituto . Aquí, nuevamente, tuve la opción de elegir, digamos, el número de seguro social de mis clientes. Pero los clientes no quieren compartir esto conmigo solo para que pueda enviarles algo. Por lo tanto, generamos una clave internamente, no le contamos a nuestros clientes sobre esta clave y continuamos llamándolos CustomerNo. 345681.

Historia divertida: conozco algunas empresas en las que expusieron este CustomerNo, y los clientes insistieron en que obtuvieran el No. 1. Fue bastante divertido: los ingenieros en realidad tuvieron que cambiar su código de front-end a: if (cust == 345681) print(1);

Al Transporte, voy a elegir un compuesto vez PackageID + puerto +. Esto es un poco más interesante. También podría haber creado un sustituto aquí, y funcionaría igual de bien.

Pero aquí radica la magia de la indexación. Las claves primarias obtienen un índice automáticamente, lo que significa que la búsqueda es mucho más eficiente que las claves primarias.

Cuando busque en esta base de datos, la mayoría de las consultas serán del tipo "¿dónde está este paquete?". En otras palabras, dado este PackageID, dígame el puerto y la hora en que se encuentra en este momento. Necesitaría un índice adicional sobre PackageID si no lo tengo como parte de mi clave principal.

¿Suena bien esto? Paso final, definamos estas 3 tablas en SQL. La sintaxis varía ligeramente con la base de datos que está utilizando.

Definición de claves primarias en MySQL

CREATE TABLE customers ( customerID INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, last_name VARCHAR(30) NOT NULL, first_name VARCHAR(25) NOT NULL, email VARCHAR(50) NOT NULL, address VARCHAR(300) );

CREATE TABLE packages ( packageID INT(15) NOT NULL AUTO_INCREMENT, weight DECIMAL (10, 2) NOT NULL, content VARCHAR(50), CONSTRAINT packages_pk PRIMARY KEY (packageID) # An alternative way to above, # when you want to name the constraint as well. );

CREATE TABLE transportation ( package INT(15) NOT NULL, port INT(15) NOT NULL, time DATE NOT NULL, PRIMARY KEY (package, port, time), FOREIGN KEY package REFERENCES packages(packageID) ON DELETE RESTRICT # It's good practice to define what should happen on deletion. In this case, I don't want things to get deleted. );

Definición de claves primarias en PostgreSQL

CREATE TABLE customers ( customerID SERIAL NOT NULL PRIMARY KEY, # In PostgreSQL SERIAL is same as AUTO_INCREMENT - it adds 1 to every new row. last_name VARCHAR(30) NOT NULL, first_name VARCHAR(25) NOT NULL, address TEXT, email VARCHAR(50) NOT NULL );

CREATE TABLE packages ( packageID SERIAL NOT NULL, weight NUMERIC NOT NULL, content TEXT, CONSTRAINT packages_pk PRIMARY KEY (packageID) # In PostgreSQL, this alternative way works too. );

CREATE TABLE transportation ( package INTEGER NOT NULL, port INT(15) NOT NULL, time DATE NOT NULL, PRIMARY KEY (package, port, time), FOREIGN KEY package REFERENCES packages(packageID) ON DELETE RESTRICT # It's good practice to define what should happen on deletion. In this case, I don't want things to get deleted. );

No es muy diferente, ¿verdad? Una vez que aprenda los conceptos básicos, puede aplicarlos a casi cualquier base de datos con solo un vistazo rápido a la documentación. ¡La clave es saber qué buscar!

Buena suerte, joven padawan.

Enjoyed this? You might also like Things I Learned From a Senior Software Engineer