Redo autoincrement on Sqlite

Asked

Viewed 656 times

1

I am passing the data from an old bank to a new bank. After running all the querys there were some primary key fields that were "missing". Example by column id jumped from data '11' to data '13' because data '12' has been deleted. This occurs in several cases in the database. How to delete data IDs and remake them to follow a correct order?

sql sqlite auto-increment
  • 1

    No database references to those Ids? that is, since each ID is associated with an entry/row of the table, changing the ID can cause other tables to stop working because they lose the reference. Better than erasing is making one soft-delete, which is basically a flag, one of the fields where the off or active status is stored.

  • It is only a single table without foreign key, tidying up the Ids would help me to do the data Sort in the future. I don’t know soft-delete, but I think it helps my problem. Obg

  • 1

    How is the table structure? How was the original data copied to the table? Won’t you have the problems reported by Sergio? What you’re looking for shouldn’t help with Sort some, and if it helps I’m afraid of what you’re doing.

  • The structure was previously intact, with correct auto-increment, but with Rows without data, I delete them. The structure of the table is this tabela, in short the absence of the ID would hinder the word search through the Sort I made in python.

  • 1

    In principle removing the jump in Ids seems counterproductive. You don’t get anything out of it, and you lose your relationship with the old DB if in the future you need a conference call. Of course, it is only you who really knows your motivation, but for me it gave the impression of something motivated by "superstition", and this does not go with programming. Obviously it’s just an impression, maybe there’s some legitimate reason you didn’t mention.

  • 1

    You have not shown the table structure but the data contained in it. You may not know the difference between them. What worries me even more is to redo the numbering. This has implications that I have the impression that I do not know.

  • The real reason to want everything ordered is literally the one function Andom will give a number and through this number do the search, wanted to avoid doing several searches to the bank, if the sought number had an empty Row.

  • As I am with the creation scripts of this new database the deleted data could be undone.

  • I re-executed the bank generation scripts by removing the labels. It’s +/- like this: table Then the best solution would be to make a new search if the data sought was ''?

  • 1

    Leonardo, if you do the Random correctly, you will not use the Ids for anything. Sort by Random and limit to 1 only, the ID is irrelevant.

  • 1

    If the table is big, you can use something like LIMIT 1 OFFSET RANDOM() * quantidade_de_registros not to index the entire table

Show 6 more comments

1 answer

3


The question doesn’t help much but I would try to do this in importing the data into the new table:

INSERT INTO novatabela (word, definicao, classe_gramatical)
    SELECT word, definicao, classe_gramatical FROM tabelaantiga

I put in the Github for future reference.

Leave without the word_id, so the numbering will be done from scratch. Ensure that the table has just been created. If you need to do this in a temporary table, delete the old one and rename the temporary one to replace the old one.

Stay tuned for comments on the question because changing a primary key can have disastrous consequences for the database.

Depend on it not to have holes in us ids is not usually a good idea. You will have to guarantee this, which is not easy. Again has comments giving better solution.

Ideally not to use AUTOINCREMENT for anything in Sqlite.

  • Several times I read this linked page, and each time I read it, I find something else that had escaped from the previous reading. Summary, item 1: AUTOINCREMENT usually not required; item 2: column INTEGER PRIMARY KEY is an alias for ROWIDP ; item 3: no INSERT, se não for fornecido ROWIDouINTEGER PRIMARY KEY, o valor será inferido automaticamente com algum inteiro não utilizado. O único motivo que justifica usar AUTOINCREMENTsegundo o SQLite é o item 4: o propósito doAUTOINCREMENTé evitar o reuso deROWID’s old already deleted

  • 1

    It’s the Sqlite doc story, there’s "everything" there, but you have to pay close attention.

Browser other questions tagged sql sqlite auto-increment

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