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.
Comments
Post a Comment