63 - Clave foránea.

Un campo que se usa para establecer un "join" (unión) con otra tabla en la cual es clave primaria, se denomina "clave ajena o foránea".

En el ejemplo de la librería en que utilizamos las tablas "libros" y "editoriales" con los campos:

 libros: codigo (clave primaria), titulo, autor, codigoeditorial, precio, cantidad y
 editoriales: codigo (clave primaria), nombre.

el campo "codigoeditorial" de "libros" es una clave foránea, se emplea para enlazar la tabla "libros" con "editoriales" y es clave primaria en "editoriales" con el nombre "codigo".

Cuando alteramos una tabla, debemos tener cuidado con las claves foráneas. Si modificamos el tipo, longitud o atributos de una clave foránea, ésta puede quedar inhabilitada para hacer los enlaces.

Las claves foráneas y las claves primarias deben ser del mismo tipo para poder enlazarse. Si modificamos una, debemos modificar la otra para que los valores se correspondan.


PROBLEMA RESUELTO

Trabajamos con las tablas "libros" y "editoriales" de una librería.

Eliminamos las tablas, si existen:

 drop table libros, editoriales;

Creamos las tablas:

 create table libros(

  codigo int unsigned auto_increment,

  titulo varchar(40) not null,

  autor varchar(30) not null default 'Desconocido',

  codigoeditorial tinyint unsigned not null,

  precio decimal(5,2) unsigned,

  cantidad smallint unsigned default 0,

  primary key (codigo)

 );

 create table editoriales(

  codigo tinyint unsigned auto_increment,

  nombre varchar(20) not null,

  primary key(codigo)

 );

En este ejemplo, el campo "codigoeditorial" de "libros" es una clave foránea, se emplea para 

enlazar la tabla "libros" con "editoriales".

Ingresamos algunos registros:

 insert into editoriales values(2,'Emece');

 insert into editoriales values(15,'Planeta');

 insert into editoriales values(23,'Paidos');

 insert into libros values(1,'El aleph','Borges',23,4.55,10);

 insert into libros values(2,'Alicia en el pais de las maravillas','Lewis Carroll'

                           ,2,11.55,2);

 insert into libros values(3,'Martin Fierro','Jose Hernandez',15,7.12,4);

Si modificamos el tipo, longitud o atributos de una clave foránea, ésta puede quedar inhabilitada para hacer los enlaces.

Veamos un ejemplo:

 alter table libros

  modify codigoeditorial char(1);

Veamos cómo afectó el cambio a la tabla "libros":

 select * from libros;

El libro con código de editorial "23" ("Paidos") ahora tiene "2" ("Emece") en "codigoeditorial" y

el libro con código de editorial "15" ("Planeta") ahora almacena "1" (valor inexistente en "editoriales").

Si buscamos coincidencia de códigos en la tabla "editoriales":

 select l.titulo,e.nombre

  from libros as l

  join editoriales as e

  on l.codigoeditorial=e.codigo;

El resultado es erróneo.

Las claves foráneas y las claves primarias deben ser del mismo tipo para poder enlazarse. 

Si modificamos una, debemos modificar la otra para que los valores se correspondan.

Intentemos modificar la clave en "editoriales":

 alter table editoriales

  modify codigo char(1);

No lo permite porque si la modifica los valores para el campo clave quedan repetidos.

PROBLEMA PROPUESTO

Una empresa tiene registrados sus clientes en una tabla llamada "clientes", 

también tiene una tabla "provincias" donde registra los nombres de las 

provincias.

1- Elimine la tabla "clientes" y "provincias", si existen:

2- Créelas con las siguientes estructuras:

 create table clientes (

  codigo int unsigned auto_increment,

  nombre varchar(30) not null,

  domicilio varchar(30),

  ciudad varchar(20),

  codigoprovincia tinyint unsigned,

  telefono varchar(11),

  primary key(codigo)

 );

 create table provincias(

  codigo tinyint unsigned auto_increment,

  nombre varchar(20),

  primary key (codigo)

 );

