SQLAnywhere – Rename Foreign Key or Primary Key

sybase-sql-anywhere

I have a database with many duplicate index, foreign key, and primary key names. Duplicate index names can be renamed using:

ALTER INDEX <index name> ON <creator>.<tablename> RENAME TO <new name>

So that's fixed.

But keys are not indexes, so it doesn't work on keys:

"Cannot find index named '<index name>'"

There is a constraint rename syntax:

ALTER TABLE <creator>.<tablename> RENAME CONSTRAINT <constraint name> TO <new name>

This doesn't work on keys either: 

"Constraint '<constraint name>' not found"

Is there any way to rename a foreign or primary key in SQLAnywhere without dropping and recreating the key?

Best Answer

You don't say which version of SQL Anywhere you are using, but in 16 I think you can use ALTER INDEX to do what you want. From the help:

ALTER INDEX statement

Renames an index, primary key, or foreign key, or changes the clustered nature of an index.

Syntax

ALTER { INDEX index-name
| [ INDEX ] FOREIGN KEY role-name
| [ INDEX ] PRIMARY KEY } ON [ owner.]object-name { REBUILD | rename-clause | cluster-clause }

object-name : table-name | materialized-view-name
rename-clause : RENAME { AS | TO } new-index-name
cluster-clause : CLUSTERED | NONCLUSTERED

Parameters

rename-clause Specify the new name for the index, primary key, or foreign key.

When you rename the underlying index for a foreign or primary key, the corresponding RI constraint name for the index is not changed. However, the foreign key role name, if applicable, is the same as the index name and is changed. Use the ALTER TABLE statement to rename the RI constraint name, if necessary.

cluster-clause Specify whether the index should be changed to CLUSTERED or NONCLUSTERED. Only one index on a table can be clustered.

REBUILD clause Use this clause to rebuild an index, instead of dropping and recreating it.

Remarks

The ALTER INDEX statement carries out two tasks:

It can be used to rename an index, primary key, or foreign key.

It can be used to change an index type from nonclustered to clustered, or vice versa.

The ALTER INDEX statement can be used to change the clustering specification of the index, but does not reorganize the data. As well, only one index per table or materialized view can be clustered.

ALTER INDEX cannot be used to change an index on a local temporary table. An attempt to do so results in an Index not found error.

This statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots.

Note what it says about using the ALTER TABLE statement to rename the constraint name if needed. If you need more help try the dedicated SQL Anywhere forum at http://sqlanywhere-forum.sap.com/

Related Question