2
I have been looking for something to solve such problem that I have been finding in other systems that I developed, I would like to know a solution for how to make an id auto increment, but an auto increment related to another field, for example: I have my product table and these products are categorized, so they have a id_categoria, referring to the table of categories and my id of the product table is auto increment, and I have some records in the product table that would be like this for example:
id | id_categoria
1 | 1
2 | 3
3 | 2
4 | 1
5 | 2
6 | 1
7 | 4
...
But I would like this auto increment to happen only with respect to category, for example for the category of id_category I have the product id 1,2,3,4,5, knowing that I would have to have a composite primary key, having the product id and category id as primary keys, my table would look something like this:
id | id_categoria
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
3 | 1
...
But without me having to first consult the bank and then add the desired id, something that was automatically done by the bank, someone knows some solution for this?
Even if there were - I don’t know if there are -, the bank would be doing just that "consulting the base to see what number to generate". Why don’t you create a function to do this? This way you can put it in your Inserts or updates and it will look like it is a native function of the database.
– Reginaldo Rigo
He came to see How auto increment works with composite keys in Myisam engine? ? that’s what you want?
– rray
@Reginaldo-Rigo so ok, I can make a Rigger, I’ve used this way, but I wonder if you have something already native of the bank that helps in this.
– Leonardo Patricio
@rray this not, because there is passing the product id already in the query, for this should perform a query before and as said in the question do not want to realize this.
– Leonardo Patricio
The result you want in the second illustration would be in the product table, the category table or a third table of the relationship between the two?
– Jao Assy
In the product table...
– Leonardo Patricio
From what I understand, you are creating a relationship N:N, ie 1 product can have N categories, and a certain category can be linked to N products, in this case the best model is the creation of 3 tables, tbl_product, tbl_category, tbl_producto_category with the id_product and id_category fields being both a composite key, thinking in this model the products will be registered and the categories too, what you need is to link to each other, so it doesn’t make much sense what you want to do in my point of view, I understand that you are totally running away from the pattern.
– Raphael Godoi