Lección 14 - Relaciones, claves primarias y foráneas

Curso SQL para principiantes


Las Relaciones es lo que, aparte de dar el nombre a las BD relacionales, hacen de este modelo una potente herramienta de reunión de datos. Para abordar las relaciones debemos tratar primero el concepto de clave primaria y clave foránea, puesto que son estas claves las que establecen las relaciones en una BD, y realizan la reunión de datos mediante consultas SQL.

Clave primaria
Si usted recuerda como su profesor de enseñanza básica pasaba lista a la clase, recordará que nombraba a los alumnos bien por su apellido, bien por su nombre, dependiendo del caso, e incluso por nombre y apellido si era necesario evitar ambigüedades. El propósito era dejar claro a quien se estaba haciendo mención y no dar lugar a dudas entre dos alumnos de igual nombre o apellido. Podríamos decir que el profesor asignaba una clave primaria a cada alumno, y con ello todo el mundo sabía que clave identificar como propia y responder: presente, al oírla.

Podemos considerar que una tabla es como una clase, y el conjunto de registros que contiene son los alumnos de esta clase. Para identificar cada registro es necesario establecer, de igual modo que hace el profesor, una clave primaria, con el propósito de identificar cada registro de forma única, por lo que el valor, o valores que ejercen de clave en un registro no se pueden repetir en el resto de registros de la tabla, ni en futuros registros que puedan existir. De esto ya se encarga el SGBD al especificarle que campos de la tabla forman la clave primaria, devolviendo un error cuando se intenta duplicar una clave primaria al insertar un nuevo registro en la tabla.

Un error común, a mi entender, al establecer la clave primaria de una tabla es intentar aprovechar algún campo de datos para que ejerza de clave, por ejemplo el DNI(documento nacional de identidad) de una persona. Aparentemente es un campo que no se puede repetir y, por tanto, es un buen candidato para ejercer de clave primaria en, por ejemplo , una tabla de empleados o de alumnos. Sin embargo no tenemos control sobre él, es decir, no podemos garantizar que no se repita. En ocasiones se asigna un DNI que perteneció a una persona ya fallecida, a un nuevo ciudadano de modo que aunque sea una posibilidad remota, puede dar problemas. Eso sin considerar que en ocasiones pueda resultar una clave poco práctica de manejar.

Otro error común es pretender que el campo clave guarde información implícita en la propia clave. Por ejemplo, a un vehículo de nuestra flota le asignamos la clave 1100456, donde el 11 está indicando que es marca SEAT y el 00456 es el resto de la clave.

Mi consejo es que no se empeñe, ni en aprovechar datos para que ejerzan de clave, ni en aprovechar claves para que implícitamente contengan información relevante. Las claves son claves y deben diseñarse únicamente para identificar registros. Los números naturales (1, 2 , 3 , etc...) son excelentes candidatos para ejercer de clave, se pueden ordenar (el SGBD creará índices sobre los campos clave que agilizarán las consultas) y son infinitos (siempre dispondremos de un valor para no repetir claves).

Clave foránea
La clave o claves foráneas de una tabla son referencias a registros de otra tabla, formándose entre ambas tablas una relación. Una registro de la tabla que tiene la clave foránea, llamemoslo registro hijo, apunta a un solo registro de la tabla a la que hace referencia, llamemoslo registro padre. Por tanto, una clave foránea apuntará siempre a la clave primaria de otra tabla.
De hecho el nombre ya nos indica que es una clave externa, es decir, el valor que contiene un registro en el campo, o campos, que ejercen de clave foránea, deberá contenerlo algún registro(uno solo) en el campo, o campos, que ejercen de clave primaria en la tabla a la que hace referencia dicha clave foránea.

Es también el SGBD quien garantiza esto, no dejando armar una clave foránea si pretendemos montarla sobre el campo, o campos, que no son clave primaria en la tabla con la que se pretende relacionar.
Tampoco permitirá, devolviendo un error, insertar valores que no existen como clave primaria en la tabla padre, o tabla a la que se hace referencia. A esto se le llama integridad referencial. El SGBD no permite incoherencias referenciales, de modo que si por ejemplo se intenta eliminar un registro padre el cual dejaría hijos huérfanos en otras tablas, es decir, tiene referencias o claves foráneas de él, el SGBD devuelve un error y no se realiza la operación.

