[MDEV-21181] Automatic invisible primary key Created: 2019-11-29  Updated: 2023-11-30

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: None

Type: New Feature Priority: Major
Reporter: MG Assignee: Unassigned
Resolution: Unresolved Votes: 2
Labels: beginner-friendly

Issue Links:
Relates
relates to MDEV-28412 Introduce sql_require_primary_key Open
relates to MDEV-10177 Invisible columns Closed
relates to MDEV-19506 Remove the global sequence DICT_HDR_R... Closed
relates to MDEV-24001 Implement hidden PK for RBR of no-uni... Open
relates to MDEV-24830 Write a warning to error log if Galer... Closed
relates to MDEV-25551 Assertion `mode_ == m_local || transa... Closed
relates to MDEV-30430 Enabling system versioning on tables ... Closed

 Description   

Feature request - new option innodb_automatic_pk (default off, global/session)

During CREATE TABLE for InnoDB, automatically translate

-- FROM:

CREATE TABLE t1 (c2 INT) ENGINE=InnoDB;

-- TO:

CREATE TABLE t1 (_inv_PK SERIAL INVISIBLE PRIMARY KEY, c2 INT) ENGINE=InnoDB;

-- FROM:

ALTER TABLE t1 ADD PRIMARY KEY (`c2`);

-- TO:

ALTER TABLE t1 DROP COLUMN IF EXISTS _inv_PK, ADD PRIMARY KEY (`c2`);

It might be a good idea to modify or suppress warning "Can't DROP COLUMN `_inv_PK`" and to issue a warning that _inv_PK was created.



 Comments   
Comment by Daniel Black [ 2022-06-02 ]

This would really help app frameworks that aren't good at doing it all at once.

Comment by Marko Mäkelä [ 2022-07-26 ]

MySQL 8.0.30 includes WL#13784 - Create auto_increment PK on table creation when no PK is specified.

Generated at Thu Feb 08 09:05:11 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.