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?
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.
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.