Relaciones
El modo de relacionar registros entre tablas es por tanto mediante referencias, para lo cual se usan los identificadores definidos como claves primarias y foráneas.

Supongamos una academia donde se imparten clases, en consecuencia habrá cursos, profesores y alumnos. En nuestra base de datos diseñamos una tabla para cada entidad, es decir, para alumnos, profesores y cursos. Veamos como se relacionan entre si estas tres entidades y como se establecen estas relaciones en la base de datos.

Intuitivamente usted puede resolver la siguiente relación: La academia oferta cursos que imparten los profesores a los alumnos matriculados, y está en lo cierto, pero para relacionar esto en una BD debemos conocer en que medida se relacionan entre si estas tres entidades, es lo que se llama cardinalidad de una relación. Veamos primero el diseño de las tablas, los datos que contienen, y que campo, o campos, juegan el papel de identificador o clave primaria.
Los campos clave se han bautizado con el prefijo ID, abreviación de identificador.

TABLA CURSOS


TABLA PROFESORES


TABLA ALUMNOS


A estas tablas se las llama "maestros", dado que contienen información relevante y concreta de cada entidad, así hablaremos del maestro de profesores o del maestro de alumnos. Bien, para establecer las relaciones entre estas tres tablas necesitamos conocer con algo más de detalle la actividad en la academia, de modo que después de investigar un poco sacamos las siguientes conclusiones:

  • Cada curso lo imparte un único profesor, sin embargo algún profesor imparte más de un curso.

  • Cada curso tiene varios alumnos, y algunos alumnos cursan dos o más cursos.

* * *

Relación de cardinalidad 1 a N

Establezcamos la siguiente relación:

Cada curso lo imparte un único profesor, sin embargo algún profesor imparte más de un curso.

Para ello basta con crear un campo en la tabla CURSOS que informe que profesor lo imparte. Este dato es una clave primaria de la tabla PROFESORES alojada en la tabla CURSOS, de ahí lo de clave foránea, por tanto el campo que ejercerá de clave foránea en la tabla CURSOS debe ser forzosamente una referencia a la clave primaria de la tabla PROFESORES.
Este tipo de relación se denomina de uno a varios, también denominada de 1 a N: un profesor imparte varios cursos, pero un curso es impartido por un único profesor. En estos casos siempre se diseña una clave foránea en la tabla hijo(CURSOS) que apunta a la tabla padre(PROFESORES).

Debemos diseñar entonces una clave foránea en la tabla CURSOS para alojar valores que son clave primaria de la tabla PROFESORES. En este caso diseñaremos un campo que llamaremos ID_PROFE, aunque se podría llamar de cualquier otro modo, que contendrá el identificador de profesor que imparte el curso que representa cada registro. Veamos como queda la tabla CURSOS:



Observando los datos de la tabla se aprecia como efectivamente cada curso lo imparte un único profesor, y que algún profesor imparte más de un curso. También se observa como uno de los curso no se le ha asignado profesor, dado que el campo ID_PROFE esta a nulo. Por lo tanto una clave foránea apuntará a un solo registro de la tabla padre o no apuntará a ninguno, en cuyo caso guardará un valor indeterminado o nulo, pero jamás contendrá un valor que no exista en la tabla padre.

A usted se le puede ocurrir que es mucho más práctico y simple guardar para cada curso el nombre del profesor en lugar de claves que apenas nos dicen nada a simple vista. Esto sería transgredir la filosofía de las BD relacionales, que defienden la no duplicidad de información. El nombre de un profesor debe estar en el maestro de profesores, y cualquier referencia a ellos debe hacerse mediante su identificador. Con ello conseguimos tres cosas destacables:

  • No se duplica información en la BD.

  • Cualquier cambio o corrección de esa información solo debe realizarse en un único lugar.

  • Evitamos la ambigüedad al no llamar la misma cosas de mil formas distintas en mil ubicaciones posibles.

Veamos la consulta que reune el nombre de cada profesor junto al curso que imparte.

Código: Seleccionar todo
select *
  from CURSOS C, PROFESORES P 
 where C
.ID_PROFE = P.ID_PROFE 



Observe como si omitimos la cláusula WHERE de la anterior consulta, el SGBD realizaría el producto cartesiano entre los cursos y los profesores, es decir, asociaría todos los profesores con todos los cursos. El hecho de disponer de un indicador por cada curso que informa que profesor lo imparte, permite filtrar el producto cartesiano y solicitar aquellas filas que la columna ID_PROFE procedente de la tabla cursos es igual a la columna ID_PROFE procedente de la tabla PROFESORES, discriminando así las filas del producto cartesiano que carecen de sentido y obteniendo aquellas que guardan una relación.


