Convert unique key to primary key

I

I had a legacy table with unique key but without primary key. All columns were mandatory and there was no reason to not have a proper primary key. First, lets's check list of unique keys.

SELECT c.table_name, c.constraint_name, LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols
FROM user_constraints n
JOIN user_cons_columns c
    ON c.constraint_name    = n.constraint_name
WHERE n.table_name          = '&TABLE_NAME'
    AND n.constraint_type   = 'U'
GROUP BY c.table_name, c.constraint_name
ORDER BY c.table_name, c.constraint_name;

Script to switch UQ constraint to PK:

DECLARE
    in_uq_constraint    CONSTANT VARCHAR2(30)   := '&UQ_NAME';
    in_pk_name          CONSTANT VARCHAR2(30)   := '&PK_NAME';
    --
    in_table_name       VARCHAR2(30);
    in_primary_cols     VARCHAR2(32767);
    q                   VARCHAR2(32767);
BEGIN
    -- get table name and primary columns
    SELECT p.table_name, LISTAGG(p.column_name, ', ') WITHIN GROUP (ORDER BY p.position)
    INTO in_table_name, in_primary_cols
    FROM user_cons_columns p
    WHERE p.constraint_name = in_uq_constraint
    GROUP BY p.table_name;

    -- create list on new foreign keys
    FOR c IN (
        SELECT
            'ALTER TABLE ' || c.table_name ||
            ' ADD CONSTRAINT ' || c.constraint_name ||
            ' FOREIGN KEY (' || LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) || ')' ||
            ' REFERENCES ' || in_table_name || ' (' || in_primary_cols || ')' AS q
        FROM user_constraints n
        JOIN user_cons_columns c ON c.constraint_name = n.constraint_name
        WHERE n.constraint_type     = 'R'
            AND n.r_constraint_name = in_uq_constraint
        GROUP BY c.table_name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('-- BACKUP: ' || c.q || ';');
        q := q || c.q || ';';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('--');

    -- drop existing foreign keys
    FOR c IN (
        SELECT 'ALTER TABLE ' || RPAD(c.table_name, 30) || ' DROP CONSTRAINT ' || c.constraint_name AS q
        FROM user_constraints c
        WHERE c.constraint_type     = 'R'
            AND c.r_constraint_name = 'UQ_DEPOT'
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(c.q || ';');
    END LOOP;

    -- drop and recreate original key
    DBMS_OUTPUT.PUT_LINE('--');
    DBMS_OUTPUT.PUT_LINE(
        'ALTER TABLE ' || RPAD(in_table_name, 30) || ' DROP CONSTRAINT ' || in_uq_constraint || ';'
    );
    DBMS_OUTPUT.PUT_LINE(
        'ALTER TABLE ' || RPAD(in_table_name, 30) ||
        ' ADD CONSTRAINT ' || in_pk_name || ' PRIMARY KEY (' || in_primary_cols || ')' || ';'
    );
    DBMS_OUTPUT.PUT_LINE('--');

    -- recreate foreign keys from first step
    q := RTRIM(q, ';');
    FOR c IN (
        SELECT DISTINCT REGEXP_SUBSTR(q, '[^;]+', 1, LEVEL) AS q
        FROM DUAL
        CONNECT BY LEVEL <= REGEXP_COUNT(q, ';') + 1
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(c.q || ';');
    END LOOP;
END;
/