Skip to main content

Fix constraint names

F

First thing I would focus on is to fix system generated constraint names. When any constraint check fail you will get an idea about the issue just from a proper constraint name. I like these conventions:

  • PK_ prefix for primary keys [P], followed by table name
  • FK_ prefix for foreign keys [R], followed by table name and shortcut
  • UQ_ prefix for unique constraints [U], followed by table name and optionally shortcut
  • CH_ prefix for checks [C], followed by table name and shortcut
  • NN_ prefix for NOT NULL constraints [C], followed by table name and shortcut

As a shorcut I use column_name or some abbreviation. These conventions works only if your tables have reasonable long/short names (like <= 27 chars) due to Oracle limit on 30 chars. Or you can extend this since Oracle 12.2 to 128 chars. Let's check too long table names:

SELECT t.table_name, LENGTH(t.table_name) AS len
FROM user_tables t
WHERE LENGTH(t.table_name) > 27  -- 27 = 30 - 3 chars for prefix (CS_, PK_, FK_, UQ_, NN_, IX...)
ORDER BY 1;

With query below you can explore constraints with related columns.

SELECT
    t.table_name,
    c.constraint_type,
    c.constraint_name,
    c.generated,
    LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.column_id) AS columns_
FROM user_tables t
LEFT JOIN (
    SELECT
        c.table_name,
        c.column_name,
        c.column_id,
        n.constraint_type,
        n.constraint_name,
        CASE WHEN n.generated = 'GENERATED NAME' THEN 'YES' END AS generated
    FROM user_cons_columns t
    JOIN user_constraints n
        ON n.constraint_name    = t.constraint_name
    JOIN user_tab_cols c
        ON c.table_name         = t.table_name
        AND c.column_name       = t.column_name
) c ON c.table_name             = t.table_name
WHERE t.table_name              NOT LIKE '%\__$' ESCAPE '\'     -- filter out my DML tables
GROUP BY t.table_name, c.constraint_type, c.constraint_name, c.generated
ORDER BY 1, 2, 3;


Primary keys and unique constraints

Fixing primary keys, foreign keys and unique constraints is relatively easy. Problem is with checks resp. NOT NULL constraints (mandatory columns) due to use of legacy LONG type as I explained in Constraints overview article. If you have multiple unique constraints in same table then you have to rename them manually.