Una lista de esto mismo mejor presentada:
Código: Seleccionar todo
select concat('Curso de ',C.TITULO,', impartido por ',P.NOMBRE,' ',P.APELLIDOS) CURSOS
  from CURSOS C
, PROFESORES P 
 where C
.ID_PROFE = P.ID_PROFE 



Las relaciones de 1 a N son quizás las más comunes en una BD y pueden verse como un padre, tabla referenciada, con muchos hijos, tabla que hace referencia a este padre. En el caso que se acaba de tratar el padre es el profesor y los hijos son los cursos que imparte dicho profesor. Todo hijo tiene forzosamente un padre, a no ser que la clave foránea pueda contener valores nulos, mientras que un padre puede tener de cero a muchos hijos.

* * *

Relación de cardinalidad N a M
Establezcamos la siguiente relación:

Cada curso tiene varios alumnos, y algunos alumnos cursan dos o más cursos.

Esta relación es un poco más laboriosa de establecer en la base de datos, puesto que un alumno cursa varios cursos, y a su vez, un curso es cursado por varios alumnos. Este tipo de relación se denomina de varios a varios, o bien, de N a M. Necesitamos crear una nueva tabla denominada tabla de relación, y que tiene como propósito definir la relación de N a M. La nueva tabla: ALUMNOS_CURSOS, contendrá como mínimo las claves primarias de ambas tablas: ID_ALUMNO e ID_CURSO. La clave primaria de la nueva tabla la formaran ambos campos conjuntamente, y a su vez cada uno de ellos por separado será clave foránea de la tabla ALUMNOS y CURSOS respectivamente.

Echemos un vistazo a la tabla ALUMNOS_CURSOS:


Fijese que esta tabla contiene únicamente referencias. Cada registro establece una relación, está relacionando un registro de la tabla CURSOS con un registro de la tabla ALUMNOS.

Veamos la consulta que realiza la reunión de los alumnos con los cursos que cursa cada uno:
Código: Seleccionar todo
select *
  from ALUMNOS_CURSOS AC, ALUMNOS A, CURSOS C
 where AC
.ID_ALUMNO = A.ID_ALUMNO
   and AC
.ID_CURSO  = C.ID_CURSO



Una lista de esto mismo mejor presentada:
Código: Seleccionar todo
  select C.TITULO CURSO , concat(A.APELLIDOS,', ',A.NOMBRE ) ALUMNO
    from ALUMNOS_CURSOS AC
, ALUMNOS A, CURSOS C
   where AC
.ID_ALUMNO = A.ID_ALUMNO
     and AC
.ID_CURSO  = C.ID_CURSO
order by C
.TITULO , A.NOMBRE , A.APELLIDOS



En este caso también podemos hacer el ejercicio de considerar el producto cartesiano entre estas tres tablas y como la cláusula WHERE permite ignorar aquellos registros del producto cartesiano que carecen de sentido y filtrar aquellos que guardan una relación.


La tabla de relación puede contener más información si es necesario, siempre y cuando la información sea vinculante tanto para el curso como para el alumno del registro en cuestión. No se tercia guardar aquí datos referentes al alumno que no tengan que ver con el curso, o datos del curso que no tengan que ver con el alumno. Por tanto registrar aquí cosas como la fecha de matrícula de un alumno en un curso, o la nota que el alumno ha sacado en un curso, tiene sentido, mientras que no lo tiene guardar aquí la fecha en que empieza un curso que nada tiene que ver con un alumno, o la veteranía del alumno en la academia que nada tiene que ver con un curso.

* * *

Relación de cardinalidad 1 a 1
No vamos a extendernos en esta tipo de relación puesto que no suelen darse mucho. En cualquier caso estas relaciones pueden verse como una relación 1 a N donde la N vale uno, es decir como una relación padre hijos donde el hijo es hijo único. En estos casos, cuando sólo se espera un hijo por registro padre, podemos montar la clave foránea en cualquiera de las dos tablas, aunque lo más correcto es establecerla en la tabla que NO es maestro. A efectos prácticos lo mismo da que el padre a punte al hijo que, a la inversa, es decir, que el hijo apunte al padre, o si usted quiere, cual de las dos tablas juega el papel de padre y cual de hijo. Lo importante es saber como se ha establecido la relación para atacarla mediante SQL al construir las consultas, pero siempre es preferible que la tabla maestro juegue el papel de padre.

