Constraints overview

F

For me data integrity is essential, yet often overlooked by others. I remember project with 1000+ tables and zero foreign keys, because "they were slowing them down". Trading performance for data integrity should be exception, not a rule. If you know little about constraints I suggest to start in latest documentation to gain some background. You can also explore other articles with constraint label.

To get a list of constraints in your schema you can query user_constraints view.

SELECT
    n.table_name,
    n.constraint_type,
    n.constraint_name,
    n.status,
    CASE WHEN n.generated = 'GENERATED NAME' THEN 'YES' END AS generated
FROM user_constraints n
WHERE n.table_name          NOT IN (SELECT object_name FROM RECYCLEBIN)
ORDER BY n.table_name, n.constraint_type;

Primary keys, foreign keys and unique constraints

These queries can be used for more detailed overview of primary keys, foreign keys and unique constraints. Checks are a bit complicated.

SELECT
    n.table_name,
    n.constraint_name,
    CASE WHEN n.generated = 'GENERATED NAME' THEN 'YES' END         AS generated,
    LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols,
    n.status,
    n.delete_rule,
    n.deferrable,
    n.deferred
FROM user_constraints n
JOIN user_cons_columns c
    ON c.constraint_name    = n.constraint_name
WHERE n.constraint_type     = 'P'  -- 'U' is also possible
GROUP BY
    n.table_name, n.constraint_name, n.generated,
    n.status, n.delete_rule, n.deferrable, n.deferred
ORDER BY 1, 2;

SELECT
    n.table_name,
    n.constraint_name,
    CASE WHEN n.generated = 'GENERATED NAME' THEN 'YES' END         AS generated,
    LISTAGG(c.column_name, ', ') WITHIN GROUP (ORDER BY c.position) AS cols,
    k.table_name                                                    AS primary_table,
    LISTAGG(p.column_name, ', ') WITHIN GROUP (ORDER BY p.position) AS primary_cols,
    n.r_constraint_name                                             AS primary_constraint,
    n.status,
    n.delete_rule,
    n.deferrable,
    n.deferred
FROM user_constraints n
JOIN user_cons_columns c
    ON c.constraint_name    = n.constraint_name
JOIN user_cons_columns p
    ON p.constraint_name    = n.r_constraint_name
    AND p.position          = c.position
JOIN user_constraints k
    ON k.constraint_name    = n.r_constraint_name
WHERE n.constraint_type     = 'R'
GROUP BY
    n.table_name, n.constraint_name, n.generated, k.table_name, n.r_constraint_name,
    n.status, n.delete_rule, n.deferrable, n.deferred
ORDER BY 1, 2;

Identify NOT NULL constraints

Only way how to distinguish between these two is to check search_condition column in user_constraints view. Unfortunatelly it is a LONG type and thus it is diffucult to access it in query. But there are several workarounds. I like this one:

WITH 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_name,
    CASE WHEN n.generated = 'GENERATED NAME' THEN 'YES' END AS generated
FROM user_constraints n
JOIN user_cons_columns c
    ON c.constraint_name    = n.constraint_name
JOIN f
    ON f.constraint_name    = n.constraint_name
    AND f.search_condition  = '"' || c.column_name || '" IS NOT NULL'
WHERE n.constraint_type     = 'C'
    AND n.table_name NOT IN (
        SELECT object_name FROM RECYCLEBIN
    )    
ORDER BY 1, 2;

For checks you will do just the opposite.

WITH 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_name,
    CASE WHEN n.generated = 'GENERATED NAME' THEN 'YES' END AS generated,
    n.status,
    n.delete_rule,
    n.deferrable,
    n.deferred
FROM user_constraints n
JOIN user_cons_columns c
    ON c.constraint_name    = n.constraint_name
JOIN f
    ON f.constraint_name    = n.constraint_name
    AND f.search_condition  != '"' || c.column_name || '" IS NOT NULL'
WHERE n.constraint_type     = 'C'
    AND n.table_name NOT IN (
        SELECT object_name FROM RECYCLEBIN
    )    
ORDER BY 1, 2;

Enable or disable constraints (with cascade)

You can enable/disable selected types of constraints for specified table(s) with following script:

DECLARE
    in_tables_like          CONSTANT VARCHAR2(30)   := '%';
    in_constraint_types     CONSTANT VARCHAR2(4)    := 'PURC';  -- [PURC]
    in_target_status        CONSTANT VARCHAR2(30)   := 'ENABLE';
BEGIN
    FOR c IN (
        SELECT n.table_name, n.constraint_name, n.status
        FROM user_constraints n
        WHERE n.status          != in_target_status || 'D'
            AND n.table_name    LIKE UPPER(in_tables_like)
            AND n.table_name    NOT IN (SELECT object_name FROM RECYCLEBIN)
            AND INSTR(UPPER(in_constraint_types), n.constraint_type) > 0
        ORDER BY
            CASE WHEN in_target_status = 'ENABLE'  THEN DECODE(n.constraint_type, 'P', 1, 'U', 2, 'R', 3, 'C', 4, 5) END,
            CASE WHEN in_target_status = 'DISABLE' THEN DECODE(n.constraint_type, 'C', 1, 'R', 2, 'U', 3, 'P', 4, 5) END,
            n.table_name, n.constraint_name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(c.table_name || ' ' || c.constraint_name);
        EXECUTE IMMEDIATE
            'ALTER TABLE ' || c.table_name ||
            ' ' || in_target_status || ' CONSTRAINT ' || c.constraint_name ||
            CASE WHEN in_target_status = 'DISABLE' THEN ' CASCADE' END;
    END LOOP;
END;
/

In next article I will show you how to fix constraint names and why you should do that.