Why is a primary-foreign key relation required when we can join without it

Question

If we can get data from two tables without having primary and foreign key relation  then why we need this rule  Can you please explain me clearly  with suitable example  It s a test database  don t mind the bad structure    Tables  structure       table -  test1  columns - id lname fname dob no primary and foreign key and also not unique without any constraints          table -  test2  columns- id native city again  no relations and no constraints      I can still join these tables with same columns  id   so if there s no primary-foreign key  then what is the use of that

User · Answer

You don t need a FK  you can join arbitrary columns   But having a foreign key ensures that the join will actually succeed in finding something   Foreign key give you certain guarantees that would be extremely difficult and error prone to implement otherwise    For example  if you don t have a foreign key  you might insert a detail record in the system and just after you checked that the matching master record is present somebody else deletes it  So in order to prevent this you need to lock the master table  when ever you modify the detail table  and vice versa   If you don t need want that guarantee  screw the FKs   Depending on your RDBMS a foreign key also might improve performance of select  but also degrades performance of updates  inserts and deletes

User · Answer

A primary key is not required  A foreign key is not required either  You can construct a query joining two tables on any column you wish as long as the datatypes either match or are converted to match  No relationship needs to explicitly exist   To do this you use an outer join   select tablea code  tablea name  tableb location from tablea left outer join  tableb on tablea code   tableb code   join with out relation  SQL join

User · Answer

The main reason for primary and foreign keys is to enforce data consistency   A primary key enforces the consistency of uniqueness of values over one or more columns   If an ID column has a primary key then it is impossible to have two rows with the same ID value  Without that primary key  many rows could have the same ID value and you wouldn t be able to distinguish between them based on the ID value alone   A foreign key enforces the consistency of data that points elsewhere  It ensures that the data which is pointed to actually exists  In a typical parent-child relationship  a foreign key ensures that every child always points at a parent and that the parent actually exists  Without the foreign key you could have  orphaned  children that point at a parent that doesn t exist

User · Answer

I know its late to post  but I use the site for my own reference and so I wanted to put an answer here for myself to reference in the future too  I hope you  and others  find it helpful   Lets pretend a bunch of super Einstein experts designed our database  Our super perfect database has 3 tables  and the following relationships defined between them   TblA 1 M TblB TblB 1 M TblC  Notice there is no relationship between TblA and TblC   In most scenarios such a simple database is easy to navigate but in commercial databases it is usually impossible to be able to tell at the design stage all the possible uses and combination of uses for data  tables  and even whole databases  especially as systems get built upon and other systems get integrated or switched around or out  This simple fact has spawned a whole industry built on top of databases called Business Intelligence  But I digress     In the above case  the structure is so simple to understand that its easy to see you can join from TblA  through to B  and through to C and vice versa to get at what you need  It also very vaguely highlights some of the problems with doing it  Now expand this simple chain to 10 or 20 or 50 relationships long  Now all of a sudden you start to envision a need for exactly your scenario  In simple terms  a join from A to C or vice versa or A to F or B to Z or whatever as our system grows   There are many ways this can indeed be done  The one mentioned above being the most popular  that is driving through all the links  The major problem is that its very slow  And gets progressively slower the more tables you add to the chain  the more those tables grow  and the further you want to go through it   Solution 1  Look for a common link  It must be there if you taught of a reason to join A to C  If it is not obvious  create a relationship and then join on it  i e  To join A through B through C there must be some commonality or your join would either produce zero results or a massive number or results  Cartesian product   If you know this commonality  simply add the needed columns to A and C and link them directly   The rule for relationships is that they simply must have a reason to exist  Nothing more  If you can find a good reason to link from A to C then do it  But you must ensure your reason is not redundant  i e  its already handled in some other way    Now a word of warning  There are some pitfalls  But I don t do a good job of explaining them so I will refer you to my source instead of talking about it here  But remember  this is getting into some heavy stuff  so this video about fan and chasm traps is really only a starting point  You can join without relationships  But I advise watching this video first as this goes beyond what most people learn in college and well into the territory of the BI and SAP guys  These guys  while they can program  their day job is to specialise in exactly this kind of thing  How to get massive amounts of data to talk to each other and make sense   This video is one of the better videos I have come across on the subject  And it s worth looking over some of his other videos  I learned a lot from him

User · Answer

You need two columns of the same type  one on each table  to JOIN on   Whether they re primary and foreign keys or not doesn t matter

[sql] Why is a primary-foreign key relation required when we can join without it?

Examples related to sql

Examples related to database