How to check modality/cardinality on a relationship in Oracle

Question

I am attempting to reverse engineer a Database that is in Oracle. I have been able to find the main keys and the referential integrity rules that correspond to the Foreign Keys but cannot locate where the modality/cardinality rules are stored.

Any help regarding to which table to look in or a sample query would be greatly appreciated.

Answer 1

I think you will have to check for referring columns being nullable:

SELECT column_name, nullable
FROM user_tab_cols
WHERE table_name = '<TABLE_NAME>'

and for unique indices on referring columns:

SELECT idx.index_name, col.column_name
FROM user_indexes idx
JOIN user_ind_columns col ON ( col.index_name = idx.index_name )
WHERE idx.uniqueness = 'UNIQUE'
  AND idx.table_name = '<TABLE_NAME>'

Answer 2

What do you mean by modality / cardinality rules?

FK relationships are always 1:N as far as Oracle is concerned. I guess one could enforce a 1:1 by having two FK relationships on the same fields but in opposite directions.

The only other 'rule' concerning cardinality are unique keys, but I think you already have those. If not, this might help:

http://www.eveandersson.com/writing/data-model-reverse-engineering#constraints

Depending on the setup of the database you also might find heuristics about the cardinality of values in columns:

select * from user_tab_col_statistics

Especially the value 'NUM_DISTINCT' might be of some value.

If you are looking for more information in the data dictionary, I recommend this select for starters

select * from dict
where lower(comments|| '@' || table_name) like '%whatareyoulookingfor%'