Hi, I'm designing a sales database.
But I have a problem. I need that when inserting data in my table, it looks like this:
Table detail_sales
Now, the id of my sale_file has to travel to my sale table as a foreign key:
The problem is that to insert data in my table detail_sales the id does not have to be repeated, because it is the primary key, and only as a primary key I can send it to the other table. I would appreciate any suggestions.
"id_det_venta" in the table "detalle_venta" is primary key and can not have repeated (so I do not understand what "id" is in the example that you put at the beginning).
"id_det_venta" in "venta" is a foreign key, it can be repeated as necessary; what you indicate with that relationship is that each "sale" has only one and only "detail_sales" related.
If what you want is that each "sale" can have several "detail_sales" related, you must have an intermediate table that stores that relationship.
Something like:
tabla "venta_detalleVenta"
id_venta int(11)
id_det_venta int(11)
where both fields ( id_vent to e id_det_venta ) are primary keys in that table.
So, every time you want to establish the relationship of a "sale" with a "sale_detail" you only have to save both id in the table "venta_detalleSell"; with so many "detail_sales" for "sale" as you need.
The PK (id_det_venta) of your detail_sales table must be FK in the sale table. Thus, each sale has a detail (since the FK is the PK of the other table). This guarantees you the uniqueness. Actually you do not send it to the other table, but from the sales table you make reference to the PK of the table detalle_venta that makes FK.