Cómo usar las claves primarias en SQL

Introducción

Una de las características valiosas de las bases de datos relacionales es el formato de los datos en una estructura bien definida. Esta estructura se logra mediante el uso de tablas con columnas fijas, el uso de tipos de datos bien definidos y la garantía de que cada fila tenga el mismo formato. Al almacenar datos como filas en tablas, es igualmente importante poder encontrarlos y hacer referencia a ellos sin ambigüedades. En el Lenguaje de Consulta Estructurado (SQL), esto se puede lograr mediante claves primarias, que actúan como identificadores de filas individuales en las tablas de una base de datos relacional.

En este tutorial, aprenderá sobre las claves primarias y a usar diferentes tipos para identificar filas únicas en las tablas de bases de datos. Con algunos conjuntos de datos de ejemplo, creará claves primarias en columnas individuales, columnas múltiples y claves secuenciales autoincrementales.

Requisitos previos

Para seguir esta guía, necesita un ordenador con un sistema de gestión de bases de datos relacionales (SGBDR) basado en SQL. Las instrucciones y los ejemplos de esta guía se han validado en el siguiente entorno:

• Un servidor con Ubuntu 20.04, con un usuario sin privilegios de administrador pero con privilegios administrativos y un cortafuegos configurado con UFW.
• MySQL está instalado y protegido en el servidor.
• Introducción básica a la ejecución de consultas SELECT para recuperar datos de la base de datos.

Nota: Tenga en cuenta que muchos RDBMS utilizan su propia implementación de SQL. Si bien los comandos mencionados en este tutorial funcionan en la mayoría de los RDBMS y las claves primarias forman parte del estándar SQL, algunas funciones son específicas de cada base de datos y, por lo tanto, la sintaxis o el resultado exactos pueden variar si se prueban en un sistema distinto de MySQL.

También necesitará una base de datos vacía para crear las tablas usando claves primarias. Le recomendamos consultar la sección "Conectarse a MySQL y configurar una base de datos de ejemplo" a continuación para obtener más información sobre cómo conectarse al servidor MySQL y crear la base de datos de prueba utilizada en los ejemplos de esta guía.

Conexión a MySQL y configuración de una base de datos de ejemplo

En esta sección, se conectará al servidor MySQL y creará una instancia de base de datos para poder utilizar los ejemplos de esta guía.

Si su sistema de base de datos SQL se ejecuta en un servidor remoto, conéctese a su servidor mediante SSH desde su máquina local:

ssh sammy@your_server_ip

A continuación, abre la consola del servidor MySQL y sustituye Sami por tu nombre de usuario de MySQL:

mysql -u sammy -p

Crea una base de datos llamada primary_keys:

CREATE DATABASE primary_keys;

Si la base de datos se creó correctamente, recibirá el siguiente resultado:

Output
Query OK, 1 row affected (0.01 sec)

Para seleccionar la base de datos primary_keys, ejecute la siguiente declaración USE:

USE primary_keys;

Obtendrás el siguiente resultado:

Output
Database changed

Una vez seleccionada una base de datos, puede crear tablas de ejemplo en ella. Ya está listo para seguir el resto de la guía y empezar a trabajar con claves primarias en MySQL.

Introducción a las tonalidades mayores

Los datos de una base de datos relacional se almacenan en tablas con una estructura específica y uniforme de filas individuales. La definición de la tabla describe las columnas y los tipos de datos que se pueden almacenar en ellas. Esto por sí solo es suficiente para almacenar información en la base de datos y encontrarla mediante diversos criterios de filtro mediante la cláusula WHERE. Sin embargo, esta estructura no garantiza que cada fila se encuentre sin ambigüedades.

Imagine una base de datos con todos los vehículos registrados que pueden circular por la vía pública. Esta base de datos contiene información como la marca, el modelo, el año de fabricación y el color de la pintura. Sin embargo, si busca un Chevrolet Camaro rojo fabricado en 2007, podría encontrar más de uno. Al fin y al cabo, los fabricantes venden los mismos coches a múltiples clientes. Por eso, los coches registrados tienen matrículas que los identifican. Si busca un coche con la matrícula OFP857, puede estar seguro de que este criterio solo encontrará un coche. Esto se debe a que, por ley, las matrículas identifican de forma única a los coches registrados. En una base de datos relacional, este tipo de datos se denomina clave principal.

