Does defining foreign keys and table relationships have any effect on writing queries?

I was putting together a table in Mysql workbench and defining many-to-one relationships and which keys were foreign keys etc, and I know it put this in the actual Create script. So I'm开发者_StackOverflow社区 wondering, is this just to make sure the data model is logical from a behind the scenes point of view, or can those relationships actually be used in queries at all? If they can be, what are some operations that would involve them? (brief example code would be wonderful)

I'm now building an SQLite database in sqlitemanager firefox plugin. It has no options to define these things, and I'm wondering if I should care. (i've only done pretty straightforward queries up to this point).

---

In reality, you can create a join between two tables with any columns. The whole reason of having primary and foreign keys is to enforce the relationship between 2 tables. In another word, in order for you to add a row in the child table, you will need to have the parent table's primary key to serve as the foreign key in the child table. Otherwise, the insertion will fail.

Another reason for using foreign key is to allow your parent table to perform cascade delete. This way, you don't have to manually delete the rows from child table first, then delete from the parent table.... if you have cascade delete set up properly, you can just delete the table row and all the children rows will be gone too.

That said, if you don't want to explicitly define that particular column as foreign key, you can still perform the query without any problems, but it makes the ER diagram difficult to read by your coworkers because the relationship between 2 tables aren't explicitly defined.

---

Foreign keys are very useful. Say you have an application with Countries and Cities.

Country
id
name

City
id
name
countryId

Each city have a foreign key which says which country the city is in. Because you have defined the 'countryId' in the 'cities' table as a foreign key which points to the 'id' column in the 'country' table you can get MySQL to automatically delete all the corresponding cities when you delete a country.