* Delete and recreate primary key *.md

Delete and recreate primary key

Delete a row with a primary key, recreate that row with the same primary key.

Preparation

mysql -u root
# Welcome to the MySQL monitor.  Commands end with ; or \g.
# Your MySQL connection id is 26
# Server version: 5.6.51 Homebrew
#
# Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.
#
# Oracle is a registered trademark of Oracle Corporation and/or its
# affiliates. Other names may be trademarks of their respective
# owners.
#
# Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

CREATE DATABASE fancy_test;
-- Query OK, 1 row affected (0.00 sec)

USE fancy_test;
-- Database changed

CREATE TABLE processes (
  id INT NOT NULL AUTO_INCREMENT,
  name varchar(255),
  PRIMARY KEY (id)
);
-- Query OK, 0 rows affected (0.03 sec)

CREATE UNIQUE INDEX id_index ON processes (id);
-- Query OK, 0 rows affected (0.03 sec)
-- Records: 0  Duplicates: 0  Warnings: 0

Experiments

INSERT INTO processes (id, name) VALUES (1, 'SCRUM');
-- Query OK, 1 row affected (0.00 sec)

INSERT INTO processes (id, name) VALUES (2, 'SAFe');
-- Query OK, 1 row affected (0.00 sec)

DELETE FROM processes WHERE id = 1;
-- Query OK, 1 row affected (0.00 sec)

SELECT * FROM processes;
-- +----+------+
-- | id | name |
-- +----+------+
-- |  2 | SAFe |
-- +----+------+
-- 1 row in set (0.00 sec)

INSERT INTO processes (id, name) VALUES (1, 'SCRUM');
-- Query OK, 1 row affected (0.00 sec)

SELECT * FROM processes;
-- +----+-------+
-- | id | name  |
-- +----+-------+
-- |  1 | SCRUM |
-- |  2 | SAFe  |
-- +----+-------+
-- 2 rows in set (0.00 sec)

Cleanup

DROP TABLE processes;
-- Query OK, 0 rows affected (0.00 sec)

DROP DATABASE fancy_test;
-- Query OK, 2 rows affected (0.00 sec)

Summary

It works without any problems.