En este ejemplo, el campo "codigoprovincia" de "clientes" es una clave 

foránea, se emplea para enlazar la tabla "clientes" con "provincias".

3- Ingrese algunos registros para ambas tablas:

 insert into provincias(codigo,nombre) values(1,'Cordoba');

 insert into provincias(codigo,nombre) values(2,'Santa Fe');

 insert into provincias(codigo,nombre) values(30,'Misiones');

 insert into provincias(codigo,nombre) values(13,'Salta');

 insert into provincias(codigo,nombre) values(15,'Buenos Aires');

 insert into provincias(codigo,nombre) values(20,'Neuquen');

 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)

  values ('Lopez Marcos', 'Colon 111', 'Córdoba',1,'null');

 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)

  values ('Perez Ana', 'San Martin 222', 'Cruz del Eje',1,'4578585');

 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)

  values ('Garcia Juan', 'Rivadavia 333', 'Villa Maria',1,'4578445');

 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)

  values ('Perez Luis', 'Sarmiento 444', 'Rosario',2,null);

 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)

  values ('Ramos Betina', 'San Martin 999', 'Bahia Blanca',15,'4223366');

 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)

  values ('Lopez Lucas', 'San Martin 1010', 'Posadas',30,'0457858745');

4- Enlace las tablas:

5- Modifique el campo "codigoprovincia" a "char(1)":

6- Vea cómo afectó el cambio a la tabla "clientes":

El cliente de "Bahia Blanca" con código de provincia "15" ("Buenos Aires") 

ahora tiene "1" ("Cordoba") y el cliente con código de provincia "30" 

("Misiones") ahora almacena "3" (valor inexistente en "provincias").

7- Realice un "left join" buscando coincidencia de códigos en la tabla 

"provincias":

 El resultado es erróneo.

8- Intente modificar la clave primaria en "provincias" para que se corresponda 

con "codigoprovincia" de "clientes":

No lo permite porque si la modifica los valores para el campo clave quedan repetidos.

Otros problemas:

Un club dicta clases de distintos deportes. En una tabla llamada "deportes" 

guarda la información de los distintos deportes que se enseñan y en una tabla

denominada "inscriptos" almacena la información necesaria para las inscripciones 

a los distintos deportes.

1- Elimine las tablas si existen.

2- Cree las tablas:

 create table deportes(

  codigo tinyint unsigned,

  nombre varchar(20),

  profesor varchar(30),

  primary key(codigo)

 );

 create table inscriptos(

  documento char(8) not null, 

  codigodeporte tinyint unsigned not null,

  año year,

  matriculapaga char(1),/* 's' si está paga, 'n' si no está paga*/

  primary key(documento,codigodeporte,año)

 );

3- Ingrese algunos registros para ambas tablas:

 insert into deportes values(1,'Tenis','Juan Lopez');

 insert into deportes values(2,'Natacion','Maria Lopez');

 insert into deportes values(3,'Basquet','Antonio Juarez');

 insert into inscriptos values ('22333444',2,'2005','s');

 insert into inscriptos values ('22333444',2,'2006','n');

 insert into inscriptos values ('23333444',2,'2005','s');

 insert into inscriptos values ('23333444',1,'2005','s');

 insert into inscriptos values ('23333444',1,'2006','s');

 insert into inscriptos values ('24333444',2,'2006','n');

 insert into inscriptos values ('24333444',3,'2006','n');

4- Muestre el nombre del deporte y todos los campos de la tabla "inscriptos":

5- Modifique el campo "codigo" de "deportes" para que almacene 1 caracter:

6- Actualice la tabla "deportes" almacenando en "codigo" el primer caracter 

del nombre del deporte:

7- Vea cómo cambió la tabla:

8- Realice un "join":

No encuentra coincidencia.