Las claves primarias son identificadores únicos que se encuentran en una columna o conjunto de columnas y que permiten identificar inequívocamente cada fila de una tabla de base de datos. Varias reglas reflejan las características técnicas de las claves primarias:

• Una clave principal debe usar valores únicos. Si la clave principal consta de más de una columna, la combinación de valores en estas columnas debe ser única en toda la tabla. Dado que la clave identifica de forma única cada fila, no puede aparecer más de una vez.
• Una clave principal no debe contener valores NULL.
• Cada tabla de base de datos solo puede utilizar una clave principal.

El motor de base de datos aplica estas reglas, por lo que si se define una clave principal en una tabla, puede confiar en que esos atributos son correctos.

Además de estas características técnicas, también debe considerar el contenido de los datos para decidir qué tipo de datos es adecuado para convertirse en una clave principal. Las claves naturales son identificadores que ya existen en el conjunto de datos, mientras que las claves sustitutas son identificadores artificiales.

Algunas estructuras de datos tienen claves primarias que aparecen de forma natural en el conjunto de datos, como los números de matrícula en una base de datos de vehículos o los números de la Seguridad Social en un registro de ciudadanos estadounidenses. En ocasiones, estos identificadores no son un valor único, sino un par o una combinación de varios valores. Por ejemplo, en un directorio local de viviendas de una ciudad, el nombre o el número de una calle por sí solos no pueden identificar una casa de forma única. Puede haber varias casas en una calle, y el mismo número puede aparecer en varias de ellas. Sin embargo, un par de nombres y números de calles puede considerarse un identificador único de vivienda. Estos identificadores de origen natural se denominan claves naturales.

Sin embargo, a menudo los datos no pueden identificarse de forma única mediante los valores de una sola columna o un pequeño subconjunto de columnas. En ese caso, se crean claves primarias artificiales, por ejemplo, mediante una secuencia de números o identificadores generados aleatoriamente, como los UUID. Estas claves se denominan claves sustitutas.

En las siguientes secciones, creará claves naturales basadas en una o más columnas y creará claves alternativas en tablas donde una clave natural no es una opción.

Crear una clave principal en una columna

En muchos casos, un conjunto de datos incluye naturalmente una columna que permite identificar de forma única las filas de una tabla. En estos casos, se puede crear una clave natural para describir los datos. Siguiendo con el ejemplo anterior de la base de datos de vehículos matriculados, imagine una tabla con la siguiente estructura:

Sample table
+---------------+-----------+------------+-------+------+
| license_plate | brand | model | color | year |
+---------------+-----------+------------+-------+------+
| ABC123 | Ford | Mustang | Red | 2018 |
| CES214 | Ford | Mustang | Red | 2018 |
| DEF456 | Chevrolet | Camaro | Blue | 2016 |
| GHI789 | Dodge | Challenger | Black | 2014 |
+---------------+-----------+------------+-------+------+

Tanto la primera como la segunda fila describen un Ford Mustang rojo de 2018. No se puede identificar el coche de forma única mediante la marca y el modelo. La matrícula es diferente en ambos casos y proporciona un buen identificador único para cada fila de la tabla. Dado que el número de matrícula ya forma parte de los datos, usarlo como clave principal crea una clave natural. Si se crea la tabla sin usar una clave principal en la columna "Matrícula", se corre el riesgo de que aparezca una página duplicada o vacía en el conjunto de datos en algún momento.

A continuación, crea una tabla similar a la anterior con la columna License_plate como clave principal y las siguientes columnas:

• Matrícula: Esta columna contiene el número de matrícula, representado por el tipo de datos varchar.
• Marca: Esta columna representa la marca del automóvil, expresada mediante el tipo de dato varchar con un máximo de 50 caracteres.
• Modelo: Esta columna contiene el modelo del automóvil, expresado mediante el tipo de datos varchar con un máximo de 50 caracteres.
• Color: esta columna contiene el color, expresado mediante el tipo de datos varchar con un máximo de 20 caracteres.
• año: Esta columna muestra el año de fabricación del automóvil, expresado utilizando el tipo de datos int para almacenar datos numéricos.

