Problema con claves foráneas

[sergiolpm]

Buenas, estoy creando una base de datos, y tengo que relacionar varias tablas, resulta que a la hora de escribir las claves foraneas me dan un error: Can't create table 'proyecto.ejemplares' (errno: 150), no soy capaz de encontrar ningún error en las tablas. Son estas:

"CREATE TABLE libros(
isbn varchar(17),
titulo varchar(50),
cod_autor varchar(40),
tema varchar(40),
cdu varchar(20),
nom_editorial varchar(40),
constraint primary key(isbn));";


"CREATE TABLE ubicacion(
pasillo varchar(1),
estanteria varchar(2),
constraint primary key(pasillo,estanteria));";


"CREATE TABLE ejemplares(
isbn varchar(17),
ejemplar varchar(3),
titulo varchar(50),
cod_autor varchar(40),
tema varchar(40),
cdu varchar(20),
nom_editorial varchar(40),
pasillo varchar(1),
estanteria varchar(2),
constraint primary key(isbn,ejemplar),
constraint foreign key(isbn) references libros(isbn) on delete cascade on update cascade,
constraint foreign key(pasillo) references ubicacion(pasillo) on delete cascade on update cascade,
constraint foreign key(estanteria) references ubicacion(estanteria) on delete cascade on update cascade);";





Todo me funciona bien hasta que llego a la clave foranea "pasillo".

De que tipo de error se puede tratar?
Gracias

[quimfv]

Código MySQL:

CREATE TABLE ejemplares(
isbn varchar(17),
ejemplar varchar(3),
titulo varchar(50),
cod_autor varchar(40),
tema varchar(40),
cdu varchar(20),
nom_editorial varchar(40),
pasillo varchar(1),
estanteria varchar(2),
constraint primary key(isbn,ejemplar),
constraint foreign key(isbn) references libros(isbn) on delete cascade on update cascade,
constraint foreign key(pasillo,estanteria) references ubicacion(pasillo,estanteria) on delete cascade on update cascade;
 

Intenta esto.

El problema puede ser que algun valor que contiene pasillo en ejemplares no este en ubicaciones.

Pero en qualquier caso te interesa la clave foranea compuesta puesto que lo que no puede haber en ejemplares es un par pasillo/estanteria que no exista en ubicaciones. Si controlas por separado puede que estes guardando un ejemplar en un pasillo que exista y en una estanteria que tambien exista pero en otro pasillo.

Otra posibilidad para no trabajar con claves compuestas es agregar un id autoincremental como pk de ubicaciones y un indice unico compuesto por pasillo/estanteria y luego en ejemplares vincular por el id.

[sergiolpm]

Muchas gracias por solucionarmelo, el problema estaba en la linea:
CONSTRAINT FOREIGN key(pasillo,estanteria) REFERENCES ubicacion(pasillo,estanteria) on DELETE CASCADE on UPDATE CASCADE;
ya me funciona a la perfección.
Thanks!! :D

[sergiolpm]

Hola de nuevo:
Sigo con problemas con las claves foráneas y la insercion de datos:
quiero insertar unos datos de un libro, asi como su isbn,titulo,tema,etc en una tabla llamada ejemplares:

//tabla ejemplares
$ejemplar="CREATE TABLE ejemplares(
isbn varchar(17),
ejemplar varchar(3),
titulo varchar(50),
autor varchar(40),
tema varchar(40),
cdu varchar(20),
nom_editorial varchar(40),
pasillo varchar(1),
estanteria varchar(2),
constraint primary key(isbn,ejemplar),
constraint foreign key(isbn) references libros(isbn) on delete cascade on update cascade,
constraint foreign key(pasillo,estanteria) references ubicacion(pasillo,estanteria) on delete cascade on update cascade);";

mysql_query($ejemplar,$link) or die(mysql_error());



la tabla ejemplares esta conectada por el isbn por la clave foranea con la tabla libros:

