New in v19.1: The RENAME CONSTRAINT statement changes the name of a constraint on a column.

Note:

It is not possible to rename a constraint for a column referenced by a view. For more details, see View Dependencies.

Tip:

New in v19.1: This command can be combined with other ALTER TABLE commands in a single statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.

Synopsis

ALTER TABLE IF EXISTS table_name RENAME CONSTRAINT current_name TO name

Required privileges

The user must have the CREATE privilege on the table.

Parameters

Parameter Description
IF EXISTS Rename the constraint only if a constraint of current_name exists; if one does not exist, do not return an error.
table_name The name of the table with the constraint you want to rename.
current_name The current name of the constraint.
name The new name you want to use for the constraint, which must be unique to its table and follow these identifier rules.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Example

Rename a constraint

copy
icon/buttons/copy
> CREATE TABLE logon (
    login_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    sales_id INT,
    UNIQUE (customer_id, sales_id)
  );
copy
icon/buttons/copy
> SHOW CONSTRAINTS FROM logon;
  table_name |        constraint_name         | constraint_type |                details                 | validated
+------------+--------------------------------+-----------------+----------------------------------------+-----------+
  logon      | logon_customer_id_sales_id_key | UNIQUE          | UNIQUE (customer_id ASC, sales_id ASC) |   true
  logon      | primary                        | PRIMARY KEY     | PRIMARY KEY (login_id ASC)             |   true
(2 rows)
copy
icon/buttons/copy
> ALTER TABLE logon RENAME CONSTRAINT logon_customer_id_sales_id_key TO unique_customer_id_sales_id;
copy
icon/buttons/copy
> SHOW CONSTRAINTS FROM logon;
  table_name |       constraint_name       | constraint_type |                details                 | validated
+------------+-----------------------------+-----------------+----------------------------------------+-----------+
  logon      | primary                     | PRIMARY KEY     | PRIMARY KEY (login_id ASC)             |   true
  logon      | unique_customer_id_sales_id | UNIQUE          | UNIQUE (customer_id ASC, sales_id ASC) |   true
(2 rows)

See also



Yes No