CURSO MariaDB 4: Claves foráneas y consultas avanzadas

En esta lección vamos a seguir avanzando en la creación de tablas donde explicaremos como crear claves foráneas (foreign keys) es decir, registros que están vinculados a otra tabla donde se nos proporciona más información. Esta tabla nos servirá también para hacer consultas avanzadas donde veamos como relacionar varias tablas a través de la clave foránea con INNER JOIN.

Para ver todo esto crearemos la tabla cb_country que dentro de las columnas que la definen tenemos monedas e idiomas con las respectivas tablas cb_currency y cb_language.

MariaDB claves foráneas / foreign key

Creamos la tabla cb_country del proyecto Learning Project en MariaDB para explicar las claves foráneas (foreign key) y ver consultas SQL avanzadas.

Código Xules

Lo que vamos ver

1. Creamos las tabla cb_country
2. SELECT con INNER JOIN obtener datos de tablas relacionadas
   • SELECT con SQL ALIAS
   • INNER JOIN CON UNA TABLA (cb_country con cb_currency)
   • INNER JOIN CON DOS TABLAS (cb_country con cb_currency y cb_language)
3. SELECT INNER JOIN vs COMA
   • Ventajas de usar INNER JOIN
   • Ventajas de usar COMAS
4. Sintaxis SELECT completa con INNER JOIN
   • SELECT FROM WHERE con INNER JOIN
   • SQL OPERADORES
   • ORDER BY
   • GROUP BY
   • GROUP BY con HAVING
   • Establecemos el número de registros que queremos con LIMIT

1. Creamos la tabla cb_country

La tabla cb_country es la tabla de países para el proyecto, en este caso para cada país indicaremos cual es el idioma y su moneda, estos campos se encuentran definidios en las tablas cb_language y cb_currency respectivamente, entonces para poder hacer referencia a los datos registrados en las otras tablas, lo que hacemos es registrar las claves primarias en la tabla cb_country, y relacionarlas con las tablas de origen.

Usamos idcountry como clave primaria: CONSTRAINT pk_cb_country PRIMARY KEY (idcountry), en este caso también definimos como clave única countrycode, es decir, que este valor no se puede repetir, para ellos utilizamos: CONSTRAINT un_cb_country_countrycode UNIQUE, como ya se explicó anteriormente.

Expliquemos brevemente el resto de los campos de la tabla cb_country, recordad que esta tabla y el resto de Learning Project están enfocadas hacia el aprendizaje:

• country: nombre del país
• description: descripción del país, para incluir datos de referencia, por ejemplo.
• countrycode: código iso de país por ejemplo ES para España.
• regionname: nombre que se le da a la región en este país.
• expressionphone: se podría utilizar para registrar un Regexp que evalue el teléfono por país, por ejemplo.
• displaysequence: definición de como mostramos el teléfono a nivel de aplicación.
• isdefault: si es el país por defecto colocaremos la letra Y de YES en caso contrario la N de NO, esto está pensado para usar a nivel aplicación.
• ibannodigits e ibancountry: el primero es el número de digítos del iban en ese país, el segundo son las letras que se utilizan para identificadr el país en el iban, que son los 2 primeros caracteres, por ejemplo en Esapaña.
• isactive: variable booleana para indicar si el idioma está activo, valor true en ese caso.
• idlanguage: identificador único de la tabla cb_language nos sirve para relacionar el idioma con su tabla.
• idcurrency: identificador único de la tabla cb_currency nos sirve para relacionar el idioma con su tabla.

El código SQL completo para la tabla cb_country es el siguiente:

CREATE TABLE cb_country
(
  idcountry INT NOT NULL AUTO_INCREMENT,
  country VARCHAR(100) NOT NULL,
  description VARCHAR(255),
  countrycode VARCHAR(2) NOT NULL,
  hasregion VARCHAR(1) NOT NULL DEFAULT 'N',
  regionname VARCHAR(60),
  expressionphone VARCHAR(20),
  displaysequence VARCHAR(20) NOT NULL,
  isdefault  VARCHAR(1) NOT NULL DEFAULT 'N',
  ibannodigits NUMERIC,
  ibancountry VARCHAR(2),
  isactive BOOLEAN NOT NULL DEFAULT true,
  idlanguage VARCHAR(6),
  idcurrency INT,
  CONSTRAINT pk_cb_country PRIMARY KEY (idcountry),
  CONSTRAINT fk_cb_country_idcurrency FOREIGN KEY (idcurrency)
      REFERENCES cb_currency (idcurrency) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_cb_country_idlanguage FOREIGN KEY (idlanguage)
      REFERENCES cb_language (idlanguage) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT un_cb_country_countrycode UNIQUE (countrycode),
  CONSTRAINT ch_cb_country_hasregion_check CHECK (hasregion IN ('Y', 'N')),
  CONSTRAINT ch_cb_country_isdefault_check CHECK (isdefault IN ('Y', 'N'))
)
ENGINE=InnoDB
COMMENT='Tabla donde se definen todos los países con sus características principales: idioma, nombre, ..., y diferentes datos íntrinsecos a cada país.';
GRANT ALL ON TABLE cb_country TO xulescode;

La sintaxis para las claves foráneas en MariaDB, al definir una clave foránea además se pueden definir acciones al efectuar un UPDATE o un DELETE sobre el campo. El valor simbolo es el nombre que le asignamos a la clave y debe ser única, es la siguiente:

[CONSTRAINT [symbol]] FOREIGN KEY
     [index_name] (index_col_name, ...)
     REFERENCES tbl_name (index_col_name,...)
     [ON DELETE reference_option]
     [ON UPDATE reference_option]

A continuación de FOREIGN KEY definimos en campo de la tabla que utilizaremos como referencia, siempre utilizaremos un índice, o como mínimo parte de un índice compuesto; usamos REFERENCES para indicar la tabla y la columna con la que establecemos la vinculación, vamos a explicar nuestro ejemplo donde lo entederemos más fácilmente.

En este caso para los países definimos el idioma y la moneda por lo que vamos a relacionar cb_country con las tablas correspondientes de cb_language y cb_currency, para esto vamos a definir las claves foráneas (foreign key) correspondientes:

• Definimos la clave foránea para cb_currency, que consiste en relacionar el valor de una tabla con otra estableciendo la relación, en este caso por el id de la tabla:

  CONSTRAINT fk_cb_country_idcurrency [Nombre de la tabla] FOREIGN KEY (idcurrency) [Campo en cb_country]
	REFERENCES cb_currency (idcurrency) [Tabla relacionada y el campo] MATCH SIMPLE
	ON UPDATE NO ACTION ON DELETE NO ACTION [Definición de las acciones]

• Definimos la clave foránea para cb_language, que consiste en relacionar el valor de una tabla con otra estableciendo la relación, en este caso por el id de la tabla:

  CONSTRAINT fk_cb_country_idlanguage [Nombre de la tabla] FOREIGN KEY (idlanguage) [Campo en cb_country]
	REFERENCES cb_language (idlanguage) [Tabla relacionada y el campo] MATCH SIMPLE
	ON UPDATE NO ACTION ON DELETE NO ACTION [Definición de las acciones]

La estructura entidad relación entre las tablas quedaría de la siguiente manera como se puede ver en la imagen:

Para poder realizar pruebas os dejamos el script completa para la creación de las tablas cb_language, cb_currency y cb_country, así como el script completo de datos para las 3 tablas, y para los que hayáis hecho el curso paso a paso os pasamos también los datos de los países por separado:

Este es el script con las 3 tablas que llevamos desarrolladas en este curso de MariaDB que como se explico en CURSO MariaDB: 1. Preparación del entorno forman parte del esquema de 8 tablas del proyecto Learning Project que nos sirve de base para los desarrollos en Código Xules:

