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

IF (NOT) EXIST clauses for ALTER TABLE (MWL #252)

    Details

    • Type: Task
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Fix Version/s: 10.0.2
    • Component/s: None
    • Labels:

      Description

      MySQL / MariaDB SQL is great as a functional language: you tell what you want
      and not how to do it. This includes suppressing of errors when the situation you
      want already exists. So there is a CREATE TABLE IF NOT EXISTS statement, for
      example (you say that you want the table to exist, not that it has to be built
      right now). It would be nice if altering a table could be done the same way:

      ALTER TABLE SomeTable ADD COLUMN IF NOT EXISTS SomeColumn ...
       
      ALTER TABLE SomeTable DROP COLUMN IF EXISTS SomeColumn ...
       
      ALTER TABLE SomeTable DROP FOREIGN KEY IF EXISTS SomeConstraint ...

      This would make definition scripts more legible and more friendly to existing
      databases. It would facilitate what I call "repeatable scripts", that can both
      create a new database and update an existing one to the latest structure.

      Mind you, this is already possible using temporary stored procedures (as shown
      in http://www.howtoforge.com/node/4833), but it would make definition scripts
      more maintainable and the SQL language more consistent.

      Specific plan for this task:

      extend the ALTER TABLE statement with the IF [NOT] EXISTS options.
      That involves:

          ALTER TABLE ADD/DROP COLUMN
          ALTER TABLE ADD/DROP INDEX
          ALTER TABLE ADD/DROP FOREIGN KEY
          ALTER TABLE ADD/DROP PARTITION
          ALTER TABLE CHANGE COLUMN
          ALTER TABLE MODIFY COLUMN

      Like CREATE TABLE IF NOT EXISTS, and similar existing commands,
      the ALTER TABLE ... IF [NOT] EXISTS will return warnings instead of errors
      if the modified object (column, index or partition) already exists (doesn't exist yet).
      If the ALTER TABLE command consists of multiple operations, it's not interrupted in the IF [NOT] EXISTS case, but succeedes with the appropriate
      amount of warnings.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                holyfoot Alexey Botchkov
                Reporter:
                ratzpo Rasmus Johansson
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Due:
                  Created:
                  Updated:
                  Resolved: