Let's say you have got two tables like the following in a MySQL database:
TABLE people:
primary key: PERSON_ID,
NAME,
SURNAME, etc.
TABLE addresses:
primary key: ADDRESS_ID,
foreign key: PERSON_ID,
addressLine1, etc.
If you manage the creation of rows (in both table) and the retrieving of data trough PHP do you still need to create a physical relationship in the database? If yes, why?
Yes, one concrete reason is to have faster retrieving of rows if you want to join tables. Creating a foreign key constraint automatically creates a an index on the column.
So table address' schema should look like this, (assuming People's table primary key is PERSON_ID)
CREATE TABLE Address
(
Address_ID INT,
Person_ID INT,
......,
CONSTRAINT tb_pk PRIMARY KEY (Address_ID),
CONTRRAINT tb_fk FOREIGN KEY (Person_ID)
REFERENCES People(Person_ID)
)
Strictly speaking: You don't need to use FK's. careful indexing and well written query's might seem to be sufficient. However FK's and certainly FK constraints are very useful when it comes to securing data consistency (avoiding orphaned data, for example)
Suppose you wrote your application, everything is tested and it works like a charm. Great, but who's to say that you'll be around every time something has to be changed? Are you going to maintain the code by yourself or is it likely that someone else might end up doing a quick fix/tweak or implement another feature down the road? In reality, you're never going to be the only one writing and maintaining the code, and even if you are the only one maintaining the code, you're almost certainly going to encounter bugs as time passes...
Foreign keys inform both your co-workers and you that data from tbl1 depends on the data from tbl2 and vice-versa. Just like comments, this makes the application easier to maintain.
Bugs are easier to detect: creating a method deleting a record from tbl1, but forgetting to update tbl2 to reflect the changes made to the first tbl. When this happens, the data is corrupted, but the query that caused this won't result in errors: the SQL is syntactically correct and the action it performs is the desired action. These kind of bugs could remain hidden for quite some time, and by the time this is spotted, god knows how much data has been corrupted...
Lastly, and this is an argument that is used all too often, what if the connection to the DB is lost mid-way through a series of update/delete query's? FK Constraints enable you to cascade certain actions. I haven't actually seen this happen, but I know of anybody who doesn't write code to protect against just such a scenario
Deleting or updating several relational records, but mid-way, the connection with the DB gets cut off for some reason. You might have edited tbl2, but the connection was lost before the query to tbl1 was sent. Again, we end up with corrupted data.
FK CASCADE's are very useful here. Delete from tbl1, and set an ON DELETE CASCADE rule, so that you can rest assured that the related records are deleted from tbl2. In the same situation, ON DELETE RESTRICT, can be a fairly useful rule, too.
Note that FK's aren't the ultimate answer to life, the universe and everything (that's 42 - as we all know), but they are a vital part of true relational database-designs.
there are two ways
-first one is to handle all the things on coding end manage the things on deleting or updating a record but when you use foreign key you are enforcing the relation and Db don't allow you to delete records with foreign key constraint especially when you don't want to delete the records related to it there is some situations accrue where you need to do this kind of tasks.
-Second way is to manage things on the Db side. If you have 1-to-many or many-to-many relations in database, foreign keys will be very useful. Also they have some good actions - RESTRICT, CASCADE, SET NULL, NO ACTION those can do some work for you
