Como crear 2 tablas con foreign key. Conectado campos que no son PRIMARY KEY

[eybel]

Tengo este codigo pero tengo problemas con el FOREIGN KEY. Tengo una tabla "CLIENTES" y otra "CUENTAS"

Tengo que ingresar clientes en esta tabla "CLIENTES" (1 record por persona) y cada cliente puede tener multiples records de cuentas "CUENTAS" puede tener 0 o 100 cuentas cada cliente por dar un ejemplo..

Este codigo es el que cree, pero algo estoy haciendo mal me da errores de FOREIGN KEY..

Puedo tener 2 tablas, con 2 PRIMARY KEYS y en la tabla CLIENTES tener otro campo como FOREIGN KEY que estaria conectado a la seguna tabla?

ASI:


create table clientes (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
clientes_id VARCHAR (100),
name VARCHAR (50),
lastname VARCHAR (50),
bdate DATE,
sex CHAR (1),
email VARCHAR (100),
phone INT (20),
street VARCHAR (100),
apt CHAR(7),
city VARCHAR (50),
state CHAR (2),
zipcode SMALLINT (10),
ethnicity CHAR (20),
FOREIGN KEY (clientes_id) REFERENCES cuentas (clientes_id)
);

create table cuentas (
cuentas_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (cuentas_id),
clientes_id VARCHAR (100) ,
company VARCHAR (100),
aname VARCHAR (100),
phone VARCHAR (100),
email VARCHAR (100),
debt INT,
status VARCHAR (100)
);


Que esta mal aca? Estoy conectado los campos "clientes_id" que no son PRIMARY KEY como FOREIGN KEYS

[gnzsoloyo]

1) Una foreign key es, por definición, un campo o conjunto de campos cuyo valor se referencia a la clave primaria de otra tabla. En este contexto, no puedes crear una FK que no apunte a una PK.
2) MySQL provee una extensión a esta regla que consiste en que puedes apuntar una FK a un campo o conjunto de campos que no sean PK, en tanto ese campo (o conjunto) tenga definido un indice UNIQUE en su tabla. En los hechos, implica que ese campo referido, en su tabla, debe cumplir con uno de lo los requerimientos de una PK: tener un valor único en esa misma tabla.

Lo que estás haciendo mal es intentar hace run mal diseño de la base, por cuanto una FK indica una dependencia entre una tabla respecto de otra, y no es posible que la existencia de un cliente dependa de que exista la cuenta. En todo caso la cuenta pertenece al cliente y no al revés.

Por cierto: No tiene sentido ni utilidad relacionar en forma simétrica un cliente con su cuenta. Es decir: Si la cuenta pertenece al cliente, la cuenta lleva como FK el ID del cliente, pero el cliente no debe llevar como FK el ID de la cuenta, porque se generaría una relación circular, imposible de cumplir.

Si una cuenta tiene más de un titular, requiere una tabla adicional para administrar la relación N:N.

[eybel]

Si me di cuenta de eso, y llegue a poner la FK en la tabla "cuentas" asi:

create table clientes (
id INT NOT NULL AUTO_INCREMENT,
clientes_id VARCHAR (100),
name VARCHAR (50),
lastname VARCHAR (50),
bdate DATE,
sex CHAR (1),
email VARCHAR (100),
phone INT (20),
street VARCHAR (100),
apt CHAR(7),
city VARCHAR (50),
state CHAR (2),
zipcode SMALLINT (10),
ethnicity CHAR (20),
PRIMARY KEY (id)
);

create table cuentas (
cuentas_id INT NOT NULL AUTO_INCREMENT,
clientes_id VARCHAR (100) ,
company VARCHAR (100),
aname VARCHAR (100),
phone VARCHAR (100),
email VARCHAR (100),
debt INT,
status VARCHAR (100),
PRIMARY KEY (cuentas_id),
FOREIGN KEY (clientes_id) REFERENCES cuentas (clientes_id)
);

Pero si quiero conectar la tabla "Clientes" con la tabla "Cuentas" con la regla que mencionaste. "cuentas" me quedaria asi:

create table cuentas (
clientes_id VARCHAR (100) ,
company VARCHAR (100),
aname VARCHAR (100),
phone VARCHAR (100),
email VARCHAR (100),
debt INT,
status VARCHAR (100),
FOREIGN KEY (clientes_id) REFERENCES clientes (clientes_id)
);

Aca estoy poniendo como FK el id del clientes. Sigo haciendo algo mal. Tengo que poner Primary Key en las 2 tablas? o en una tabla PRIMARY KEY Y EN LA OTRA NO MAS FOREIGN KEY? ya me confundi mas.

Yo tengo entendido que todas las tablas necesitan PRIMARY KEY y que para conectarlas necesito un FOREIGN KEY en la TABLA HIJO

[gnzsoloyo]

Estás omitiendo un detalle importantísimo:
Los campos usados de FK deben ser del mismo tipo y rango que las PK a las que se referencian. Y tu estás poniendo INT en uno y VARCHAR en el otro...

