TLDR:
Is not declaring a row in a table that relates to another table as a foreign key good practice in mysql?
Long story:
Today at a project meeting about the design of the mysql db I was presenting the test model when the team lead asked: "did you declare the relations as foreign keys?" At first I was confused and then answer a solid "yes!" thinking that he was testing my knowledge, as I'm very much new to the team and a junior. To my surprise he responded with "oh, ok, that's fine anyway", as if not doing it would've been better. The other team member was as confused as I was. He added something to the tune of "it leads to endless cascading", but I couldn't go further into it because the meeting was a short daily and the subject was dropped.
Later he wrote to me in private, he clearly felt the need to clarify. "Is not that declaring foreign keys is wrong, is that the bring up problems in large databases. Since this is not a large database, it won't be a problem." So I asked "if you don't declare it as a foreign key then what? You just leave it as a int and relate them 'manually'?" To what he basically answered "Yes".
I'm I ignorant of how this truly work behind closed doors, as opposed to how they are taught, or is he wrong? Is there every any reason to not declare a foreign key for a row that is a foreign key? Wouldn't the answer to uncontrolled cascading be more knowledge of how your db works and better design?
Context:
I'm beginning to think my team lead is not as senior as he presents himself to be and want to know if this is one more clue to the add to the case.
I tried to the the DB as was taught and expected it to be correct.
Foreign keys are great for maintaining data integrity, making sure your database stays consistent and clean. But they're not always perfect. They can slow things down in large databases because every time you change something, the system has to make sure it still makes sense in terms of those foreign keys. And if you're not careful with how you set them up, you could accidentally delete or change things you didn't mean to (that's the "cascading" your team lead was talking about).
Your team lead isn't necessarily wrong in saying that you could just handle the relations manually, especially in a larger database to boost performance. But, doing it manually has its own problems - it's easy to make mistakes and end up with inconsistent data.
So, it's not a case of foreign keys being universally good or bad. It depends on your specific situation. For smaller databases, or when data integrity is crucial, use foreign keys. If you're working with a huge database and you're more concerned about speed, and you're confident you can handle the relations accurately in your application code, then maybe you can do without foreign keys.