I am trying to change all PK constraint names. Below is the code I am trying to run:

BEGIN   
    FOR i IN (
    SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
    FROM ALL_CONSTRAINTS
    WHERE OWNER = 'SOME_SCHEME'
    AND TABLE_NAME NOT LIKE 'flyway%'
    AND TABLE_NAME NOT LIKE 'BIN%'
    AND CONSTRAINT_TYPE = 'P')
    LOOP
    dbms_output.put_line(i.CONSTRAINT_NAME || i.table_name);
    EXECUTE IMMEDIATE 'ALTER TABLE i.table_name rename constraint i.constraint_name to i.table_name || ''_PK''';
    END LOOP;           
    END;

I am getting the following error:

SQL Error [946] [42000]: ORA-00946: missing TO keyword
ORA-06512: at line 17
ORA-06512: at line 17

Why does it say TO is missing when it is not? How can this problem be solved?

CodePudding user response：

If you use dbms_output to display the statement before you execute it, you'll see it's malformed; whatever the table and constraint, the statement you are currently trying to execute is, literally:

ALTER TABLE i.table_name rename constraint i.constraint_name to i.table_name || '_PK'

The i.* parts are not variables or values, they are literally those strings. Running that statement manually gets the same error, of course.

You need to concatenate the loop variable values into the statement; you also need to specify the owner, since you're looking at all_tables, and you might as well quote the names:

DECLARE
  l_stmt varchar2(4000);
BEGIN  
  FOR ... 
  LOOP
    dbms_output.put_line(i.CONSTRAINT_NAME || i.table_name);
    l_stmt := 'ALTER TABLE SOME_SCHEME."' || i.table_name || '"'
      || ' rename constraint "' || i.constraint_name || '"'
      || ' to "' || i.table_name || '_PK"';
    dbms_output.put_line(l_stmt);
    EXECUTE IMMEDIATE l_stmt;
  END LOOP;           
END;
/    

which generates something more like:

ALTER TABLE SOME_SCHEME."SDO_DATUMS" rename constraint "DATUM_PRIM" to "SDO_DATUMS_PK"

db<>fiddle

I've hard-coded the SOME_SCHEME name you use in the loop query; you could also get that from the query itself by adding OWNER to the select list, and then concatenate that in as well (again, quoted to be overly cautious). Or set that in a local variable and use that in both places, as @MTO suggested in a comment.

If you will only run this as the SOME_SCHEME user then you can query user_constraints instead:

DECLARE
  l_stmt varchar2(4000);
BEGIN   
  FOR i IN (
    SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME
    FROM USER_CONSTRAINTS
    WHERE TABLE_NAME NOT LIKE 'flyway%' -- are these really quoted lower-case?
    AND TABLE_NAME NOT LIKE 'BIN%'
    AND CONSTRAINT_TYPE = 'P'
    AND CONSTRAINT_NAME != TABLE_NAME || '_PK'
  )
  LOOP
    l_stmt := 'ALTER TABLE "' || i.table_name || '"'
      || ' RENAME constraint "' || i.constraint_name || '"'
      || ' TO "' || i.table_name || '_PK"';
    dbms_output.put_line(l_stmt);
    EXECUTE IMMEDIATE l_stmt;
  END LOOP;           
END;
/

db<>fiddle with example tables, with quoted and unquoted identifiers.

You might also want your loop query to include:

AND CONSTRAINT_NAME != TABLE_NAME || '_PK'

... so you don't touch constraints that already have the name pattern you want.