[eybel]

'clientes_id' en cuentas es VARCHAR y en clientes es VARCHAR.

Todavia no me funciona.

1) Necesito tener PK en las 2 tablas?
2) Puede el PK de la segunda tabla o "cuentas" en mi caso ser tambien a la misma vez FK?
3) si el segundo campo de "clientes" que seria 'clientes_id' esta conectado al PK de la tabla "cuentas" no necesito un PRIMARY KEY con auto_increment para distinguir cada record?

Probe asi y tampoco me funciona:

create table clientes (
id VARCHAR (100),
clientes_id VARCHAR (100),
name VARCHAR (50),
lastname VARCHAR (50),
sex CHAR (1),
email VARCHAR (100),
phone INT (20),
street VARCHAR (100),
apt CHAR(7),
city VARCHAR (50),
state CHAR (2),
zipcode SMALLINT (10),
ethnicity CHAR (20),
PRIMARY KEY (id)
);

create table cuentas (
clientes_id VARCHAR (100),
company VARCHAR (100),
aname VARCHAR (100),
phone VARCHAR (100),
email VARCHAR (100),
debt INT,
status VARCHAR (100),
PRIMARY KEY (clientes_id),
FOREIGN KEY (clientes_id) REFERENCES clientes (clientes_id)
);

[gnzsoloyo]

1) ¿Por qué tienes una PK "ID" y un campo "CLIENTE_ID" en la tabla Clientes? Es redundante e innecesario.
2) La PK de "Clientes" es "ID", no "Cliente_id", y Cliente_Id no está definido como UNIQUE en lo que estás mostrando. Sin eso una FK fallará.

Tengo que poner Primary Key en las 2 tablas? o

Toda tabla tiene que poseer su propia PK, sea que la herede (relación identificatoria), que la herede parcialmente (relación de dependencia funcional) o que sea propia de esa tabla.
Es un requisito del modelo E-R aplicado a las bases de Datos Relacionales.

[eybel]

ID porque seria un auto_increment, lei en varios lados que no es bueno usar ID como PRIMARY KEYS.. el "clientes_id" es como nombre de usuario que uno pondria seria unico... EL PK de clientes es "ID" correcto. Voy a probar con UNIQUE

[gnzsoloyo]

En ese caso, vuelale ese campo y declara a cliente_id como PK.

[eybel]

ya esta elimine ID y puse clientes_id como PK ahora funciono.
gracias por la ayuda!

Pregunto algo mas:

Necesito poner ON UPDATE CASCADE ON DELETE CASCADE cuando creo el FK en "cuentas" para que cuando borre un clientes se borren todas las cuentas?

[gnzsoloyo]

Necesito poner ON UPDATE CASCADE ON DELETE CASCADE cuando creo el FK en "cuentas" para que cuando borre un clientes se borren todas las cuentas?

Si no lo pones, no te dejará borrar al cliente.
Pero esa es una decisión de diseño de sistema, porque normalmente, para preservar la integridad histórica de las transacciones, los clientes no se borran. Se desactivan.
Esto suele hacerse poniendo un campo adicional que se llama "fechaBaja", y que contiene NULL cuando el cliente está activo. De ese modo, las consultas historias funcionan y en las actualmente vigentes, se descarta el cliente si la fecha de baja no es NULL.
¿Se entiende la idea?

[eybel]

Si voy a hacer eso, no quiero borrar los clientes quiero desactivarlos para que me quede el historial...

en caso de que agregue una tercera tabla que diga por ejemplo "pedidos" que estaria por debajo de "cuentas" (osea una cuenta puede tener 5 pedidos) el proceso es el mismo? "pedidos" tendria que tener un PK y un FK referenciado al PK de "cuentas" cierto?

[gnzsoloyo]

Exacto.
Ahora bien, si vas a profundizar en este tema, te conviene ir dando una buena leída a los principios generales de las bases de datos: Modelo Entidad-Relación y su aplicación a las Bases de Datos Relacionales, las Dependencias Funcionales, Formas Normales, etc.
TE ira dando un poco de claridad en este tema, que no por nada es una especialidad dentro del desarrollo de aplicaciones.

En Wikipedia estos temas están explicado de una forma clara y simple. Dale una leída.

http://es.wikipedia.org/wiki/Modelo_relacional
http://es.wikipedia.org/wiki/Modelo_...-relaci%C3%B3n
http://es.wikipedia.org/wiki/Base_de_datos_relacional
http://es.wikipedia.org/wiki/Bases_de_datos

[eybel]

Tengo un libro muy bueno en casa. Triste historia es que yo aprendi todo de esto a nivel intermedio y hace un ano estaba fresco me era mas facil. Pero despues de 1 ano sin usar base de datos, se me olvidaron los principios y todo esto es de memoria practicamente. Tengo que volver a retomar. Gracias por tu ayuda!