How to rename a default constraint on Oracle?

Question

I want to rename many constraints (PK, FK, ...etc. ) that have default names which start with 'SYS' to be able to insert the same data in other DB.

I found the following script that I changed to get what I want:

BEGIN
    FOR cn IN (
        SELECT constraint_name 
        FROM user_constraints 
        WHERE constraint_type = 'P'
        AND table_name = 'SPECIALITE'
    )
    LOOP
        EXECUTE IMMEDIATE 'ALTER TABLE ' || cn.table_name || ' RENAME CONSTRAINT ' || cn.constraint_name  || ' TO PK_' || 'SPECIALITE';
    END LOOP;
END;

This script works, but it seems a bit complicated for me, I wonder if it exists something like:

ALTER TABLE 'SPECIALITE' RENAME CONSTRANT (....)

The problem is I don't know the name of constraints, they have a default name, I know only tables where they are.

Is it possible?

Answer 1

As you already know, you need to run two queries.

select constraint_name from user_constraints 
where table_name = <table_name> and constraint_type = 'P';

and with the constraint name in hand,

alter table <table_name> rename constraint <old_constr_name> to <new_constr_name>;

This will require copying the constraint name from the first query and pasting it into the second, in the proper place (<old_constr_name>).

If this was all, I wouldn't post an answer. But I remember something I read on AskTom some time ago - a clever way to avoid copying and pasting, using the COLUMN command in SQL*Plus. (This may also work in SQL Developer and Toad.) Something like this:

column constraint_name new_val c   -- Note: no semicolon - this is SQL*Plus, not SQL

select constraint_name from user_constraints 
where table_name = <table_name> and constraint_type = 'P';

alter table <table_name> rename constraint &c to <new_constr_name>;

If you need to change many PK constraint names, this will save some work. The constraint name returned by the SELECT query is saved in the "new_val" labeled "c" from the SQL*Plus COLUMN command, and it is used in the ALTER TABLE statement.