Place several foreign keys to a primary

Question

Place several foreign keys to a primary

Navigation

#1 by (3 votes)

1

I'm starting with SQL and I wanted to know if it is possible to place several foreign keys from one table to a primary key from another table, I have something like this

create table producto(
llave int primary key auto_increment,
producto varchar (100));

create table ventas(
clave int primary key auto_increment,
fk_producto1 int,
fk_producto2 int,
fk_producto3 int,
fk_producto4 int,
fk_producto5 int,
foreign key (fk_producto1) references producto (llave),
foreign key (fk_producto2) references producto (llave),
foreign key (fk_producto3) references producto (llave),
foreign key (fk_producto4) references producto (llave),
foreign key (fk_producto5) references producto (llave));

The code is in MySql and if you can run it without any problem, but I can not make the query and leave me data that are outside the primary key of the product table, I appreciate

mysql

asked by Nestor Bautista 13.09.2018 в 15:18

source

1 answer

What Happens with the Values of Screen Halves? Null value in MySQL with PHP

score 3 · Accepted Answer

Based on what you explained in the comments and what you see in the logic of your question, my recommendation is to do it this way:

CREATE TABLE facturas (
    id INT AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE producto (
    id INT AUTO_INCREMENT,
    producto VARCHAR(100),
    PRIMARY KEY (id)
);

CREATE TABLE ventas (
    id INT AUTO_INCREMENT,
    id_producto INT,
    id_factura INT,
    PRIMARY KEY (id),
    CONSTRAINT fk_producto_ventas FOREIGN KEY (id_producto)
    REFERENCES producto(id),
    CONSTRAINT fk_facturas_ventas FOREIGN KEY (id_factura)
    REFERENCES facturas(id)
);

It is not necessary to create so many columns to register the quantity of products of an invoice, it is enough to have 2 columns one that contains the products and another that contains the invoice number. With that, you can now work on queries and relate them as you wish.