RENAME CONSTRAINT

On this page

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

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

> CREATE TABLE logon (
    login_id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    sales_id INT,
    UNIQUE (customer_id, sales_id)
  );

> 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)

> ALTER TABLE logon RENAME CONSTRAINT logon_customer_id_sales_id_key TO unique_customer_id_sales_id;

> 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)

Pricing

Contact us

Sign In