Could you please explain me why it is necessary to specify those foreign keys when creating tables? I mean, I've created two tables which have a one-to-many relationship (on the ER-diagram) but I didn't specify the foreign keys and references. I can connect the tables using the where-clause and even perform joins and so on.
Probably, I don't get some basic concepts, although I've read some stuff about it. I guess it has something to do with data consistency or referential integrity or something.
So, could you explain me these concepts? Are those references and foreign keys are absolutely required if I have, let's say, 8-10 tables with one-to-many relationships and if I can assure that the data is inserted correctly into the database?
It is not necessary to specify foreign key relationships. It is just a good idea.
When you specify the relationship, the database ensures relational integrity. That is, it ensures that the values in the foreign key column are legitimate values.
In addition, the
cascade options on foreign keys are a big help when values are updated or deleted.
The reason it's necessary is to ensure data integrity.
Suppose you have a table called
orders, and a table called
order details, both have a column called
If you don't use foreign keys you might be inserting order details for an order that doesn't exists in the orders table.
Having a foreign key will make the database raise an error if you try to add order details to a non existing order.
It will also raise an error if you delete an order that already have details, unless you delete the order details first or specify cascade delete on the foreign key.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!Donate Us With