Life without foreign keys can exist. Right?

thewebprojects · August 19, 2019, 6:37pm

Howdy, I think this is a fundamental principle that I haven’t quite learned…

It doesn’t seem like my code cares if a foreign key is set in the DB. What’s the benefit? For example I can query all contacts for a specific user even if a foreign key wasn’t set, assuming I have a column that holds something like user_id values in my contact table?

It must have some benefit such as indexing or performance, etc. or maybe something more interesting - like automating tasking such as removing all contacts that belong to a user that’s been deleted?

Hmmm…

benanamen · August 19, 2019, 7:29pm

Data Integrity for one.

frankbeen · August 20, 2019, 12:27am

You will measure incredible speed performances when your table contains a lot of data. (for example about one million records or so)

You can compare it with a phone book which has the names on alphabetical order and not the phone numbers in an ascending order. But what if you want to find a name that belongs to one particular phone number? You will have to read the whole phone book page by page until you find the number that you search for. In stead of that you could use another list (or index) with telephone numbers in ascending order. That is exactly what a database index does: make a new list in a specific order so that the database can find the data that you want in no time!

benanamen · August 20, 2019, 1:30am

@frankbeen, The OP is asking about Foreign Keys. They are not the same as Indexes.

chorn · August 20, 2019, 5:45am

I don’t really see what you are asking about. If foreign keys don’t do anything on your machine, you are using them wrong, or your database is broken - in MySQL it may depend on the engine you use.

like automating tasking such as removing all contacts that belong to a user that’s been deleted?

Did you think about that specific task by yourself? Because it looks like you’ve already read about the use of foreign keys, what you mentioned is a default use case for foreign keys.

thewebprojects · August 20, 2019, 1:07pm

Hello all, Thanks for you valuable input.

Although my focus in this thread mentions foreign keys, it’s really about many of the things used in MySQL to manage the data. I’m not respecting MySQL for all its possibilities because I haven’t learned them all or fully realized their benefit. I’m using PHP and MySQL. It just seems like there’s some overlap at times where I can either use some MySQL feature to handle something or code the feature.

Very clear example: Why restrict your field type to timestamp within MySQL when I can just be sure to use PHP to insert the property formatted timestamp. Note: no need to answer this.

Based on remarks by @benanamen in other (kinda related) post, here, I should allow MySQL to handle as much of the data storing, formatting, relationships, etc. as possible. And based on a reply here from @frankbeen doing so will help me gain performance (why code up something that comes out of the box already).

thewebprojects · August 20, 2019, 1:13pm

Did you think about that specific task by yourself? Because it looks like you’ve already read about the use of foreign keys, what you mentioned is a default use case for foreign keys.

@chorn, Actually I was never really sure that would be possible but figured these are some of the reasons why it’s best to use MySQL’s features opposed to coding the hell out of everything.

I see I need to bone up on MySQL. I’m kinda learning stuff as I go here.