I have a table a with a varhar array on id in b table. For example (1,2,3,4,5,6). When in the b table I delete the 5 and 6 elements (last) then when adding new elements to the b table, the new id does not create 7,8,9 ... but 5,6,7,8 ... and it turns out that in the table а 5 and 6 are no longer on that row. You can of course also clean the array in а , but can you make the increment unique?

  • 2
    And why do you assume that in the second table there would be records pointing to already deleted rows? you need to either delete such records, or update their reference to NULL when deleting the record from the parent table. You can do this automatically - by specifying the keyword on delete cascade or on delete set null when creating a table link ( mysql.ru/docs/man/SEC451.html - Mike
  • Yes, it would be possible. But I do not have a foreign key, but just a string array on different id in table b , but can I make a foreign key array? - Bogdan Shulga
  • one
    Well, arrays are not allowed in the database at all, relational bases are not designed for this. Take out in a separate table. - Mike

3 answers 3

You are faced with some classic problems, which I will describe quickly (and, perhaps, someone will have time to paint in more detail):

The automatic increment continues its work from the last value, leaving gaps in the remote values.

This is absolutely normal. It is necessary to suppress the internal perfectionist and leave the gaps as is. Nothing wrong with that, the range of values ​​for a standard int will suffice for any project other than the visual discomfort of the few who will track the ID in the address bar, this will bring nothing.

The project is required to store a list of references to entities, so I store them as a string of values, separated by a comma.

Such an approach will not allow you to use a relational database properly. The correct approach in this case will be to create a new table in which to describe the relationship.

Wrong:

 TABLE students id | first_name | last_name 1 | Vasya | Pupkin 2 | Vitaliy | Merkushev TABLE teachers id | first_name | last_name | students 1 | L'enfant | Terrible | '1,2'

Right:

 TABLE students id | first_name | last_name 1 | Vasya | Pupkin 2 | Vitaliy | Merkushev TABLE teachers id | first_name | last_name 1 | L'enfant | Terrible TABLE teacher_students # таблица связей, которая описывает, какие именно студенты обучаются teacher_id | student_id 1 | 1 1 | 2 # Кто учится у конкретного преподавателя? SELECT students.* FROM teacher_students INNER JOIN students ON teacher_students.student_id = students.id WHERE teacher_id = 1

Formally, this is described by the so-called. normal database forms that you can read about in wikipedia

I need to update entity references as their identifiers change

Especially for this, a foreign key mechanism was invented (foreign key), which allows you to perform a basic set of actions when changing a related entity, for example, to prohibit the teacher being deleted while he has students, or vice versa, to delete a connection record in case of a student deletion.

  • Thank you, individually described. But it is not clear with the Автоматический инкремент продолжает свою работу с последнего значения, оставляя зазоры в удаленных значениях since after restarting the database (restarting the computer) the increment starts from the last available, not from the last added (which could be deleted) - Bogdan Shulga
  • @BogdanShulga in most cases, the server does not restart for months. Just do not think about it. - etki

The autoincrement logic on the InnoDB engine is as follows

  • when starting the server, find the maximum id in the table and remember it
  • when inserting a new record, increase the saved value by 1 and insert

Thus, your situation will appear only when the server is restarted . To bypass this mechanism, add an auxiliary table with one auto-increment column, and remove the auto-increment in your table. Then the insertion of a new record will look like this.

 INSERT INTO gen (id) VALUES (NULL); INSERT INTO mytable (id, .....) VALUES (LAST_INSERT_ID(), ......);

And you will only delete records from the main table.

Periodically, the auxiliary table can be cleared

 DELETE FROM gen HAVING id < MAX(id);

    Here is the answer to your question: https://stackoverflow.com/questions/3718229/stop-mysql-reusing-auto-increment-ids . In a nutshell: in this case, simply discard the use of auto-increment and store in the separate table the last value for the key, changing it when adding new data.

    • In extreme cases, I thought about this option. Thank you - Bogdan Shulga