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?

Answer 1

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.

Answer 2

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.