Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21181

Automatic invisible primary key

Details

    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.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-28412 Introduce sql_require_primary_key

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Task - A task that needs to be done. MDEV-10177 Invisible columns

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-19506 Remove the global sequence DICT_HDR_ROW_ID for DB_ROW_ID

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Task - A task that needs to be done. MDEV-24001 Implement hidden PK for RBR of no-unique key table

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-24830 Write a warning to error log if Galera replicates InnoDB table with no primary key

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-25551 Assertion `mode_ == m_local || transaction_.is_streaming()` failed in BF abort

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-30430 Enabling system versioning on tables without primary key breaks replication

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            danblack Daniel Black added a comment -

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

            danblack Daniel Black added a comment - This would really help app frameworks that aren't good at doing it all at once.
            marko Marko Mäkelä added a comment -

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

            marko Marko Mäkelä added a comment - MySQL 8.0.30 includes WL#13784 - Create auto_increment PK on table creation when no PK is specified .
            ib-mlatin Matthew Latin added a comment -

            This would definitely help quite a bit since there are a lot of frameworks that habitually do not use PKs. Having to modify apps to add dummy PKs is not very feasible sometimes.

            ib-mlatin Matthew Latin added a comment - This would definitely help quite a bit since there are a lot of frameworks that habitually do not use PKs. Having to modify apps to add dummy PKs is not very feasible sometimes.
            SylvainArbaudie Sylvain ARBAUDIE added a comment - - edited

            is it possible to have something lighter than auto increments ? they are actually quite heavy and cause issues with all replication systems

            coiuld we not use a regular hidden column, and auto create a sequence ? that would be easier to manage than auto increments.
            something like this :

            PK int default sequence.nextval() invisible key

            SylvainArbaudie Sylvain ARBAUDIE added a comment - - edited is it possible to have something lighter than auto increments ? they are actually quite heavy and cause issues with all replication systems coiuld we not use a regular hidden column, and auto create a sequence ? that would be easier to manage than auto increments. something like this : PK int default sequence.nextval() invisible key

            People

              Unassigned Unassigned
              mg MG
              Votes:
              4 Vote for this issue
              Watchers:
              14 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.