Skip to main content

How to view relationships between Primary keys and Foreign keys in your Oracle Database


The following sql statement allows you to view the relationships between primary keys
and foreign keys with other tables.

SELECT uc.constraint_name||CHR(10)
   || '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
   , 'REFERENCES'||CHR(10)
   || '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
    FROM user_constraints uc ,
    user_cons_columns ucc1 ,
    user_cons_columns ucc2
    WHERE uc.constraint_name = ucc1.constraint_name
    AND uc.r_constraint_name = ucc2.constraint_name
    and ucc1.position        = ucc2.position
    AND uc.constraint_type   = 'R'
    --AND uc.constraint_name   = 'FKF32F2C126588DBBC'
    order by ucc1.table_name ,
    uc.constraint_name;
 
 
CONSTRAINT_TYPE (from 11gR2 docs)
C - Check constraint on a table
P - Primary key
U - Unique key
R - Referential integrity
V - With check option, on a view
O - With read only, on a view
H - Hash expression
f - constraint that involves a ref column
S - Supplemental logging
 

Comments

Post a Comment

Popular posts from this blog

Building your first django app in minutes (Conjure up that overdue MVP)

As the trends shift towards data science and big data, machine learning there’s has never been a more lucrative time to start learning python and Django. Building your first Django app in minutes course takes an in-depth look at Django for web development with python. The course is tailored for both beginners, experts, to be founders.  Why not start today learning so you can land yourself the next dream job, build your MVP fast, get that work, and school project done fast. Coding doesn’t have to be mystic, it’s actually more familiar than you think.   Checkout this awesome  75% limited time discount
2 comments
Read more

How to rename hibernate auto generated Foreign key constraint names

When using hibernate maven plugin to generate your tables you may have noticed hibernate generates obfuscated foreign key constraint names which are unreadable. In order to change the constraint names you can apply the @ForeignKey annotation to give your foreign keys more meaningful names as shown below. Take note if you take this approach you will need to take by insuring no duplicate constraint names are created. import @org.hibernate.annotations.ForeignKey;   @ForeignKey( name = "FK_PI_COMPANY_ID" ) @ManyToOne(targetEntity = Company.class, fetch = FetchType.EAGER) @JoinColumn(name = "COMPANY_ID", nullable = true) private Company company;
Post a Comment
Read more