MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.27


create table EventTypes (id int not null primary key) select 1 id union all select 2 union all select 3;
CREATE TABLE IF NOT EXISTS `Events` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `EventType` INT NULL,
  `timestamp` TIMESTAMP NULL,
  `Comment` TEXT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Events_EventTypes_idx` (`EventType` ASC) VISIBLE,
  CONSTRAINT `fk_Events_EventTypes`
    FOREIGN KEY (`EventType`)
    REFERENCES `EventTypes` (`id`)
    ON DELETE NO ACTION
    ON UPDATE CASCADE)
ENGINE = InnoDB;
insert into Events (EventType) values (1),(1),(1),(2),(3);
CREATE TABLE NewEvents LIKE Events;
show create table NewEvents;
INSERT INTO NewEvents (EventType, timestamp, Comment) SELECT EventType, timestamp, Comment FROM Events ORDER BY id;
show create table NewEvents;
DROP TABLE Events;
CREATE TABLE Events LIKE NewEvents;
show create table Events;
INSERT INTO Events (EventType, timestamp, Comment) SELECT EventType, timestamp, Comment FROM NewEvents ORDER BY id;
show create table Events;
DROP TABLE NewEvents;


Table
Create Table


NewEvents
CREATE TABLE `NewEvents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `EventType` int DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  `Comment` text,
  PRIMARY KEY (`id`),
  KEY `fk_Events_EventTypes_idx` (`EventType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci




Table
Create Table


NewEvents
CREATE TABLE `NewEvents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `EventType` int DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  `Comment` text,
  PRIMARY KEY (`id`),
  KEY `fk_Events_EventTypes_idx` (`EventType`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci




Table
Create Table


Events
CREATE TABLE `Events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `EventType` int DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  `Comment` text,
  PRIMARY KEY (`id`),
  KEY `fk_Events_EventTypes_idx` (`EventType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci




Table
Create Table


Events
CREATE TABLE `Events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `EventType` int DEFAULT NULL,
  `timestamp` timestamp NULL DEFAULT NULL,
  `Comment` text,
  PRIMARY KEY (`id`),
  KEY `fk_Events_EventTypes_idx` (`EventType`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


select * from Events;


id
EventType
timestamp
Comment


1
1
null
null

2
1
null
null

3
1
null
null

4
2
null
null

5
3
null
null


CREATE TABLE NewEvents LIKE Events;
INSERT INTO NewEvents (EventType, timestamp, Comment) SELECT EventType, timestamp, Comment FROM Events ORDER BY id;
DROP TABLE Events;
CREATE TABLE Events LIKE NewEvents;
INSERT INTO Events (EventType, timestamp, Comment) SELECT EventType, timestamp, Comment FROM NewEvents ORDER BY id;
alter table Events auto_increment=0;
DROP TABLE NewEvents;
insert Events (EventType) values (3);
select * from Events;


id
EventType
timestamp
Comment


1
1
null
null

2
1
null
null

3
1
null
null

4
2
null
null

5
3
null
null

6
3
null
null
id	EventType	timestamp	Comment
1	1	null	null
2	1	null	null
3	1	null	null
4	2	null	null
5	3	null	null
id	EventType	timestamp	Comment
1	1	null	null
2	1	null	null
3	1	null	null
4	2	null	null
5	3	null	null
6	3	null	null