//tabla libros
$libro="CREATE TABLE libros(
isbn varchar(17),
titulo varchar(50),
autor varchar(40),
tema varchar(40),
cdu varchar(20),
nom_editorial varchar(40),
constraint primary key(isbn),
constraint fknombreditorial foreign key(nom_editorial) references editoriales(nom_editorial) on delete cascade on update cascade);";

mysql_query($libro,$link) or die(mysql_error());

cabe decir que la tabla libros esta puesta para que se cree antes que la tabla ejemplares. ahora, al insertar el codigo para insertar el libro:
$link=mysql_connect("localhost","genius","genius") ;
mysql_select_db("proyecto",$link);
$isbn=$_REQUEST[isbn];
$ejemplar=$_REQUEST[ejemplar];
$titulo=$_REQUEST[titulo];
$tema=$_REQUEST[tema];
$autor=$_REQUEST[autor];
$cdu=$_REQUEST[cdu];
$editorial=$_REQUEST[editorial];
$pasillo=$_REQUEST[pasillo];
$estanteria=$_REQUEST[estanteria];
$resultado=mysql_query("SELECT * FROM ejemplares WHERE isbn='$isbn' AND ejemplar='$ejemplar'",$link) or die(mysql_error());

if(mysql_num_rows($resultado)!=0){
echo "<script language='javascript'>alert('EL ISBN y ejemplar introducido ya existe')</script>";
}

else{


$agregarejemplar="INSERT INTO ejemplares(isbn,ejemplar,titulo,autor,tema,cdu,nom _editorial,pasillo,estanteria)
VALUES('$isbn','$ejemplar','$titulo','$autor','$te ma','$cdu','$editorial','$pasillo','$estanteria')" ;
mysql_query($agregarejemplar,$link) or die(mysql_error());
echo "<script language='javascript'>alert('Libro introducido correctamente')</script>";
}



aqui me da el error Cannot add or update a child row: a foreign key constraint fails (`proyecto`.`ejemplares`, CONSTRAINT `ejemplares_ibfk_1` FOREIGN KEY (`isbn`) REFERENCES `libros` (`isbn`) ON DELETE CASCADE ON UPDATE CASCADE)



puf esto es desesperante necesito algo de ayuda, gracias

[gnzsoloyo]

No puedes insertar un ejemplar en la tabla EJEMPLARES de un libro que aún no existe en la tabla LIBROS.
Una FK es una restricción de integridad, pero no hace que el registro de la tabla base se genere en forma automática. Eso no existe.
Tienes que llenar en orden:
1) Editorial.
2) Libro.
3) Ejemplar.

Finalmente:
Las sentencia de creación de las tablas se ejecutan una sola vez. No es necesario tenerlas en un script de un lenguaje de programación.
De hecho, normalmente es mejor que las bases no estén en los scripts de PHP, sino que se crean directamente, por medio de scripts en SQL, los que no deben quedar en el servidor.

[sergiolpm]

es decir, que cada vez que cree un ejemplar, tengo que crear datos en la tabla libros y la tabla editorial, asi como las demas tablas que tengo relacionadas no?

[gnzsoloyo]

Exactamente. ¿Cómo creías, si no?
La idea de las FK es asegurarse que existe la información relacionada con cada parte, y no pueda falta ninguna parte (integridad referencial).
Si ingresas un ejemplar, debe existir el libro en el listado. Si existe el libro, debe existir la editorial.
Obviamente eso implica que debes llenar las tablas base primero.

No te desesperes, así funcionan desde las bases de datos para negocios chicos, o la base de datos del Banco Mundial.

[quimfv]

Solo agregaria que para insertar el segundo ejemplar de un libro, o el segundo libro de un editorial, no tendras que insertar nada en las tablas base y ahi esta la ventaja de que todo este bien relacionado.

Para pasillos y estanterias lo mismo, si no existe el pasillo dificilmente podrías guardar nada en el. Luego para la bbdd si en ubicaciones no has entrado previamente el pasillo no podras entrar ejemplares guardados en el... como la vida misma, vaya.

[sergiolpm]

