Wednesday, December 28, 2011

Checking Foreign Key attribute consistency

While evolving a schema during development, it's sometimes hard to make sure that column attributes remain consistent across different tables.

Some databases provide domain types. So for example, with Firebird/InterBase you can define a domain type or alias for your invoice_number column as, say, VARCHAR(10). Then you can use the domain type in defining any tables containing invoice_number, and the column attributes will always be consistent.

Oracle doesn't have a natural way to do this. One trick I've used is preprocessing SQL DDL files with Ant and using "macros" for column types. So I might have a types.properties file like this:

...
INVOICE_NUMBER_TYPE = VARCHAR2(10)
...


Then in create_table_invoice.sql I might have:

CREATE TABLE invoice (
invoice_number @INVOICE_NUMBER_TYPE@ NOT NULL,
...
);


And also use the macro elsewhere for any foreign key column.

But, this turned out to be a bit of a pain. For one thing, it makes the build more convoluted because of the preprocessing required. More importantly, it made the SQL DDL files "invalid". We couldn't just run one in sqlplus, without the preprocessing step first. We couldn't send one to a DBA. We didn't get good IDE support, because the IDE doesn't understand the type macros.

As a result, I've reverted to putting hardcoded column attributes in the DDL files. But this takes me back to the problem of keeping the foreign keys parent/child attributes consistent.

Another approach to that problem is to use a view like this:

CREATE OR REPLACE VIEW chk_foreign_key_type AS
SELECT
ac.table_name child_table,
acc.column_name child_column,
atc.data_type child_data_type,
atc.data_length child_data_length,
atc.data_scale child_data_scale,
ac2.table_name parent_table,
acc2.column_name parent_column,
atc2.data_type parent_data_type,
atc2.data_length parent_data_length,
atc2.data_scale parent_data_scale
FROM all_constraints ac
JOIN all_cons_columns acc ON acc.owner = ac.owner
AND acc.constraint_name = ac.constraint_name
JOIN all_tab_columns atc ON atc.owner = ac.owner
AND atc.table_name = acc.table_name
AND atc.column_name = acc.column_name
JOIN all_constraints ac2 ON ac2.owner = ac.owner
AND ac2.constraint_name = ac.r_constraint_name
JOIN all_cons_columns acc2 ON acc2.owner = ac2.owner
AND acc2.constraint_name = ac2.constraint_name
AND acc2.position = acc.position
JOIN all_tab_columns atc2 ON atc2.owner = acc2.owner
AND atc2.table_name = acc2.table_name
AND atc2.column_name = acc2.column_name
WHERE ac.owner = 'your_schema'
AND ac.constraint_type = 'R'
AND (atc2.data_type <> atc.data_type
OR atc2.data_length <> atc.data_length
OR NVL(atc2.data_scale, -1) <> NVL(atc2.data_scale, -1))
ORDER BY 1, 2
/

COMMENT ON TABLE chk_foreign_key_type IS
'Foreign key column(s) different from parent type/length/scale'
/


This view will return any foreign key where a data type, length or scale of a column in the child table does not match the corresponding column in the parent table.