Add 2FK in a single table

Question

Add 2FK in a single table

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have these tables:

mysql> describe clientes;
+----------------+-------------+------+-----+---------+----------------+
| Field          | Type        | Null | Key | Default | Extra          |
+----------------+-------------+------+-----+---------+----------------+
| id_cliente     | int(11)     | NO   | PRI | NULL    | auto_increment |
| nombre_cliente | varchar(25) | YES  |     | NULL    |                |
| calle_cliente  | varchar(40) | YES  |     | NULL    |                |
| ciudad_cliente | varchar(12) | YES  |     | NULL    |                |
+----------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> describe saldo;
+---------------+------------+------+-----+---------+-------+
| Field         | Type       | Null | Key | Default | Extra |
+---------------+------------+------+-----+---------+-------+
| numero_cuenta | varchar(5) | NO   | PRI | NULL    |       |
| saldo         | int(11)    | YES  |     | NULL    |       |
+---------------+------------+------+-----+---------+-------+

But I want to make a table with the primary keys of each table, It can? If so, how?

mysql

asked by AldoMunoZz 13.05.2018 в 10:03

source

2 answers

0

Estimated in a table there should always be a primary key, so if you want to have the primary key of each table in a table you have to pass it as a foreign key.

answered by 13.05.2018 в 10:04

Subtract a string New or previous value in script according to input

score 1 · Accepted Answer

It can and is called a many-to-many relationship, it is done as follows:

CREATE TABLE cliente_saldo(
   id INT PRIMARY KEY AUTO_INCREMENT, 
   cliente_id INT NOT NULL,
   saldo_id INT NOT NULL,
   CONSTRAINT fk_cliente_saldo_clientes FOREIGN KEY(cliente_id) REFERENCES clientes(id) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT fk_cliente_saldo_saldos FOREIGN KEY(saldo_id) REFERENCES saldos(id) ON DELETE CASCADE ON UPDATE CASCADE
);

EXPLANATION.

client_id = It is the field that will act as a foreign key and since we are going to link it with an INT type id, that's why I also   I declared as INT; this same situation explains the creation of balance_id

customer_slider_clients and customer_slide_slots are the necessary aliases that each of the foreign keys must carry for   can identify them; Check how each one starts with the name of the   new table and then by the name of each table that I do   reference

When I use ONDELETE CASCADE ON UPDATE CASCADE, I am handling referential integrity to know what happens when I delete a node   parent and what will happen to the child node associated with that parent node.

Here more info on the referential integrity

CASCADE: Deletes the records in table B, when the   record of table A; on which they depend if the sentence is DELETE   and modifies the records in table b when the record in the table   A is modified in the UPDATE statement