How to delete an Id and restructure the database

Question

How to delete an Id and restructure the database

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I have the following problem and I do not know if there is a function in php to do this or in what way I can fix it.

I have a database in which I will search for records that match a search criteria, when I receive it I will have something like this:

id  total  pagado
1   $100   1
3   $400   1
7   $500   1

I want to delete those records, but I have to leave the consecutive IDs, and these in the search will be skipped, in what way can I do it with PHP, is there any function or function that they know?

Greetings!

sql php

asked by Alejandro Godinez 19.02.2018 в 06:42

source

2 answers

Why can not the JSON database value be decoded in PHP? Get the name of a textbox and save it in an MS Visual Basic array?

score 1 · Answer 1

You can try this:

ALTER TABLE companies
DROP PRIMARY KEY,
CHANGE id id int(11),
ADD PRIMARY KEY (uuid);

More or less that is the idea, you should delete the current ID column and then re-create a new id column, for example uuid, as primary key and autoincrement.

I hope you help greetings.

score 1 · Answer 2

The solution could be to eliminate the id field completely and re-add it as AUTOINCREMENT so that it is reordered with new values.

The problem I see is that if the values of that field are referenced from other tables you will have problems with relational integrity.

To carry out the described task you will need to execute:

ALTER TABLE test DROP id;
ALTER TABLE test
  ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST;

You can see an online example of the operation in the next link .

There are other alternatives:

Decrease the values higher than those eliminated in one to compact each element removed.
Fill in the deleted items by assigning that value to the record that contains the maximum current value.

But all these solutions are, in my opinion, bad ideas if we then want to reference the records unequivocally through that field (and if not, maybe it does not make sense to add it).