Aquí puedes descargarte también el script SQL de las 3 tablas: cb_country, cb_currency y cb_language:

2. SELECT con INNER JOIN obtener datos de tablas relacionadas

Cuando queremos obtener los datos de una tabla relacionada con la tabla principal que estamos consultando necesitamos utilizar la relación INNER JOIN que define la relación entre las tablas con ese valor.

En nuestro caso en la definición de cb_country tenemos un caso claro con cb_currency y cb_language, por ejemplo, en la tabla cb_country solo tenemos el identificador único de cb_currency que nos sirve para establecer la relación entre las tablas, pero si creamos un listado ese datos no nos dice nada, estableciendo la relacción con INNER JOIN podemos obtener en la consulta todos los datos de la moneda relacionada por idcurrency.

La sintaxis para la relación de tablas permite establecer relaciones entre las mismas como si fueran conjuntos, empezaremos por INNER JOIN y después no extenderemos con el resto, la sintaxis es la siguiente

join_table:
     table_reference [INNER | CROSS] JOIN table_factor [join_condition]
   | table_reference STRAIGHT_JOIN table_factor
   | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
   | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
   | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor
 join_condition:
     ON conditional_expr
   | USING (column_list

SELECT con SQL ALIAS

Recordemos como se hace una consulta SELECT sobre una tabla y aprovechemos para introducir el concepto de ALIAS. SQL ALIAS consiste simplemente en añadirle un identificador a la tabla, para que a través de él podamos hacer referencia al campo de la tabla que estamos utilizando, veamos un ejemplo con cb_country:

SELECT     co.country, co.idcountry, co.countrycode, 
         co.description, co.isactive, co.idcurrency, co.idlanguage 
     FROM cb_country co
     ORDER BY co.country 

¿Porqué es útil?, como veremos a continuación cuando relacionamos por ejemplo dos tablas que tienen un campo con el mismo nombre como podría ser cb_country y cb_currency, la base de datos necesita saber a que tabla hace referencia el campo, el uso de ALIAS nos facilita la tarea, la alternativa sería poner el nombre canónico del campo, es decir, el nombre de la tabla y a continuación el campo seleccionado. A continuación la consulta superior pero usando el nombre de la tabla, como verás tenemos que escribir mucho más, y además no aporta mayor información:

SELECT cb_country.country, cb_country.idcountry, cb_country.countrycode, 
         cb_country.description, cb_country.isactive, cb_country.idcurrency, cb_country.idlanguage 
     FROM cb_country
     ORDER BY cb_country.country     

El resultado en ambos casos es el mismo, aquí un extracto de los primeros registros mostrados:

INNER JOIN CON UNA TABLA (cb_country con cb_currency)

Consulta sobre cb_country utilizando INNER JOIN para relacionar la tabla con cb_currency, a través de la clave foránea definida con idcurrency, en esta consulta al poner asterisco (*) mostraremos todos los campos de las dos tablas:

SELECT *
     FROM cb_country co
     INNER JOIN cb_currency cu 
     ON co.idcurrency = cu.idcurrency     

A continuación, el mismo INNER JOIN seleccionando las columnas que queremos obtener de la consulta:

SELECT co.country, co.idcountry, co.countrycode, 
         co.description, co.isactive, co.idcurrency, 
         cu.currency, cu.description, cu.cursymbol, cu.isocode,
         co.idlanguage 
     FROM cb_country co
     INNER JOIN cb_currency cu 
     ON co.idcurrency = cu.idcurrency     

En ambos casos el resultado será el mismo, vemos los datos visualizados en DBeaver:

INNER JOIN CON DOS TABLAS (cb_country con cb_currency y cb_language)

En una consulta SELECT podemos relacionar tantas tablas como necesitemos en este caso vamos a añadir a la consulta anterior donde relacionabamos cb_country y cb_currency, la relación con cb_language con INNER JOIN utilizando la columna de la clave foránea idlanguage que relaciona las dos tablas:

SELECT co.country, co.idcountry, co.countrycode, co.description, co.isactive, 
         co.idcurrency, 
         cu.currency, cu.cursymbol, cu.isocode,
         co.idlanguage,
         la.namelanguage, la.countrycode , la.languageiso 
     FROM cb_country co
     INNER JOIN cb_currency cu 
     ON co.idcurrency = cu.idcurrency
     INNER JOIN cb_language la
     ON co.idlanguage = la.idlanguage   

El resultado de la consulta es el siguiente:

3. SELECT INNER JOIN vs COMA

Qué diferencia hay entre estas dos formas de realizar un INNER JOIN, pues a nivel de resultados ninguna ya que es lo mismo, si hay una gran diferencia a la hora de hacer las consultas, veamos las ventajas de una y otra.

Realicemos la consulta anterior utilizando «COMAS» en vez de INNER JOIN, este sería el resultado de la consulta que relaciona cb_country con cb_currency y cb_language:

SELECT co.country, co.idcountry, co.countrycode, co.description, co.isactive, 
         co.idcurrency, 
         cu.currency, cu.cursymbol, cu.isocode,
         co.idlanguage,
         la.namelanguage, la.countrycode , la.languageiso 
     FROM    cb_country co, 
             cb_currency cu,
             cb_language la
     WHERE   co.idcurrency = cu.idcurrency AND 
             co.idlanguage = la.idlanguage  

El resultado será el mismo pero la sintaxis como se puede ver cambia bastante.

Ventajas de usar INNER JOIN

Ventajas de usar INNER JOIN, la primera sería que MariaDB recomienta esta opción por 3 factores:

• Legibilidad: es más fácil la lectura de una consulta con INNER JOIN que con «COMAS» ya que las columnas que intervienen en la relación van a continuación en la claúsula ON.
• Flexibilidad: al establecer las consultas con INNER JOIN es más fácil cambiar el operador para utilizar LEFT OUTER JOIN que si lo establecemos con «COMAS».
• Portabilidad: el traspaso de estás consultas siguiendo la sintaxis de INNER JOIN hará más sencilla la portabilidad a otras bases de datos.

Ventajas de usar COMAS

Si bien las recomendaciones y los factores de MariaDB son correctos, también hay que señalar que la sintaxis por «COMAS» es más ágil sobre todo para gente experta en SQL, en cuanto a las factores para gente experta como es mi caso, pues no serían tan determinante, veamos, en cuanto a :

• Legibilidad: para mis es más natural por comas, si bien entiendo que para la gente que esta empezando no.
• Portabilidad: los principales motores de base de datos soportante la sintaxis por «COMAS».
• Flexibilidad: si estas acostumbrado a utilizar SQL cuando necesites utilizar un LEFT OUTER JOIN ya lo vas a utilizar, es muy raro que una vez tengas la consulta te des cuenta que mejor usar LEFT OUTER JOIN en vez de INNER JOIN.

Una vez dicho esto, para la gente que este empezando recomiendo utilizar INNER JOIN.

4. Sintaxis SELECT completa con INNER JOIN

En la lección 3 del CURSO MARIADB ya vimos como se realizaban las consultas SQL con SELECT vamos a aplicar los mismo conceptos con INNER JOIN, ampliándolos con más conceptos cómo: GROUP BY, ORDER BY o LIMIT. Recordemos para empezar la sintaxis MYSQL:

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    select_expr [, select_expr ...]
    [ FROM table_references [PARTITION (partition_list)]

      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Cláusulas SQL SELECT FROM WHERE CON INNER JOIN

Ya hemos visto antes como utilizabamos INNER JOIN para relacionar las tablas, vamos a ver como aplicar filtros utilizando la claúsula WHERE, el concepto es el mismo que para una tabla, solo que ahora al tener tablas podemos aplicar un filtro sobre cualquiera de ellas, a través de los ALIAS.

Veamos un ejemplo filtrando en la claúsula WHERE por el idioma con idlanguage es_ES, la consulta sería la siguiente:

SELECT co.country, co.idcountry, co.countrycode, co.description, co.isactive, 
         co.idcurrency, 
         cu.currency, cu.cursymbol, cu.isocode,
         co.idlanguage,
         la.namelanguage, la.countrycode , la.languageiso 
     FROM cb_country co
     INNER JOIN cb_currency cu 
     ON co.idcurrency = cu.idcurrency
     INNER JOIN cb_language la
     ON co.idlanguage = la.idlanguage
<code>     WHERE la.idlanguage = 'es_ES'   </code>   

Este es el resultado:

En la consulta filtramos haciendo referencia a la tabla de idiomas con la.idlanguage como están relacionadas es lo mismo que filtrar por co.idlanguage ya que estamos relacionando las tablas con INNER JOIN.

En la claúsula WHERE podemos encadenar condiciones utilizando los operadores AND y OR, asímismo = no es el único operador que podemos utilizar, por ejemplo, si queremos buscar los países que empiezan por A podemos utilizar el operador LIKE, y además añadimos la condición de que los paises estén activos:

SELECT co.country, co.idcountry, co.countrycode, co.description, co.isactive, 
         co.idcurrency, 
         cu.currency, cu.cursymbol, cu.isocode,
         co.idlanguage,
         la.namelanguage, la.countrycode , la.languageiso 
     FROM cb_country co
     INNER JOIN cb_currency cu 
     ON co.idcurrency = cu.idcurrency
     INNER JOIN cb_language la
     ON co.idlanguage = la.idlanguage
<code>     WHERE co.country LIKE 'A%' AND co.isactive = TRUE;     </code>   

El uso del operador LIKE en este caso se utiliza e % para indicar que se incluyan todos los países cuya letra comience por A el resto es igual.

SQL OPERADORES

Qué otros operadores tenemos en MariaDB, y que podemos utilizar en vez de =, pues como os podeís imaginar todos los operadores lógicos y algunos otros, estos son:

• Not equal operator: !=
• Less than operator: <
• Less than operator: <
• Less than or equal operator: <=
• NULL-safe equal operator <=>
• Equal operator: =
• Greater than operator: >
• Greater than or equal operator: >=
• True if expression between two values: BETWEEN AND
• Returns the first non-NULL parameter: COALESCE
• Returns the largest argument: GREATEST
• True if expression equals any of the values in the list: IN
• Index of the argument that is less than the first argument: INTERVAL
• Tests whether a boolean is TRUE, FALSE, or UNKNOWN: IS
• Tests whether a boolean value is not TRUE, FALSE, or UNKNOWN: IS NOT
• Tests whether a value is not NULL: IS NOT NULL
• Tests whether a value is NULL: IS NULL
• Checks if an expression is NULL: ISNULL
• Returns the smallest argument: LEAST
• Same as NOT (expr BETWEEN min AND max): NOT BETWEEN
• Same as NOT (expr IN (value,…)): NOT IN

Vamos a ir utilzando algunos de estos operadores para ir viendo como funcionen a lo largo de las consultas siguientes.

SQL ORDER BY

En muchos casos necesitamos ordenar los resultados según nuestros interés y no según el orden natural de la consulta, para ello tenemos la cláusula ORDER BY, que nos permite ordenar por uno o varios parámetros, y de forma ascendente (ASC) y descendente (DESC), la ordenación ascendente es la ordenación por defecto del parámetro.

[ORDER BY {col_name | expr | position} [ASC | DESC], ...]

A continuación, mostramos la consulta de los países con los idiomas donde filtramos para aquellos países que en su namelanguae contienene Arabic y además, que tengan el campo ibannodigits con valor NULL. Además ordenamos en primer lugar por los países activos, y en segundo lugar por el nombre de país, esta es la consulta:

SELECT co.country, co.idcountry, co.countrycode, 
         co.description, co.isactive, co.ibannodigits, co.ibancountry ,
         co.idlanguage,
         la.namelanguage, la.countrycode , la.languageiso 
     FROM cb_country co
     INNER JOIN cb_language la
     ON co.idlanguage = la.idlanguage
     WHERE la.namelanguage LIKE '%Arabic%' AND ibannodigits IS NULL
     ORDER BY co.isactive, co.country

Este es el resultado:

Si el orden de países quisiesemos que fuese descendente simplemente, añadimos el operador DESC a continuación de la columna:

SELECT co.country, co.idcountry, co.countrycode, 
         co.description, co.isactive, co.ibannodigits, co.ibancountry ,
         co.idlanguage,
         la.namelanguage, la.countrycode , la.languageiso 
     FROM cb_country co
     INNER JOIN cb_language la
     ON co.idlanguage = la.idlanguage
     WHERE la.namelanguage LIKE '%Arabic%' AND ibannodigits IS NULL
     ORDER BY co.isactive, co.country DESC

Agrupaciones con GROUP BY

Con la claúsula GROUP BY podemos agrupar y contabilizar elementos de una tabla, esto nos puede ser para realizar: sumas (SUM), media (AVG) y contar elementos (COUNT). La sintaxis es sencilla, y las operaciones se declaran sobre las columnas en el SELECT:

[GROUP BY {col_name | expr | position} [ASC | DESC]

Una ejemplo sencillo lo podemos hacer, con la tabla cb_language contando el número de variantes de un idioma por ejemplo del españlo, que tiene varias variantes esto lo podemos hacer agrupando por el campo language iso, que identifica el idioma y hacemos un COUNT para contar las variantes:

SELECT la.languageiso, count(*) AS numlanguagesiso
     FROM cb_country co
     INNER JOIN cb_language la
     ON co.idlanguage = la.idlanguage    
     GROUP BY la.languageiso 
     ORDER BY numlanguagesiso DESC

Agrupaciones con GROUP BY y HAVING

En combinación con GROUP BY podemos utilizar HAVING para establecer una condición sobre la agrupación que estamos realizando, la sintaxis es muy sencilla:

[GROUP BY {col_name | expr | position} [ASC | DESC]
[HAVING where_condition]

Partiendo del ejemplo anterior vamos a añadir como condición que el número de variantes del idioma sea mayor que 5, como hacemos esto, pues usando HAVING, y a continuación, la condición:

SELECT la.languageiso, count(*) AS numlanguagesiso
	FROM cb_country co
	INNER JOIN cb_language la
	ON co.idlanguage = la.idlanguage	
	GROUP BY la.languageiso 
	HAVING count(*) > 5
	ORDER BY numlanguagesiso DESC

Establecemos el número de registros que queremos con LIMIT

Con LIMIT establecemos el número de registros que queremos que nos devuelva la consulta, aquí hay que tener en cuanta la ordenación, ya que por ejemplo si quieres recibir los 10 primeros que cumplen una condición tendrá que estar alineado con la ordenación. La sintaxis utiliza el operador LIMIT seguido de la cantidad de registros.

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

Utilizamos LIMIT para quedarnos con los 10 primeros países que estén activos y por orden alfabético:

SELECT co.country, co.idcountry, co.countrycode, 
         co.description, co.isactive, co.ibannodigits, co.ibancountry ,
         co.idlanguage,
         la.namelanguage, la.countrycode , la.languageiso 
     FROM cb_country co
     INNER JOIN cb_language la
     ON co.idlanguage = la.idlanguage
     ORDER BY co.isactive, co.country DESC LIMIT 10

Espero que te haya sido útil

Código Xules