* * *

Resumen
La clave primaria de una tabla permiten identificar registros de forma única, estas pueden ser simples: de un solo campo, o bien compuestas: formadas por dos o más campos. En cualquier caso los valores que toman estas claves no se pueden repetir en dos o más registros de la tabla, puesto que se perdería la funcionalidad de identificar un registro de forma única. De esto se encarga el SGBD si se ha especificado debidamente que campos son la clave primaria de la tabla.

Las claves foráneas de una tabla permiten establecer relaciones con otras tablas, puesto que contienen valores que encontramos como clave primaria en la tabla con la que se relaciona. Una clave foránea será simple o compuesta dependiendo de si lo es la clave primaria de la tabla a la que apunta o hace referencia.

Si al diseñar una tabla el campo o campos que forman una clave foránea pueden contener valores nulos, entonces el registro hijo puede no tener registro padre asociado. Esto es muy común que ocurra cuando un registro se ha creando en previsión y será en un futuro, después de que ocurra alguna cosa, que se le asignará un padre. Por ejemplo el curso sin profesor definido de la tabla CURSOS. El curso está previsto que se imparta, pero no se ha decidido o no se conoce aun que profesor lo impartirá, de ahí que el campo ID_PROFE de dicho registro contenga un valor nulo.

Las relaciones de 1 a N son quizás las que más se dan en una BD, en estos casos siempre encontraremos la clave foránea en el registro hijo apuntando al registro padre.

Las relaciones de N a M, entre por ejemplo dos tablas maestras, siempre necesitará una estructura auxiliar para establecer la relación. Esta tabla auxiliar se denomina tabla de relación, y contendrá como mínimo los campos que son clave primaria en ambos maestros. La clave primaria de la nueva tabla será siempre compuesta y estará formada por todos estos campos que son clave primaria en los maestros. A su vez estos campos por separado serán clave foránea de sus respectivos maestros. Por tanto los registros hijos se hallarán en la tabla de relación.

El modo de obtener la reunión de tablas relacionadas es mediante filtros sobre el producto cartesiano de dichas tablas, excluyendo con ayuda de la cláusula WHERE aquellos registros del producto cartesiano que carecen de sentido y obteniendo los que guardan una relación. Para ello debemos igualar la clave primaria de la tabla padre con la clave foránea de la tabla hijo.

* * *

Ejercicio 1
Construya una consulta que devuelva los cursos en que se ha matriculado el alumno con identificador 1.

Modifique la anterior consulta para que devuelva los nombres y apellidos de los alumnos, y los cursos en que se han matriculado, tales que el nombre de pila del alumno contenga un E.

Ejercicio 2
¿Cuantos cursos imparte cada profesor? Construya una consulta que responda a esta cuestión de modo que el resultado muestre el nombre completo del profesor acompañado del número de cursos que imparte.

Ejercicio 3
¿Cuantos alumnos hay matriculados en cada uno de los cursos? Construya una consulta que responda a esta cuestión de modo que el resultado muestre el titulo del curso acompañado de el número de alumnos matriculados.

Modifique la anterior consulta de modo que muestre aquellos cursos que el número de alumnos matriculados sea exactamente de dos alumnos.

Ejercicio 4
Si ahora a usted le pidiesen que adaptara la BD, que consta de las tres tablas presentadas en esta lección, a la siguiente necesidad: A todo alumno se le asignara un profesor que lo tutele. ¿Que cambios realizaría en la BD?




    Lección 13 - Consultas III (SQL SELECT FROM WHERE)
  • Lección 15 - Reunión interna y externa (INNER / OUTER JOIN)

Creative Commons License Creative Commons License

Este curso está sujeto a la licencia Reconocimiento-NoComercial-SinObraDerivada 3.0 de Creative Commons. Usted puede copiarla, distribuirla y comunicarla públicamente siempre que especifique su autor y deletesql.com; no la utilice para fines comerciales; y no haga con ella obra derivada. Puede usted consultar la licencia completa aquí.




Volver a Curso SQL desde cero

¿Quién está conectado?

Usuarios navegando por este Foro: No hay usuarios registrados visitando el Foro y 8 invitados