oracle

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
    v_sql VARCHAR2(4000);
BEGIN
    DBMS_OUTPUT.PUT_LINE('-- ===== PRIMARY KEYS =====');

    FOR rec IN (
        SELECT uc.constraint_name,
               LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) AS cols
        FROM user_constraints uc
        JOIN user_cons_columns acc ON uc.constraint_name = acc.constraint_name
        WHERE uc.table_name = 'ZORDERDETAIL'
          AND uc.constraint_type = 'P'
        GROUP BY uc.constraint_name
    ) LOOP
        v_sql := 'ALTER TABLE ZOrderTable_new ADD CONSTRAINT ' || rec.constraint_name || ' PRIMARY KEY (' || rec.cols || ');';
        DBMS_OUTPUT.PUT_LINE(v_sql);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('-- ===== UNIQUE CONSTRAINTS =====');

    FOR rec IN (
        SELECT uc.constraint_name,
               LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) AS cols
        FROM user_constraints uc
        JOIN user_cons_columns acc ON uc.constraint_name = acc.constraint_name
        WHERE uc.table_name = 'ZORDERDETAIL'
          AND uc.constraint_type = 'U'
        GROUP BY uc.constraint_name
    ) LOOP
        v_sql := 'ALTER TABLE ZOrderTable_new ADD CONSTRAINT ' || rec.constraint_name || ' UNIQUE (' || rec.cols || ');';
        DBMS_OUTPUT.PUT_LINE(v_sql);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('-- ===== FOREIGN KEYS =====');

    FOR rec IN (
        SELECT uc.constraint_name,
               LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) AS cols,
               uc.r_constraint_name
        FROM user_constraints uc
        JOIN user_cons_columns acc ON uc.constraint_name = acc.constraint_name
        WHERE uc.table_name = 'ZORDERDETAIL'
          AND uc.constraint_type = 'R'
        GROUP BY uc.constraint_name, uc.r_constraint_name
    ) LOOP
        DECLARE
            v_ref_table VARCHAR2(100);
            v_ref_cols  VARCHAR2(1000);
        BEGIN
            SELECT acc2.table_name,
                   LISTAGG(acc2.column_name, ', ') WITHIN GROUP (ORDER BY acc2.position)
            INTO v_ref_table, v_ref_cols
            FROM user_constraints uc2
            JOIN user_cons_columns acc2 ON uc2.constraint_name = acc2.constraint_name
            WHERE uc2.constraint_name = rec.r_constraint_name;

            v_sql := 'ALTER TABLE ZOrderTable_new ADD CONSTRAINT ' || rec.constraint_name ||
                     ' FOREIGN KEY (' || rec.cols || ') REFERENCES ' || v_ref_table || ' (' || v_ref_cols || ');';
            DBMS_OUTPUT.PUT_LINE(v_sql);
        END;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('-- ===== INDEXES =====');

    FOR rec IN (
        SELECT ui.index_name,
               ui.uniqueness,
               LISTAGG(uic.column_name, ', ') WITHIN GROUP (ORDER BY uic.column_position) AS cols
        FROM user_indexes ui
        JOIN user_ind_columns uic ON ui.index_name = uic.index_name
        WHERE ui.table_name = 'ZORDERDETAIL'
          AND ui.generated = 'N'  -- skip system indexes created for constraints
        GROUP BY ui.index_name, ui.uniqueness
    ) LOOP
        IF rec.uniqueness = 'UNIQUE' THEN
            v_sql := 'CREATE UNIQUE INDEX ' || rec.index_name || '_NEW ON ZOrderTable_new (' || rec.cols || ');';
        ELSE
            v_sql := 'CREATE INDEX ' || rec.index_name || '_NEW ON ZOrderTable_new (' || rec.cols || ');';
        END IF;
        DBMS_OUTPUT.PUT_LINE(v_sql);
    END LOOP;

END;
/