Para crear la tabla de máquinas, ejecute la siguiente declaración SQL:

CREATE TABLE cars (
license_plate varchar(8) PRIMARY KEY,
brand varchar(50),
model varchar(50),
color varchar(20),
year int
);... */

La instrucción PRIMARY KEY sigue la definición del tipo de dato License_plate. Al trabajar con claves primarias basadas en columnas individuales, puede usar la sintaxis simplificada para crear la clave y escribirla en la definición de la columna.

Si se imprime el siguiente resultado, la tabla se ha creado correctamente:

Output
Query OK, 0 rows affected (0.00 sec)

Después de eso, cargue la tabla con las filas de muestra proporcionadas en el ejemplo anterior ejecutando la siguiente operación INSERT INTO:

INSERT INTO cars VALUES
('ABC123', 'Ford', 'Mustang', 'Red', 2018),
('CES214', 'Ford', 'Mustang', 'Red', 2018),
('DEF456', 'Chevrolet', 'Camaro', 'Blue', 2016),
('GHI789', 'Dodge', 'Challenger', 'Black', 2014);

La base de datos responderá con un mensaje de éxito:

Output
Query OK, 4 rows affected (0.010 sec)
Records: 4 Duplicates: 0 Warnings: 0

Ahora puede verificar que la tabla recién creada contiene los datos y el formato esperados utilizando la declaración SELECT:

SELECT * FROM cars;

La salida muestra una tabla similar a la que aparece al principio de la sección:

Output
+---------------+-----------+------------+-------+------+
| license_plate | brand | model | color | year |
+---------------+-----------+------------+-------+------+
| ABC123 | Ford | Mustang | Red | 2018 |
| CES214 | Ford | Mustang | Red | 2018 |
| DEF456 | Chevrolet | Camaro | Blue | 2016 |
| GHI789 | Dodge | Challenger | Black | 2014 |
+---------------+-----------+------------+-------+------+

A continuación, puede comprobar si el motor de base de datos garantiza las reglas de la clave principal. Intente insertar un coche con matrícula duplicada ejecutando lo siguiente:

INSERT INTO cars VALUES ('DEF456', 'Jeep', 'Wrangler', 'Yellow', 2019);

MySQL responde con un mensaje de error que indica que la etiqueta DEF456 genera una entrada duplicada para la clave principal:

Output
ERROR 1062 (23000): Duplicate entry 'DEF456' for key 'cars.PRIMARY'

Nota: En esencia, las claves primarias se implementan con índices únicos y comparten muchas de las propiedades de los índices que se crean manualmente para otras columnas de una tabla. Es más, los índices de clave primaria también mejoran el rendimiento al consultar una tabla con la columna en la que se define el índice. Para obtener más información sobre el uso de índices para este propósito, consulte la guía "Cómo usar índices" de este tutorial.

Ahora puede estar seguro de que no se permiten matrículas duplicadas. A continuación, verifique si se puede importar un coche con matrícula en blanco:

INSERT INTO cars VALUES (NULL, 'Jeep', 'Wrangler', 'Yellow', 2019);

Esta vez la base de datos responderá con otro mensaje de error:

Output
ERROR 1048 (23000): Column 'license_plate' cannot be null

Con estas dos reglas aplicadas por la base de datos, puede estar seguro de que License_plate identifica de forma única cada fila de la tabla. Si consulta la tabla con cada matrícula, obtendrá una fila cada vez.

En la siguiente sección, aprenderá cómo utilizar claves principales con múltiples columnas.

Creación de una clave principal en varias columnas

Cuando una columna no es suficiente para identificar de forma única una fila en una tabla, puede crear claves principales que utilicen más de una columna.

Por ejemplo, imaginemos un registro de viviendas donde ni el nombre ni el número de la calle son suficientes para identificar cada casa:

