CODEWITHSUNDEEP
sqloracle-databaseoracle10gmetadata
ssr532
ssr532

Reputation: 117

Viewing oracle's metadata about primary/foreign key constraints

Which table contains detailed information(For example the table the foreign key is referring to) about the constraints? The tables 'all_cons_columns' , 'all_constraints' contains only the name of the constraints which isn't very helpful. I am currently using dbms_metadata.get_ddl() but it doesn't work on all the databases.

Thanks.

Upvotes: 3

Views: 13082

Answers (5)

Clarius
Clarius

Reputation: 1409

Using the Oracle XE sample database. I wanted to identify the primary and foreign keys on a schema/table. Here's how...

SELECT acc.column_name, ac.constraint_type, ac.status, fkc.table_name, fkc.column_name 
FROM all_cons_columns acc 
LEFT JOIN all_constraints ac ON ac.constraint_name = acc.constraint_name 
LEFT OUTER JOIN all_cons_columns fkc ON fkc.constraint_name = ac.r_constraint_name 
WHERE acc.owner = 'OT' 
AND acc.table_name = 'ORDERS' 
AND ac.constraint_type IN ('P', 'R');

The output (after formatting the column widths) looks like this...

COLUMN_NAME          C STATUS   TABLE_NAME           COLUMN_NAME
-------------------- - -------- -------------------- --------------------
ORDER_ID             P ENABLED
CUSTOMER_ID          R ENABLED  CUSTOMERS            CUSTOMER_ID
SALESMAN_ID          R ENABLED  EMPLOYEES            EMPLOYEE_ID

Here we can that see that (for the ORDERS table) ORDER_ID is the primary key, and that there are two foreign keys referencing the CUSTOMERS, and the EMPLOYEES tables respectively.

Upvotes: 0

ema
ema

Reputation: 107

In order to retrieve the foreign key and generate a script to create these, you can use the following query:

SELECT 
   'ALTER TABLE ' || a.table_name || ' ADD CONSTRAINT ' || a.constraint_name 
   || ' FOREIGN KEY (' || a.column_name || ') REFERENCES ' || jcol.table_name 
   || ' (' || jcol.column_name || ');' as commandforeign
FROM
   (SELECT 
       uc.table_name, uc.constraint_name, uc.r_constraint_name, col.column_name
    FROM 
       USER_CONSTRAINTS uc, USER_CONS_COLUMNS col
    WHERE 
       uc.constraint_type='R' and uc.constraint_name=col.constraint_name) a
 INNER JOIN 
    USER_CONS_COLUMNS jcol
 ON 
    a.r_constraint_name=jcol.constraint_name;

Upvotes: 4

devio
devio

Reputation: 37205

This statement lists tables, constraint names, and foreign key table names:

select c.table_name,c.constraint_name,  --c.r_constraint_name, 
  cc.table_name
from all_constraints c
inner join all_constraints cc on c.r_constraint_name = cc.constraint_name

Upvotes: 4

Michał Niklas
Michał Niklas

Reputation: 54292

Have a look at: Reverse Engineering a Data Model. Based on this I did a Python program that dumps Oracle db schema to text. There is PRIMARY_KEYS_INFO_SQL and FOREIGN_KEYS_INFO_SQL that do what you are interested in.

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

It is all in there: the column R_CONSTRAINT_NAME in ALL_CONSTRAINTS contains the name of the referenced PK/UK constraint for a foreign key. You can then look up that constraint to get the TABLE_NAME of the reference table.

When looking at ALL_CONS_COLUMNS, the POSITION of the column in the foreign key will match the POSITION of the column in the primary/unique key.

Upvotes: 5

Related Questions