WITH z (constraint_type, expected_name) AS (
    SELECT 'P', 'PK\_${TABLE_NAME}'  FROM DUAL UNION ALL
    SELECT 'U', 'UQ\_${TABLE_NAME}%' FROM DUAL
)
SELECT
    n.table_name,
    n.constraint_type,
    n.constraint_name,
    LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols,
    'ALTER TABLE ' || RPAD(LOWER(n.table_name), 30) || ' RENAME CONSTRAINT ' || RPAD(LOWER(n.constraint_name), 30) || ' TO ' ||
        RPAD(LOWER(REPLACE(REPLACE(z.expected_name, '\'), '${TABLE_NAME}', n.table_name)) || ';', 35) ||
        '  -- ' || LPAD('(' || LENGTH(REPLACE(REPLACE(z.expected_name, '\'), '${TABLE_NAME}', n.table_name)), 3) || ') ' ||
        LOWER(LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position)) AS fix
FROM user_cons_columns c
JOIN user_constraints n
    ON n.constraint_name    = c.constraint_name
JOIN z ON z.constraint_type = n.constraint_type
WHERE (
    n.generated             = 'GENERATED NAME'
    OR n.constraint_name    NOT LIKE REPLACE(z.expected_name, '${TABLE_NAME}', n.table_name) ESCAPE '\'
)
GROUP BY n.table_name, n.constraint_type, n.constraint_name, z.expected_name
ORDER BY 1, 2, 3;


Foreign keys

Fixing foreign key names has just one catch. The name length limitation. Due to this you must adjust target names before script execution.

WITH z (constraint_type, key_prefix) AS (
    SELECT 'R', 'FK_' FROM DUAL
)
SELECT
    n.table_name,
    n.constraint_type,
    n.constraint_name,
    LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols,
    'ALTER TABLE ' || RPAD(LOWER(n.table_name), 30) || ' RENAME CONSTRAINT ' || RPAD(LOWER(n.constraint_name), 30) || ' TO ' ||
        LOWER(z.key_prefix || n.table_name || '_' || LISTAGG(c.column_name, '_') WITHIN GROUP (ORDER BY c.position)) || ';' ||
        '  -- ' || LOWER(LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position)) AS fix
FROM user_cons_columns c
JOIN user_constraints n
    ON n.constraint_name    = c.constraint_name
JOIN z ON z.constraint_type = n.constraint_type
WHERE (
    n.generated             = 'GENERATED NAME'
    OR n.constraint_name    NOT LIKE z.key_prefix || '%'
)
GROUP BY n.table_name, n.constraint_type, n.constraint_name, z.key_prefix
ORDER BY 1, 2, 3;


NOT NULL constraints

We need to identify NOT NULL constraints first. I wrote a qurey for this in Constraints overview article, you just need to combine it with approach above. And you will face same issues with the name length limitation as in foreign keys.

WITH z (constraint_type, key_prefix) AS (
    SELECT 'C', 'NN_' FROM DUAL
),
x AS (
    SELECT XMLTYPE(DBMS_XMLGEN.GETXML(q'[SELECT c.constraint_name AS name, c.search_condition AS text
FROM user_constraints c
WHERE c.constraint_type = 'C'
]')) AS xml_
    FROM DUAL
),
f AS (
    SELECT
        EXTRACTVALUE(s.object_value, '/ROW/NAME') AS constraint_name,
        EXTRACTVALUE(s.object_value, '/ROW/TEXT') AS search_condition
    FROM x
    CROSS JOIN TABLE(XMLSEQUENCE(EXTRACT(x.xml_, '/ROWSET/ROW'))) s
)
SELECT
    n.table_name,
    n.constraint_type,
    n.constraint_name,
    LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols,
    'ALTER TABLE ' || RPAD(LOWER(n.table_name), 30) || ' RENAME CONSTRAINT ' || RPAD(LOWER(n.constraint_name), 30) || ' TO ' ||
        LOWER(z.key_prefix || n.table_name || '_' || LISTAGG(c.column_name, '_') WITHIN GROUP (ORDER BY c.position)) || ';' AS fix
FROM user_cons_columns c
JOIN user_constraints n
    ON n.constraint_name    = c.constraint_name
JOIN z ON z.constraint_type = n.constraint_type
JOIN f
    ON f.constraint_name    = n.constraint_name
    AND f.search_condition  = '"' || c.column_name || '" IS NOT NULL'
WHERE (
    n.generated             = 'GENERATED NAME'
    OR n.constraint_name    NOT LIKE z.key_prefix || '%'
)
GROUP BY n.table_name, n.constraint_type, n.constraint_name, z.key_prefix
ORDER BY 1, 2, 3;


Checks

If you fixed all NOT NULL constraints then fixing checks should be easy now.

WITH z (constraint_type, key_prefix) AS (
    SELECT 'C', 'CH_' FROM DUAL
)
SELECT
    n.table_name,
    n.constraint_type,
    n.constraint_name,
    LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols,
    'ALTER TABLE ' || RPAD(LOWER(n.table_name), 30) || ' RENAME CONSTRAINT ' || RPAD(LOWER(n.constraint_name), 30) || ' TO ' ||
        LOWER(z.key_prefix || n.table_name || '_' || LISTAGG(c.column_name, '_') WITHIN GROUP (ORDER BY c.position)) || ';' AS fix
FROM user_cons_columns c
JOIN user_constraints n
    ON n.constraint_name    = c.constraint_name
JOIN z ON z.constraint_type = n.constraint_type
WHERE n.generated           = 'GENERATED NAME'
GROUP BY n.table_name, n.constraint_type, n.constraint_name, z.key_prefix
ORDER BY 1, 2, 3;


And because this is now my longest article ever, here is cherry on top. A query to generate short constraint names by cutting table and column names.

WITH x AS (
    SELECT
        3 AS max_len_table,
        5 AS max_len_column
    FROM DUAL
)
SELECT
    c.table_name, c.column_name,
    SUBSTR(
        REGEXP_REPLACE(c.table_name,  '([[:alpha:]]{0,' || max_len_table  || '})[^_[:digit:]]*', '\1') || '_' ||
        REGEXP_REPLACE(c.column_name, '([[:alpha:]]{0,' || max_len_column || '})[^_[:digit:]]*', '\1'),
        1, 27) AS short_name
FROM user_tab_columns c
JOIN user_tables t ON t.table_name = c.table_name
CROSS JOIN x
ORDER BY 1, 2;


Tags:

Comments

Post a Comment