Sample table
+-------------------+---------------+-------------------+------+
| street_name | street_number | house_owner | year |
+-------------------+---------------+-------------------+------+
| 5th Avenue | 100 | Bob Johnson | 2018 |
| Broadway | 1500 | Jane Smith | 2016 |
| Central Park West | 100 | John Doe | 2014 |
| Central Park West | 200 | Tom Thompson | 2015 |
| Lexington Avenue | 5001 | Samantha Davis | 2010 |
| Park Avenue | 7000 | Michael Rodriguez | 2012 |
+-------------------+---------------+-------------------+------+

El nombre de la calle Central Park West aparece más de una vez en la tabla, al igual que el número 100. Sin embargo, no se observan pares duplicados de nombre y número de calle. En este caso, aunque ninguna columna puede ser la clave principal, el par de esos dos valores puede utilizarse para identificar de forma única cada fila de la tabla.

A continuación, crea una tabla similar a la que se muestra arriba con las siguientes columnas:

• street_name: Esta columna representa el nombre de la calle donde se encuentra la casa, con un tipo de datos varchar limitado a 50 caracteres.
• street_number: Esta columna contiene el número de la calle de la casa, representado con el tipo de dato varchar. Esta columna puede almacenar hasta 5 caracteres. No utiliza el tipo de dato numérico int, ya que algunos números de calle pueden contener letras (p. ej., 200B).
• house_owner: esta columna contiene el nombre del propietario de la casa, representado por el tipo de datos varchar limitado a 50 caracteres.
• año: Esta columna representa el año en que se construyó la casa, representado con el tipo de datos int para almacenar valores numéricos.

Esta vez, la clave principal usa las columnas street_name y street_number en lugar de solo una. Para ello, ejecute la siguiente instrucción SQL:

CREATE TABLE houses (
street_name varchar(50),
street_number varchar(5),
house_owner varchar(50),
year int,
PRIMARY KEY(street_name, street_number)
);

Esta vez, a diferencia del ejemplo anterior, la instrucción PRIMARY KEY aparece debajo de las definiciones de columna. La instrucción PRIMARY KEY está entre paréntesis con dos nombres de columna: street_name y street_number. Esta sintaxis crea una clave principal en la tabla de casas, que se ubica en dos columnas.

Si se imprime el siguiente resultado, la tabla se ha creado correctamente:

Output
Query OK, 0 rows affected (0.00 sec)

Después de eso, cargue la tabla con las filas de muestra proporcionadas en el ejemplo anterior ejecutando la siguiente operación INSERT INTO:

INSERT INTO houses VALUES
('Central Park West', '100', 'John Doe', 2014),
('Broadway', '1500', 'Jane Smith', 2016),
('5th Avenue', '100', 'Bob Johnson', 2018),
('Lexington Avenue', '5001', 'Samantha Davis', 2010),
('Park Avenue', '7000', 'Michael Rodriguez', 2012),
('Central Park West', '200', 'Tom Thompson', 2015);

La base de datos responderá con un mensaje de éxito:

Output
Query OK, 6 rows affected (0.000 sec)
Records: 6 Duplicates: 0 Warnings: 0

Ahora puede verificar que la tabla recién creada contiene los datos y el formato esperados utilizando la declaración SELECT:

SELECT * FROM houses;

La salida muestra una tabla similar a la que aparece al principio de la sección:

Output
+-------------------+---------------+-------------------+------+
| street_name | street_number | house_owner | year |
+-------------------+---------------+-------------------+------+
| 5th Avenue | 100 | Bob Johnson | 2018 |
| Broadway | 1500 | Jane Smith | 2016 |
| Central Park West | 100 | John Doe | 2014 |
| Central Park West | 200 | Tom Thompson | 2015 |
| Lexington Avenue | 5001 | Samantha Davis | 2010 |
| Park Avenue | 7000 | Michael Rodriguez | 2012 |
+-------------------+---------------+-------------------+------+
6 rows in set (0.000 sec)

Ahora, verifiquemos si la base de datos permite filas que repiten nombres y números de calles, pero restringe la aparición de direcciones completas duplicadas en la tabla. Comencemos agregando otra casa en Park Street:

INSERT INTO houses VALUES ('Park Avenue', '8000', 'Emily Brown', 2011);

MySQL responde con un mensaje de éxito porque la dirección 8000 Park Avenue no aparecía previamente en la tabla:

Output
Query OK, 1 row affected (0.010 sec)

