Umbenennen Ihrer Constraints

05.
Januar
2008
Veröffentlicht von: Marietta Achatz

Wollten Sie schon immer auf einfache Art und Weise Ihre Constraints umbenennen, damit Sie sofort erkennen, um welchen Typ es sich handelt (beispielsweise in Fehlermeldungen, in welchen eine Verletzung eines Constraints bemerkt wird)? Dann haben wir jetzt den passenden Tipp für Sie, mit dem Sie alle Ihre Constraints mit wenig Aufwand umbenennen können.

Wollten Sie schon immer auf einfache Art und Weise Ihre Constraints umbenennen, damit Sie sofort erkennen, um welchen Typ es sich handelt (beispielsweise in Fehlermeldungen, in welchen eine Verletzung eines Constraints bemerkt wird)? Dann haben wir jetzt den passenden Tipp für Sie, mit dem Sie alle Ihre Constraints mit wenig Aufwand umbenennen können.

Der Befehl zum Umbenennen lautet allgemein:

ALTER TABLE <table_name> RENAME CONSTRAINT <constraint_name> TO <new_constraint_name>;


Alle notwendigen Informationen erhalten wir aus den Data Dictionary Views DBA_CONSTRAINTS und DBA_CONS_COLUMNS. Aus DBA_CONSTRAINTS lesen wir den Eigentümer (OWNER), den Namen des Constraint (CONSTRAINT_NAME), den Typ des Constraint (CONSTRAINT_TYPE) und den Tabellennamen (TABLE_NAME), aus DBA_CONS_COLUMNS den Namen der Spalte, auf welcher der Constraint liegt (COLUMN_NAME). Um die zusammengehörenden Constraint-Eigenschaften zu erhalten, müssen die Tabellen verknüpft werden (OWNER und CONSTRAINT_NAME müssen bei beiden gleich sein). Durch einen SELECT geben wir alle Informationen automatisch als fertigen ALTER TABLE-Befehl aus. Dazu müssen Sie Strings (Konstanten) und Spaltennamen miteinander verknüpfen.

SELECT 'ALTER TABLE '||a.owner||'.'||a.table_name||
 ' RENAME CONSTRAINT '||a.constraint_name||' TO '||
 a.table_name||'_'||b.column_name||'_'||a.constraint_type||';'
FROM dba_constraints a , dba_cons_columns b
WHERE a.constraint_name=b.constraint_name
AND a.owner=b.owner
AND a.owner = 'SCOTT';


Ein Beispiel wäre hier (der Eigentümer lautet SCOTT, die Tabelle heißt EMP, der Spaltenname EMPNO und auf dieser Spalte liegt ein Constraint vom Typ Primary Key):

ALTER TABLE scott.emp RENAME CONSTRAINT sys_c008518 TO emp_empno_p;

Da wir aber gerne eine genauere Bezeichnung der Constraint-Typen im Namen hätten, für

     C (not null) = NN
     C (check) = CK
     P (Primary Key) = PK
     U (Unique Constraint) = UK
     R (Foreign Key) = FK

müssen wir noch weitere Bedingungen einfügen. Geben Sie hierzu eine CASE-Klausel in den SELECT-Befehl ein:

CASE
 WHEN a.constraint_type = 'C' THEN 'NN'
 WHEN a.constraint_type = 'C' THEN 'CK'
 WHEN a.constraint_type = 'P' THEN 'PK'
 WHEN a.constraint_type = 'U' THEN 'UK'
 WHEN a.constraint_type = 'R' THEN 'FK'
END


Wie Sie sehen, kann das noch nicht funktionieren, da für 'C' zwei verschiedene Werte ausgegeben werden sollen (zwei verschiedene Typen, C (not null) und C (check) werden mit dem gleichen Buchstaben gekennzeichnet). Nun müssen Sie noch zwei weitere Bedingungen anhängen. Dazu lässt sich die Spalte SEARCH_CONDITION der View DBA_CONSTRAINTS verwenden. Allerdings ist diese vom Datentyp LONG, was zur Folge hat, dass man auf diese Spalten keinen LIKE-Operator anwenden kann.

Deshalb greifen Sie auf einen bereits vorhandenen Tipp des Monats (<link blog-detailansicht besonderheiten-des-datentyps-long.html external-link-new-window internal link in current>Opens external link in new windowMärz 2007) zurück, in welchem erklärt wird, wie in LONG-Spalten gesucht werden kann.

CREATE OR REPLACE FUNCTION get_search_condition( p_cons_name IN VARCHAR2 )
RETURN VARCHAR2
  authid current_user
  IS
    l_search_condition dba_constraints.search_condition%type;
  BEGIN
    SELECT search_condition into l_search_condition
      FROM dba_constraints
      WHERE constraint_name = p_cons_name;
    RETURN l_search_condition;
END;
/


Aus Vereinfachungsgründen wird ein extra View erzeugt, der die Data Dictionary Views DBA_CONSTRAINTS und DBA_CONS_COLUMNS joint.

CREATE OR REPLACE VIEW cons_list
AS
SELECT t1.owner,
       t1.constraint_name,
       t1.constraint_type,
       t1.table_name,
       t2.column_name,
       t2.position,
       t1.search_condition
    FROM dba_constraints t1,
         dba_cons_columns t2
      WHERE t1.owner = t2.owner
        AND t1.constraint_name = t2.constraint_name;


Der eigentliche SELECT-Befehl, der alle Befehle zum Umbenennen ausgibt lautet schließlich folgendermaßen:

SELECT 'ALTER TABLE '||A.OWNER||'.'||A.TABLE_NAME||
       ' RENAME CONSTRAINT '||A.CONSTRAINT_NAME||
       ' TO '||A.TABLE_NAME||'_'||A.COLUMN_NAME||'_'||
       CASE
            WHEN B.ANZAHL > 1
                 THEN 'COMBINE_'
            END||
       CASE
            WHEN A.CONSTRAINT_TYPE = 'C'
                 AND GET_SEARCH_CONDITION(A.CONSTRAINT_NAME)
LIKE '%NOT NULL%'
                 THEN 'NN'
            WHEN A.CONSTRAINT_TYPE = 'C'
                 AND GET_SEARCH_CONDITION(A.CONSTRAINT_NAME)
NOT LIKE '%NOT NULL%'
                 THEN 'CK'
            WHEN A.CONSTRAINT_TYPE = 'P'
                 THEN 'PK'
            WHEN A.CONSTRAINT_TYPE = 'U'
                 THEN 'UK'
            WHEN A.CONSTRAINT_TYPE = 'R'
                 THEN 'FK'
            END ||';' AS BEFEHLE
    FROM CONS_LIST A,
         ( SELECT OWNER, CONSTRAINT_NAME, COUNT(*) AS ANZAHL
             FROM CONS_LIST
            GROUP BY OWNER, CONSTRAINT_NAME ) B
      WHERE A.OWNER = 'SCOTT'
        AND A.OWNER = B.OWNER
        AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
        AND ( A.POSITION = 1
              OR A.POSITION IS NULL )
/

Dann nur noch die Ausgabe in eine Spool-Datei umleiten, die Datei ausführen und fertig.
Viel Erfolg!

DBA SQL

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.