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 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!