Un resultado similar ocurre cuando se agrega una casa en 8000 Main Street y se repite el número de calle:

INSERT INTO houses VALUES ('Main Street', '8000', 'David Jones', 2009);

Una vez más, esto inserta una nueva fila correctamente porque no se repite la dirección completa:

Output
Query OK, 1 row affected (0.010 sec)

Sin embargo, agregue otra casa en 100 5th Avenue utilizando la siguiente declaración INSERT:

INSERT INTO houses VALUES ('5th Avenue', '100', 'Josh Gordon', 2008);

La base de datos responde con un mensaje de error, informándole que hay una entrada duplicada para la clave principal para el par de valores 5th Avenue y 100:

Output
ERROR 1062 (23000): Duplicate entry '5th Avenue-100' for key 'houses.PRIMARY'

La base de datos aplica correctamente las reglas de clave principal con la clave definida en un par de columnas. Puede estar seguro de que la dirección completa, incluyendo el nombre y el número de la calle, no se duplicará en la tabla.

En esta sección, creó una clave natural con un par de columnas para identificar de forma única cada fila de la tabla de inicio. Sin embargo, las claves primarias no siempre se pueden extraer del conjunto de datos. En la siguiente sección, utilizará claves primarias artificiales que no provienen directamente de los datos.

Creación de una clave primaria secuencial

Hasta ahora, ha creado claves primarias únicas utilizando columnas del conjunto de datos de muestra. Sin embargo, en algunos casos, los datos se duplican inevitablemente, lo que impide que las columnas sean identificadores únicos fiables. En estos casos, puede crear claves primarias secuenciales utilizando los identificadores generados. Cuando sus datos requieren la creación de nuevos identificadores para identificar filas, las claves primarias creadas a partir de esos identificadores artificiales se denominan claves sustitutas.

Imaginen una lista de miembros de un club de lectura: una reunión informal a la que cualquiera puede unirse sin necesidad de mostrar una identificación oficial. Es probable que personas con nombres similares se unan al club en algún momento:

Sample table
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| John | Doe |
| Jane | Smith |
| Bob | Johnson |
| Samantha | Davis |
| Michael | Rodriguez |
| Tom | Thompson |
| Sara | Johnson |
| David | Jones |
| Jane | Smith |
| Bob | Johnson |
+------------+-----------+

Los nombres Bob Johnson y Jane Smith se repiten en la tabla. Se necesitaría un identificador adicional para identificar quién es quién, y no hay forma de identificar las filas de forma única. Si se mantuviera una lista de miembros del club de lectura en papel, se podrían usar identificadores auxiliares para distinguir a las personas con nombres similares dentro del grupo.

Se puede hacer algo similar en una base de datos relacional usando una columna adicional que contenga identificadores generados, no reales, cuyo único propósito es aislar de forma única todas las filas de la tabla. La llamaremos Member_id.

Sin embargo, crear dicho identificador cada vez que se quiere añadir un nuevo miembro del club de lectura a la base de datos resulta complicado. Para solucionar este problema, MySQL ofrece una función de autoincremento de columnas numéricas, donde la base de datos incrementa automáticamente el valor de la columna mediante una secuencia de enteros.

Creemos una tabla similar a la anterior. Agregaremos una columna de autoincremento (member_id) para guardar un número asignado automáticamente a cada miembro del club. Este número servirá como clave principal de la tabla:

Member_id: esta columna tiene un identificador numérico de incremento automático representado por el tipo de datos int.

first_name: Esta columna contiene el nombre de los miembros del club, representado por el tipo de datos varchar limitado a 50 caracteres.

last_name: Esta columna contiene el apellido de los miembros del club, que se muestra con un tipo de datos varchar limitado a 50 caracteres.

Para crear la tabla, ejecute la siguiente declaración SQL:

CREATE TABLE club_members (
member_id int AUTO_INCREMENT PRIMARY KEY,
first_name varchar(50),
last_name varchar(50)
);

Aunque la instrucción PRIMARY KEY aparece después de la definición del tipo de columna, al igual que una clave primaria de una sola columna, aparece un atributo adicional antes: AUTO_INCREMENT. Este atributo indica a MySQL que genere automáticamente valores para esa columna, si no se proporciona explícitamente, mediante una secuencia numérica creciente.

