what is the need of foreign key..

Question

if we store the data in one table then we don't need foreign key

Answer 1

Foreign keys are for linking 2 or more tables together.

customer table
id firstName LastName purchaseId
1 John Doe 42

purchase table
id item itemPrice ItemId quantity total
42 pen 1.99 402 1 1.99

item table
id item itemPrice instock
402 pen 1.99 100

In the customer table above purchaseId is a foreign key that correlates to the purchase id in the purchase table and the itemId in the purchase table correlates to the id in the item table.

This would allow you to make a query view that contains all the information that you would need from all 3 tables.

For instance one that would return:
Purchase # 42 John Doe item: pen quantity: 1 price: 1.99 total: 1.99 amount remaining in stock: 100

More info:
https://www.w3schools.com/sql/sql_foreignkey.asp

Answer 2

If you have a small amount of information that may be fine, but when you're working with larger databases it is usually better to keep your tables columns down to a minimum for organizational purposes.

Answer 3

but why don't we store all the info in one table ?

Answer 4

why it is not working..

alter table second add foreign key(id) references first(roll);