Gracias, lo que me dijisteis me funciono, ahora, como no, vuelve otro problemas con claves foráneas al querer modificar los ejemplares. tengo entendido que si modifico un dato de una tabla que este relacionado con una clave foránea, se actualizan ambas tablas al estar con UPDATE CASCADE, pero pare que esto no lo hace. codigo:

$editorial="CREATE TABLE editoriales(
nom_editorial varchar(40),
localizacion varchar(70),
dni_comercial varchar(11),
constraint pkeditorial primary key(nom_editorial),
constraint fkcomercial foreign key(dni_comercial) references comerciales(dni_comercial) on delete cascade on update cascade);";
mysql_query($editorial,$link) or die(mysql_error());


//tabla libros
$libro="CREATE TABLE libros(
isbn varchar(17),
titulo varchar(50),
autor varchar(40),
tema varchar(40),
cdu varchar(20),
nom_editorial varchar(40),
constraint primary key(isbn),
constraint fknombreditorial foreign key(nom_editorial) references editoriales(nom_editorial) on delete cascade on update cascade);";

mysql_query($libro,$link) or die(mysql_error());

$ejemplar="CREATE TABLE ejemplares(
isbn varchar(17),
ejemplar varchar(3),
titulo varchar(50),
autor varchar(40),
tema varchar(40),
cdu varchar(20),
nom_editorial varchar(40),
pasillo varchar(1),
estanteria varchar(2),
constraint primary key(isbn,ejemplar),
constraint foreign key(isbn) references libros(isbn) on delete cascade on update cascade,
constraint foreign key(pasillo,estanteria) references ubicacion(pasillo,estanteria) on delete cascade on update cascade);";
mysql_query($ejemplar,$link) or die(mysql_error());

en otra pagina php tengo este codigo, los $_REQUEST los saco de una pagina con los datos nuevos que quiero introducir que me mandan aqui:


$isbn=$_REQUEST[isbn];
$ejemplar=$_REQUEST[ejemplar];
$titulo=$_REQUEST[titulo];
$tema=$_REQUEST[tema];
$autor=$_REQUEST[autor];
$cdu=$_REQUEST[cdu];
$editorial=$_REQUEST[editorial];
$pasillo=$_REQUEST[pasillo];
$estanteria=$_REQUEST[estanteria];

$resultado1=mysql_query("UPDATE ejemplares set nom_editorial='$editorial' WHERE isbn='$isbn'",$link) or die(mysql_error());

$resultado=mysql_query("UPDATE libros set nom_editorial='$editorial' WHERE isbn='$isbn'",$link) or die(mysql_error());


quiero modificar el nombre de la editorial del ejemplar y el libro y me sale

Cannot add or update a child row: a foreign key constraint fails (`proyecto`.`libros`, CONSTRAINT `fknombreditorial` FOREIGN KEY (`nom_editorial`) REFERENCES `editoriales` (`nom_editorial`) ON DELETE CASCADE ON UPDATE CASCADE)

no se si estarán mal creadas creadas las claves foráneas en las tablas o si no lo entiendo

[gnzsoloyo]

Gracias, lo que me dijisteis me funciono, ahora, como no, vuelve otro problemas con claves foráneas al querer modificar los ejemplares. tengo entendido que si modifico un dato de una tabla que este relacionado con una clave foránea, se actualizan ambas tablas al estar con UPDATE CASCADE, pero pare que esto no lo hace. codigo:

El ON UPDATE CASCADE hace que si modificas la clave de una tabla base, todos los registros que tenían esa clave en otras tablas donde fuese FK, se actualizan automáticamente.
Esto lo puedes probar modificando la PK de un registro cualquiera.
Pero ten cuidado: Todas las apariciones de esa clave, como FK, en todas las demás tablas también deben haber sido definidas como ON UPDATE CASCADE. Si al menos una de ellas no fue definida así, generará un error.
¿Se entiende?
Pero no funciona a la inversa. SI lo que modificas es una tabla donde esa clave es FK, no se actualiza la tabla base... Forzosamente la clave que modifiques en esta tabla secundaria debe existir en su tabla de origen.
En otras palabras, no es una restricción bidireccional...