T
Thanks to the extended namespaces introduced in Oracle 12c (12.2 actually), you don't have to worry about making constraint names fit into 30 characters. You don't have to create aliases which nobody understands anymore — you can use longer names, you just have to fit within 128 characters.
Here are the patterns I like:
- primary key - {TABLE_NAME}_PK
- unique key - {TABLE_NAME}_UQ (if there is only one), {TABLE_NAME}_UQ_{COLUMN_NAME} (if there are more of them)
- foreign key - {TABLE_NAME}_FK_{COLUMN_NAMES} (I used to do {TABLE_NAME}_FK_{REFERENCED_TABLE} but sometimes I have to reference the same table multiple times and then this does not work)
- not null - {TABLE_NAME}_NN_{COLUMN_NAME} (much better than the generated name)
- check - {TABLE_NAME}_CH_{COLUMN_NAME} (using just the first column name, so it might not work every time)
And here is a 200+ lines long nifty script that will rename your constraints according to these patterns. You can also use prefixes. I used to do that in the past, but I don't recommend it anymore — it's easier to find project/module-specific objects when the constraint type is used as a postfix.
DECLARE
in_prefix CONSTANT VARCHAR2(16) := '%'; -- to limit scope just to a subset of objects, like XX%
in_execute CONSTANT BOOLEAN := TRUE; -- rename the constraints
in_strip_id CONSTANT BOOLEAN := TRUE; -- strip _ID from the columns
--
in_pk_prefix CONSTANT VARCHAR2(8) := ''; -- primary key
in_pk_postfix CONSTANT VARCHAR2(8) := '_PK';
in_fk_prefix CONSTANT VARCHAR2(8) := ''; -- foreign key
in_fk_postfix CONSTANT VARCHAR2(8) := '_FK';
in_uq_prefix CONSTANT VARCHAR2(8) := ''; -- unique constraints
in_uq_postfix CONSTANT VARCHAR2(8) := '_UQ';
in_nn_prefix CONSTANT VARCHAR2(8) := ''; -- not null constraints
in_nn_postfix CONSTANT VARCHAR2(8) := '_NN';
in_ch_prefix CONSTANT VARCHAR2(8) := ''; -- checks
in_ch_postfix CONSTANT VARCHAR2(8) := '_CH';
--
v_query VARCHAR2(2000);
BEGIN
FOR c IN (
SELECT
c.table_name,
c.constraint_type,
c.constraint_name,
--
CASE WHEN c.constraint_name != c.new_name THEN c.new_name END AS new_name
FROM (
-- primary keys
SELECT
c.table_name,
c.constraint_type,
c.constraint_name,
in_pk_prefix || c.table_name || in_pk_postfix AS new_name
FROM user_constraints c
WHERE c.constraint_type = 'P'
AND c.table_name LIKE in_prefix
AND c.table_name NOT IN (SELECT object_name FROM user_recyclebin)
-- unique constraints
UNION ALL
SELECT
c.table_name,
c.constraint_type,
c.constraint_name,
in_uq_prefix || c.table_name || in_uq_postfix || '_'
|| LISTAGG(CASE WHEN in_strip_id
THEN REGEXP_REPLACE(n.column_name, '_ID$', '')
ELSE n.column_name END,
'_') WITHIN GROUP (ORDER BY n.position) AS new_name
FROM user_constraints c
JOIN user_cons_columns n
ON n.constraint_name = c.constraint_name
WHERE c.constraint_type = 'U'
AND c.table_name LIKE in_prefix
AND c.table_name NOT IN (SELECT object_name FROM user_recyclebin)
GROUP BY
c.table_name,
c.constraint_type,
c.constraint_name
-- foreign keys
UNION ALL
SELECT
c.table_name,
c.constraint_type,
c.constraint_name,
in_fk_prefix || c.table_name || in_fk_postfix || '_'
|| LISTAGG(CASE WHEN in_strip_id
THEN REGEXP_REPLACE(n.column_name, '_ID$', '')
ELSE n.column_name END,
'_') WITHIN GROUP (ORDER BY n.position) AS new_name
FROM user_constraints c
JOIN user_cons_columns n
ON n.constraint_name = c.constraint_name
JOIN user_cons_columns r
ON r.constraint_name = c.r_constraint_name
AND r.position = n.position
JOIN user_tab_cols p
ON p.table_name = c.table_name
AND p.column_name = r.column_name
WHERE c.constraint_type = 'R'
AND c.table_name LIKE in_prefix
AND c.table_name NOT IN (SELECT object_name FROM user_recyclebin)
GROUP BY
c.table_name,
c.constraint_type,
c.constraint_name,
r.table_name
-- not null constraints
UNION ALL
SELECT
c.table_name,
c.constraint_type,
c.constraint_name,
in_nn_prefix || c.table_name || in_nn_postfix || '_'
|| MIN(n.column_name) KEEP (DENSE_RANK FIRST ORDER BY n.position DESC) AS new_name
FROM user_constraints c
JOIN user_cons_columns n
ON n.constraint_name = c.constraint_name
WHERE c.constraint_type = 'C'
AND c.table_name LIKE in_prefix
AND c.table_name NOT IN (SELECT object_name FROM user_recyclebin)
AND c.search_condition_vc LIKE '"%" IS NOT NULL'
GROUP BY
c.table_name,
c.constraint_type,
c.constraint_name
-- checks, these are very tricky, try to keep current
UNION ALL
SELECT
c.table_name,
c.constraint_type,
c.constraint_name,
CASE
WHEN c.constraint_name LIKE in_ch_prefix || c.table_name || in_ch_postfix || '%' -- keep current if it starts correctly
THEN c.constraint_name
ELSE in_ch_prefix || c.table_name || in_ch_postfix || '_'
|| MIN(n.column_name) KEEP (DENSE_RANK FIRST ORDER BY n.position DESC)
END AS new_name
FROM user_constraints c
JOIN user_cons_columns n
ON n.constraint_name = c.constraint_name
WHERE c.constraint_type = 'C'
AND c.table_name LIKE in_prefix
AND c.table_name NOT IN (SELECT object_name FROM user_recyclebin)
AND c.search_condition_vc NOT LIKE '"%" IS NOT NULL'
GROUP BY
c.table_name,
c.constraint_type,
c.constraint_name
) c
ORDER BY
1, 2, 3
) LOOP
IF c.new_name IS NOT NULL THEN
v_query := TRIM(APEX_STRING.FORMAT (
q'!ALTER TABLE %1 RENAME CONSTRAINT %2 TO %3
!',
--
p1 => c.table_name,
p2 => c.constraint_name,
p3 => c.new_name,
--
p_prefix => '!',
p_max_length => 32767
));
--
DBMS_OUTPUT.PUT_LINE(v_query || ';');
--
IF in_execute THEN
EXECUTE IMMEDIATE v_query;
END IF;
END IF;
END LOOP;
-- fix indexes mapped to the constraints, so they have same name
FOR c IN (
WITH i AS (
SELECT
d.table_name,
d.index_name,
LISTAGG(d.column_name, ', ') WITHIN GROUP (ORDER BY d.column_position) AS cols
FROM user_ind_columns d
JOIN user_indexes i
ON i.index_name = d.index_name
WHERE 1 = 1
AND i.constraint_index = 'YES'
AND i.uniqueness = 'UNIQUE'
AND i.table_name LIKE in_prefix
AND i.table_name NOT IN (SELECT object_name FROM user_recyclebin)
GROUP BY
d.table_name,
d.index_name
),
p AS (
SELECT
c.table_name,
c.constraint_name,
LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols
FROM user_cons_columns c
JOIN user_constraints n
ON n.constraint_name = c.constraint_name
AND n.constraint_type IN ('P', 'U')
AND n.table_name LIKE in_prefix
GROUP BY
c.table_name,
c.constraint_name
)
SELECT
p.table_name,
p.cols AS table_cols,
p.constraint_name AS new_name,
i.index_name
FROM i
JOIN p
ON p.table_name = i.table_name
AND p.cols = i.cols
AND p.constraint_name != i.index_name
ORDER BY
1, 2
) LOOP
v_query := TRIM(APEX_STRING.FORMAT (
q'!ALTER INDEX %1 RENAME TO %2
!',
--
p1 => c.index_name,
p2 => c.new_name,
--
p_prefix => '!',
p_max_length => 32767
));
--
DBMS_OUTPUT.PUT_LINE(v_query || ';');
--
IF in_execute THEN
EXECUTE IMMEDIATE v_query;
END IF;
END LOOP;
END;
/
If you don't have the extended namespace, you can check my older script.
Comments
Post a Comment