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

UPSERT (ON DUPLICATE KEY) with multiple UNIQUE columns

    XMLWordPrintable

Details

    Description

      As we know from the documentation on UPSERTs: "It is not recommended to use this statement on tables with more than one unique index."

      Well, this is an understatement. The "not recommended" part really means, that rows may be overwritten randomly and unexpectably. This happens silently with a success message, which is the worst part .

      Problem ist, that

      • not a combination of all unique keys is checked for a match
        OR
      • not a relevant unique key can be specified.
        The duplicate key condition simply hits the first matching row.

      Solution should be in following order:

      • allow to specify which unique keys to check in the ON DUPLICATE KEY section (greetings from Postgres).
      • with a switch allow to only check against the PRIMARY KEY
      • only consider a match, where all unique keys coalesce'd match the insert candidate
      • update the documentation, that really bad things can happen.

      As it is, we have no working UPSERT statement.

      • REPLACE does to much (triggers TRIGGERS, delete related rows with foreign key).
      • UPSERT is useless with two unique keys

      Attachments

        Activity

          People

            Unassigned Unassigned
            ooloo ooloo
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.