Auto increment unique in all DB tables

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 increments each launch. The question is that it takes the same value from the other tables, ie it needs to be unique as if this column were the same in all tables.

For example : If I make a post in table1 and that column has the value 125 and after that post in table2 this value should be 126, that is, it added with the value of the other table even without having received the launch, because in my case it is counting only the postings in the table itself without taking into account the postings already made in others.

    
asked by anonymous 31.10.2017 / 14:50

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 believe there is no such option. So I would work with just 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

. V You can even change the sequence of another table by triggering or by setting a sequence for just the two tables, but I do not see why. If they are going to be in separate tables, you will not be able to make an FK for the key you need.

. T You can also add a third table that lists sales, purchasing and product codes, but it depends on your needs and model.

    
31.10.2017 / 15:38
1

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

    
31.10.2017 / 15:05