How to retrieve foreign key constraints data

foreign keyoraclequery

I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema.

I've found this, but does not gives all the info I need:
https://stackoverflow.com/questions/4389228/sql-for-oracle-to-check-if-a-constraint-exists

I'm currently working on it and may end up with a solution in the next minutes/hours. But if someone has already a full working solution, I'll be glad to know it 🙂

Best Answer

After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:

SELECT
    CONS.CONSTRAINT_NAME,
    CONS.TABLE_NAME,
    COLS.COLUMN_NAME,
    CONS.R_CONSTRAINT_NAME,
    CONS_R.TABLE_NAME R_TABLE_NAME,
    COLS_R.COLUMN_NAME R_COLUMN_NAME

FROM USER_CONSTRAINTS CONS
    LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
    LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
    LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME

-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R'

ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME

Related Solutions

Mysql – Derived value not calculating as expected using combination of FK and Triggers (MySQL)

reviews_has_comments (rhc) exists because comments can be added to several other types of content.

If I understand correctly, that is not a justification for responses_has_comments table - you can have several tables with 1-to-many relationships with the same comments table. The only reason for introducing responses_has_comments would be if the same comment can be attached to more than one response, as well as more than one comment being attached to each response, ie it is a many-to-many relationship - I don't think that is what you are trying to achieve.

This does not answer your main question but may sidestep the problem in your updated trigger. Your comments trigger then becomes:

CREATE TRIGGER `dec_n_comments`
    AFTER DELETE ON `comments` FOR EACH ROW
    BEGIN
        UPDATE responses
        SET n_comments = n_comments - 1
        WHERE id_responses = @id_responses;
        -- rinse and repeat for all linked "commentable" table data...        
    END;
$$

Mysql – Foreign key constraints with MySQL/InnoDB

"integers are cheaper to compare than characters, because character sets and collations (sorting rules) make character comparisons complicated. Integers are very good when using as an index column." - HottestGuide

Best Answer

Related Solutions

Mysql – Derived value not calculating as expected using combination of FK and Triggers (MySQL)

Mysql – Foreign key constraints with MySQL/InnoDB

Related Question