claves externas en MySQL

[aviana]

Hola, en MySQL tengo una tabla "usa" que se relaciona con otras 3, "proveedor", "cliente" y "forma de pago". Necesito que el campo "cod" de la tabla "usa" sea o un código de proveedor o uno de cliente, por lo que pongo una restricción de que sea clave externa al campo cod de proveedor y otra de que sea clave externa al campo dni de cliente, pero al meter datos válidos de un cliente o de un proveedor, me da error. ¿Qué falla? El código es:

CREATE TABLE `usa` (
`cod` varchar(9) NOT NULL default '',
`cod_pago` varchar(12) NOT NULL default '',
PRIMARY KEY (`cod`,`cod_pago`),
KEY `cod_pago` (`cod_pago`),
CONSTRAINT `usa_cliente` FOREIGN KEY (`cod`) REFERENCES `cliente` (`dni`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `usa_forma_pago` FOREIGN KEY (`cod_pago`) REFERENCES `forma_pago` (`cod_pago`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `usa_proveedor` FOREIGN KEY (`cod`) REFERENCES `proveedor` (`cod_prov`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;


CREATE TABLE `proveedor` (
`cod_prov` varchar(15) NOT NULL default '',
`dni` varchar(50) NOT NULL default '',
`nombre` varchar(30) NOT NULL default '',
`direccion` varchar(30) NOT NULL default '',
`localidad` varchar(20) NOT NULL default '',
`provincia` varchar(20) NOT NULL default '',
`telefono` varchar(9) NOT NULL default '',
`fecha_nac` date NOT NULL default '0000-00-00',
`sexo` char(1) NOT NULL default '',
PRIMARY KEY (`cod_prov`)
) TYPE=InnoDB;

CREATE TABLE `cliente` (
`dni` varchar(9) NOT NULL default '',
`direccion` varchar(30) NOT NULL default '',
`razon_social` varchar(30) NOT NULL default '',
`cod_contable` int(6) NOT NULL default '0',
`provincia` varchar(20) NOT NULL default '',
`riesgo_max` tinyint(4) default NULL,
`riesgo_act` tinyint(4) default NULL,
`tarifa` varchar(9) NOT NULL default '',
`sexo` char(1) default NULL,
`devoluciones` tinyint(4) default NULL,
`movil` varchar(9) default NULL,
`cobros_no_vencidos` tinyint(4) default NULL,
`pais` varchar(15) default NULL,
`recargo` char(2) default NULL,
`cod_postal` smallint(6) default NULL,
`observaciones` varchar(100) default NULL,
`acumulados` smallint(6) default NULL,
`alta` date default NULL,
`correoe` varchar(30) default NULL,
`actividad` varchar(30) default NULL,
`fax` varchar(9) default NULL,
`nacimiento` date default NULL,
`telefono` varchar(9) default NULL,
`localidad` varchar(30) default NULL,
`asegurado` char(2) default NULL,
`retencion` tinyint(4) default NULL,
PRIMARY KEY (`dni`)
) TYPE=InnoDB;

CREATE TABLE `forma_pago` (
`cod_pago` varchar(12) NOT NULL default '',
`forma_pago` varchar(20) default NULL,
`descripcion` varchar(50) default NULL,
PRIMARY KEY (`cod_pago`)
) TYPE=InnoDB;

[xanful]

Buenas aviana, aunque sean claves ajenas a esta tabla tambien han de ser declarados los campos como si fueran de esa misma tabla.

Pasate de todas formas por aqui que yo tambien tuve problemillas con ese tema y gracias a ese ejemplo lo consegui solucionar

http://www.forosdelweb.com/f21/claves-ajenas-mysql-220538/

Salu2

[aviana]

te agradezco tu respuesta, pero creo que no es ese el problema. Si que me deja crear dos claves externas en una tabla, el problema es que quiero que un mismo campo sea clave externa de 2 tablas. Concretamente, el campo cod de la tabla usa puede ser tanto un codigo de cliente como uno de proveedor. Es por eso que pongo que cod sea clave externa al campo dni de la tabla cliente y también clave externa al campo cod_prov de la tabla proveedor. Gracias y salu2

[Vice]

Aviana, el problema que te da al insertar un proveedor o un cliente válido es muy sencillo: si es un proveedor no es un cliente (falla la foreign key sobre clientes) y si es un cliente no es un proveedor (falla la foreign key sobre proveedores).
¿Por qué no te planteas una única tabla para proveedores y clientes con un atributo que te indique que tipo de individuo es?. De esta manera la clave foránea sólo tiene que cumplirse contra una tabla. Además para no tener tantos campos infrautilizados, te creas una tabla complementaria para los datos de un cliente que no tienes para un proveedor.
Un saludo.

[aviana]

podría ser una solución lo que tu dices, pero complicaría un poco el diseño (parece ya una sábana mi ER) ¿se podría hacer algo como que comprobara si está en una tabla o en la otra o hacer una consulta sobre ambas antes de aplicar la restricción de la clave primaria?Gracias y salu2

[Vice]

Realmente lo que te propongo no te complica más tu ER: de tres tablas pasas a tres tablas, pero estructuradas de otra manera.

Por otro lado, si quieres mantener la estructura que tienes, tendrás que comprobar la existencia en una de las tablas antes de insertarlo para asegurarte que se cumple la norma. Y lo mismo para borrar un registro de clientes o proveedores, pues tendrás que comprobar que no exista ningún registro en usa antes de borrarlo.

Mi propuesta sería la siguiente:
CREATE TABLE `usa` (
`cod` varchar(15) NOT NULL default '',
`cod_pago` varchar(12) NOT NULL default '',
PRIMARY KEY (`cod`,`cod_pago`),
KEY `cod_pago` (`cod_pago`),
CONSTRAINT `usa_usuarios` FOREIGN KEY (`cod`) REFERENCES `usuarios` (`cod_prov`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `usa_forma_pago` FOREIGN KEY (`cod_pago`) REFERENCES `forma_pago` (`cod_pago`) ON DELETE CASCADE ON UPDATE CASCADE,
) TYPE=InnoDB;


CREATE TABLE `usuarios` (
`cod_prov` varchar(15) NOT NULL default '',
`dni` varchar(50) NOT NULL default '',
`nombre` varchar(30) NOT NULL default '',
`direccion` varchar(30) NOT NULL default '',
`localidad` varchar(20) NOT NULL default '',
`provincia` varchar(20) NOT NULL default '',
`telefono` varchar(9) NOT NULL default '',
`fecha_nac` date NOT NULL default '0000-00-00',
`sexo` char(1) NOT NULL default '',
tipo char(1) not null,
PRIMARY KEY (`cod_prov`)
) TYPE=InnoDB;

CREATE TABLE `cliente` (
`cod_prov` varchar(9) NOT NULL default '',
`razon_social` varchar(30) NOT NULL default '',
`cod_contable` int(6) NOT NULL default '0',
`riesgo_max` tinyint(4) default NULL,
`riesgo_act` tinyint(4) default NULL,
`tarifa` varchar(9) NOT NULL default '',
`devoluciones` tinyint(4) default NULL,
`movil` varchar(9) default NULL,
`cobros_no_vencidos` tinyint(4) default NULL,
`pais` varchar(15) default NULL,
`recargo` char(2) default NULL,
`cod_postal` smallint(6) default NULL,
`observaciones` varchar(100) default NULL,
`acumulados` smallint(6) default NULL,
`alta` date default NULL,
`correoe` varchar(30) default NULL,
`actividad` varchar(30) default NULL,
`fax` varchar(9) default NULL,
`asegurado` char(2) default NULL,
`retencion` tinyint(4) default NULL,
PRIMARY KEY (`cod_prov`)
CONSTRAINT `cli_usuarios` FOREIGN KEY (`cod_prov`) REFERENCES `usuarios` (`cod_prov`) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;

donde usuarios.tipo sería el campo que indica que tipo de usuario es: provedor o cliente.
Por otro lado: no uses nunca el DNI como clave única, es falso que el DNI sea único, no son muchos los casos pero hay DNI's duplicados.

Un saludo.