2 Replies Latest reply: May 4, 2011 3:29 PM by Steven Hawkins RSS

PK's/FK's Use

Van Halbert Apprentice

When importing base tables from a relational source I can see that the Foreign Keys are imported.

 

In the Package Diagram the links between FK's and PK's are shown. Are these Relationships in EDS or links? I ask as you can define objects called relationships which have their own symbol, which the aforementioned 'links' don't seem to have in the Model Explorer view.

 

I was wondering what the purpose of these links/relationships within EDS are outside of the database?

Are they used for cascading deletes, helping the optomizer etc...?

 

When creating a new view which includes two tables which do have FK/PK relationship defined (as links), the view doesn't automatically add the JOIN clause, I thought it might?

 

Your thoughts would be appreciated.

  • 1. PK's/FK's Use
    Ramesh Reddy Master

    Teiid imports the PK/Fk relationships during the import process in the Designer, however they are not used during the query optimizer currently. I believe this data is also in the metadata files.

     

    Ramesh..

  • 2. PK's/FK's Use
    Steven Hawkins Master

    > Are they used for cascading deletes, helping the optomizer etc...?

     

    Cascading deletes, no not currently.  To be handled automatically that would typically only be applicable in inherently updatable views that are using the default updating handling.  In which case our notion of pk/fk relationships on views is more informational rather than an actual integretity constraint.  There is room for an enhancement there, but it is also striaghtfoward for the user to handle the cascading manually in an update procedure (or with a containing view that acts like a before trigger).

     

    Yes the optimizer uses pk/fk relationships.  pks are of course used during costing.  Relationships are used to determine if a table is key preserved through a join.  The notion of key preservation is used in subquery optimization and with inherently updatable views.

     

    > When creating a new view which includes two tables which do have FK/PK relationship defined (as links), the view doesn't automatically add the JOIN clause, I thought it might?

     

    There has been a feature request along these lines for years.  There is also a concept of a natural join that joins based upon same named columns, which simplifies some of responsibility of writing a join.