How can I never mysql primary key ever repeat even after deleting it

Question

I inherited a rather poorly designed mysql database that does not have true foreign keys between the user table and the preference table.

Consider

CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(255) NOT NULL COMMENT 'email address', ... ) 

and

 CREATE TABLE `preference` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) NOT NULL, ... ) 

If I delete a row from the user table, this will cause the preference table to become an orphan. However, with the primary key specified above in mysql, as soon as I add a new row to the user table, it will actually use the old identifier from the previously deleted row - thus, referring to the preferences of old users (very undesirable).

Despite a poor implementation (yes, I know that the right thing is to reorganize the code and update the database with the true foreign key relationships), is there a way to ensure that the primary key is never reused?

Answer 1

You can create a constraint to set the foreign key to null after writing que with the corresponding primary key. This can be achieved using the following:

 ALTER TABLE `fk_table` ADD CONSTRAINT `fk_constraint_name` FOREIGN KEY(`fk_field`) REFERENCES `pk-table` (`pk`) ON DELETE SET NULL ON UPDATE SET NULL 

As said in the comments on your question, MySQL does not reuse values ​​from auto_increment .