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

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%

Answer 2

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>

友情链接