2 Replies Latest reply on Sep 18, 2015 4:11 PM by rp0428

    Primary Key & Foreign Key relationship

    3029265

      This may be the novice question, but still I looking for answers to find out the primary key & foreign key relationship between the tables? Primarily my task is to update tables in Oracle.

       

      I would like to know the impact, before updating any tables. I believe there will be a impact only if we've primary key & foreign key relationship between the tables. How to find out this impact?

       

      Thanks in advance for your answers.

        • 1. Re: Primary Key & Foreign Key relationship
          David Last-Oracle

          If you select a Table in the SQL Developer tree, there's a new Model tab in the main panel.  This displays a diagram showing the table and all its foreign key relationships to other tables.

          (The model tab was introduced in version 4.1 of SQL Developer.)

           

          Alternatively you can use the Data Dictionary Import Wizard in either Data Modeler or SQL Developer (using Data Modeler > Import > Data Dictionary from the File menu).

          This will display a diagram showing the Foreign Key relationships between the tables you import.

           

          David

          • 2. Re: Primary Key & Foreign Key relationship

            This may be the novice question, but still I looking for answers to find out the primary key & foreign key relationship between the tables? Primarily my task is to update tables in Oracle.

            Find out how? Using Sql Developer? Using the data dictionary?

             

            You generally do NOT need to know if primary/foreign keys exist in order to write a proper update statement. Those 'keys' are used by Oracle when INSERTING/DELETING data.

             

            The primary key is used to ensure that rows are unique and don't contain null values. So that key only comes into play for an update if you try to update the primary key value to so that it becomes a duplicate or null. Or for update if you try to update the key value and there are current CHILD rows in another table with the current key value.

             

            A foreign key is used by Oracle for INSERTs to ensure that CHILD rows have a required PARENT ROW if needed. For an update it just ensures that any new/updated foreign key value exists in the PARENT table if required.

             

            I would like to know the impact, before updating any tables. I believe there will be a impact only if we've primary key & foreign key relationship between the tables. How to find out this impact?

            Why do you 'believe' there will be an impact?

             

            The MOST COMMON relationship in a data model is PARENT/CHILD where there are primary keys on each table and a foreign key between the child and parent.

             

            So it is normal for UPDATES to be done for that data model.

             

            Actually the 'impact' would be if there WERE NO indexes on the join columns between the two tables. Those 'keys' are on the join columns and have an index behind them. It is those INDEXES that are important.

             

            You do NOT need actual primary or foreign keys for performance of updates but you do need indexes on the join columns. Otherwise Oracle will have to do a full table scan for each row.

             

            What PROBLEM are you trying to solve?

             

            Bsse your code/queries on FACTS - not on what you 'believe'.

             

            If you question is NOT related to Sql developer or how to use it, or if you question is about PK/FK issues or writing proper SQL statements then mark this thread ANSWERED and repost it in the Sql forum

            SQL & PL/SQL