Single auto increment on all DB tables

Asked

Viewed 259 times

0

Hello, my DB has several tables that have the same column:

`PROD` int(11) NOT NULL AUTO_INCREMENT

This column is an identifier that makes one that makes an increment with each launch. The issue is that it needs to receive the same value of the other tables, ie it needs to be unique as if this column was the same in all tables.

For example : If I make a release on Table 1 and this column shows the value 125 and after that make the release in table2 that value must be 126, ie it added with the value of the other table even without having received the release, because in my case she is only counting the releases in the table itself without taking into account the releases already made in other.

mysql sql database

  • it seems to me a gambiarra... if you specify what would be Tabela1 and table2, maybe we can better understand what you need.

  • Tabela1 for example would be of purchase and the table 2 of sale for example, if I make a launch in the purchase table the sales table will not receive anything, but would increase its increment by +1 for the two to have the same launch value as the PROD column.

2 answers

2


In postgres, you could use inheritance between tables:

Tabela "Transacoes"
id | data

Tabela "Vendas" que herda "Transacoes"
cliente

Tabela "Compras" que herda "Transacoes"
fornecedor

But in Mysql I think there is no such option. So, it would work with only one table:

"Transacoes"
id   |   data     |   emitente    |    destinatario    |  tipo    | numero
1    | 31/10/2017 |  "Voce"       |   "Ciclano"        |  "Venda" |   1
2    | 30/10/2017 |"Fornecedor X" |   "Voce"           | "Compra" |   1
3    | 29/10/2017 |  "Voce"       |   "Fulano"         |  "Venda" |   2
4    | 28/10/2017 |  "Voce"       |   "Maria"          |  "Venda" |   3
5    | 27/10/2017 |  "Voce"       |   "Joao"           |  "Venda" |   4
6    | 26/10/2017 |"Fornecedor A" |   "Voce"           | "Compra" |   2

. VYou can even change the sequence of another table either by Rigger or by setting a sequence only for the two tables, but I don’t see why do it. If you are going to be on separate tables, you will not be able to make an FK for the key you need.

. Tit is also possible to add a third table that relates the sales codes, with the purchase codes and the products, but it depends on your need and model.

1

The auto increment is individual from each table. To standardize your Ids, you can control through your application or Trigger/function, where you would recover the ID of the generated in the table you want and would update the corresponding column in the other tables. But if it is a relational database, you can think about creating a relationship between them and thus, recover your records more integrated and independent of the ID of each table.

  • Do you think in this case it would be more feasible to do this increment through the code when you launch? I had thought about it but as I have several release options add one code in each would be difficult, yet feasible.

  • If you really need to keep your Ids the same, it would be a practical way to control them, as you could get your records ready on Insert. Remembering that it would be interesting to maintain a transaction to maintain integrity in case of failure in one of the tables.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.