Difference between a relationship and foreign key

I'm having difficulty understanding the nuances between relationships and foreign keys. Foreign keys/primary keys make sense to me in the traditional relational database sense.; however I am not seeing the difference between defining keys and defining relationships.

Could someone give an example of when you would NOT use a primary/foreign key pair and WOULD define a relationship to link two tables?

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Phillip Hetzel, I think you would find what you are looking for on this page: https://docs.thoughtspot.com/4.5/admin/loading/constraints.html

    We recommend that you use "Foreign Key relationships" wherever possible. "Generic relationships" are for objects with Many-to-Many cardinality i.e. where you do not have PK-FK constraint (1:M). These can be resource intensive and should be used sparingly.

    Reply Like
  • I'm looking to get a little more under the hood on how many-to-many relationships are treated in ThoughtSpot. Can I think of it as ThoughtSpot creating a virtual dimension table with unique values in it to facilitate a drill across query of two fact tables?

    Reply Like
  • You can run a search query on the tables that are connected by a "Generic relationship" and then click on (i) icon > QUERY VISUALIZER at the right of the search bar to look precisely at how ThoughtSpot is planning & executing the query for your the answer. 

    Under the hood, TS creates set of join indices referred to as "Denorm."


    If you tql>

    script table <tablename>; 


    You will see that the generic relationship is a physical constraint defined as follows:

    ALTER TABLE "db_name"."schema_name"."table1_name" ADD RELATIONSHIP "Relationship_name" WITH "db_name"."schema_name"."table2_name" AS  "table1_name"."field1" = "table2_name"."field1" ;

    Thus, generic relationships can result in large join multiplicity & resource consumption and should be avoided when PK-FK relationships exist or can be derived using bridge tables.

    Reply Like
Like Follow
  • Status Answered
  • 1 yr agoLast active
  • 3Replies
  • 394Views
  • 2 Following