Nota: La propiedad AUTO_INCREMENT para las definiciones de columnas es específica de MySQL. Otras bases de datos suelen ofrecer métodos similares para generar claves secuenciales, pero la sintaxis varía según el motor. En caso de duda, recomendamos consultar la documentación oficial de su RDBMS.

Si se imprime el siguiente resultado, la tabla se ha creado correctamente:

Output
Query OK, 0 rows affected (0.00 sec)

Después de eso, cargue la tabla con las filas de muestra proporcionadas en el ejemplo anterior ejecutando la siguiente operación INSERT INTO:

INSERT INTO club_members (first_name, last_name) VALUES
('John', 'Doe'),
('Jane', 'Smith'),
('Bob', 'Johnson'),
('Samantha', 'Davis'),
('Michael', 'Rodriguez'),
('Tom', 'Thompson'),
('Sara', 'Johnson'),
('David', 'Jones'),
('Jane', 'Smith'),
('Bob', 'Johnson');

La declaración INSERT ahora incluye una lista de nombres de columnas (first_name y last_name), lo que garantiza que la base de datos sepa que la columna Member_id no se proporciona en el conjunto de datos, por lo que se debe tomar el valor predeterminado en su lugar.

La base de datos responderá con un mensaje de éxito:

Output
Query OK, 10 rows affected (0.002 sec)
Records: 10 Duplicates: 0 Warnings: 0

Utilice la instrucción SELECT para verificar los datos en la tabla recién creada:

SELECT * FROM club_members;

La salida muestra una tabla similar a la que aparece al principio de la sección:

Output
+-----------+------------+-----------+
| member_id | first_name | last_name |
+-----------+------------+-----------+
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Bob | Johnson |
| 4 | Samantha | Davis |
| 5 | Michael | Rodriguez |
| 6 | Tom | Thompson |
| 7 | Sara | Johnson |
| 8 | David | Jones |
| 9 | Jane | Smith |
| 10 | Bob | Johnson |
+-----------+------------+-----------+
10 rows in set (0.000 sec)

Sin embargo, esta vez, la columna Member_id aparece en el resultado y contiene una secuencia de números del 1 al 10. Con esta columna, las filas duplicadas de Jane Smith y Bob Johnson ya no son reconocibles porque cada nombre está asociado con un identificador único (Member_id).

Ahora, verifiquemos si la base de datos nos permite agregar otro Tom Thompson a la lista de miembros del club:

INSERT INTO club_members (first_name, last_name) VALUES ('Tom', 'Thompson');

MySQL responderá con un mensaje de éxito:

Output
Query OK, 1 row affected (0.009 sec)

Para comprobar qué ID numérico ha asignado la base de datos a la nueva entrada, ejecute la consulta SELECT nuevamente:

SELECT * FROM club_members;

Hay una fila más en la salida:

Output
+-----------+------------+-----------+
| member_id | first_name | last_name |
+-----------+------------+-----------+
| 1 | John | Doe |
| 2 | Jane | Smith |
| 3 | Bob | Johnson |
| 4 | Samantha | Davis |
| 5 | Michael | Rodriguez |
| 6 | Tom | Thompson |
| 7 | Sara | Johnson |
| 8 | David | Jones |
| 9 | Jane | Smith |
| 10 | Bob | Johnson |
| 11 | Tom | Thompson |
+-----------+------------+-----------+
11 rows in set (0.000 sec)

A una nueva fila se le asignó automáticamente el número 11 en la columna Member_id a través del atributo AUTO_INCREMENT de la base de datos.

Si los datos con los que está trabajando no tienen candidatos naturales para claves primarias y no desea crear identificadores inventados cada vez que agrega datos nuevos a la base de datos, puede confiar de manera segura en identificadores generados secuencialmente como claves primarias.

Resultado

Siguiendo esta guía, aprendió qué son las claves primarias y cómo crear tipos comunes en MySQL para identificar filas únicas en las tablas de la base de datos. Creó claves primarias naturales, claves primarias que abarcaban varias columnas y utilizó claves secuenciales autoincrementales donde no existen claves naturales.