CODEWITHSUNDEEP
sqldatabasejoinkeypredicate
BND
BND

Reputation: 678

Primary keys for Join operation?

I read from a classmate post: “Joins are usually done using primary keys in a good database design.” Is really using primary keys as predicate necessary for good design. I can't see how.

Thank you for your help!

Upvotes: 0

Views: 60

Answers (1)

raj kumar
raj kumar

Reputation: 101

Use of primary keys for a good database design could be a debate. classically according to RDBMS guideline it is recommended to create primary keys for good database design. but now a days there is a trend not to put much constraints on DB side to improve performance rather do the validations on business layer (not sure if it is true for primary keys as well).

Now coming to your question, Primary keys are not mandatory for join operations, however it is mandatory to use columns which uniquely identifies the records of master table otherwise it can generate spurious records.

department

| dept| sub_dept | dsc         |
| CS  | CS       | Computer sc.|
| CS  | IT       | Info Tech.  |

student

| Name | age | sex  | dept | sub_dept|
| abcd | 025 | M    | CS   | CS      |
| wxyz | 023 | M    | CS   | IT      |

Now if you join the tables on sub_dept you will get correct results. 

select s.name, s.age, s.sex, d.dsc from student s, department d where

s.sub_dept = d.sub_dept

| Name | age | sex | dsc            |
| abcd | 025 | M   | Computer Sc.   |
| wxyz | 023 | M   | Computer Sc.   |

if you join the tables on dept column you will get spurious tuples (2 extra rows)

select s.name, s.age, s.sex, d.dsc from student s, department d where s.dept = d.dept

| Name | age | sex | dsc            |
| abcd | 025 | M   | Computer Sc.   |
| wxyz | 023 | M   | Computer Sc.   |


| abcd | 025 | M   | Info Tech.     |
| wxyz | 023 | M   | Computer Sc.   |

Upvotes: 1

Related Questions