Questions on JOINs and foreign keys
I'm new to using JOINs and foreign keys in MySQL. I think I understand but have a few questions:
- Are less queries always better? E.g. You can query a product and then query the category it belongs to in two queries or you can use one in a single JOIN query? Is the latter better? I'm guessing it means less trips to the server if you're using PHP.
- Should you always index both columns involved in a foreign key relationship? What if the cardinality on a column is really low?
- Are joins faster with a foreign key in place? Or it is for data integrity only?
- Is adding 'On INSERT CASCADE' really worth it on an auto-increment ID field? In practice they never really change do they? So is having an index here a waste of resources?
- If you have 'ON DELETE RESTRICT' set up the query fails if you try to delete the parent of a child. Other than PHP returning false when you run a DELETE query, is there any other way to check if there's a dependency? From your web application point-of-view a query returning false is ambiguous. Or is there a specific error code for it?
- Am I right in saying most FK relations are one-to-many. When you set the key up, do you always do it from the 'one' to the 'many'?
- If you want a zero-to-many do you allow NULL on the column on the left table—and if you want a one-to-one you make both columns unique?