RENAME CONSTRAINT

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

ALTERTABLEIFEXISTStable_nameRENAMECONSTRAINTcurrent_nameTOname

Required privileges

The user must have the CREATE privilege on the table.

Parameters

ParameterDescription
IF EXISTSRename the constraint only if a constraint of current_name exists; if one does not exist, do not return an error.
table_nameThe name of the table with the constraint you want to rename.
current_nameThe current name of the constraint.
nameThe 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

  1. > CREATE TABLE logon (
  2.     login_id INT PRIMARY KEY,
  3.     customer_id INT NOT NULL,
  4.     sales_id INT,
  5.     UNIQUE (customer_id, sales_id)
  6.   );
  1. > SHOW CONSTRAINTS FROM logon;
  1.   table_name |        constraint_name         | constraint_type |                details                 | validated
  2. +------------+--------------------------------+-----------------+----------------------------------------+-----------+
  3.   logon      | logon_customer_id_sales_id_key | UNIQUE          | UNIQUE (customer_id ASC, sales_id ASC) |   true
  4.   logon      | primary                        | PRIMARY KEY     | PRIMARY KEY (login_id ASC)             |   true
  5. (2 rows)
  1. > ALTER TABLE logon RENAME CONSTRAINT logon_customer_id_sales_id_key TO unique_customer_id_sales_id;
  1. > SHOW CONSTRAINTS FROM logon;
  1.   table_name |       constraint_name       | constraint_type |                details                 | validated
  2. +------------+-----------------------------+-----------------+----------------------------------------+-----------+
  3.   logon      | primary                     | PRIMARY KEY     | PRIMARY KEY (login_id ASC)             |   true
  4.   logon      | unique_customer_id_sales_id | UNIQUE          | UNIQUE (customer_id ASC, sales_id ASC) |   true
  5. (2 rows)

See also

Was this page helpful?
YesNo