定义外键和表关系对编写查询有影响吗？
我在 Mysql 工作台中组合了一个表，并定义了多对一关系以及哪些键是外键等，我知道它将其放入实际的 Create 脚本中。所以我想知道，这只是为了确保数据模型从幕后的角度来看是逻辑的，还是这些关系实际上可以在查询中使用？如果可以的话，哪些操作会涉及到它们？ （简短的示例代码会很棒）
我现在正在 sqlitemanager firefox 插件中构建一个 SQLite 数据库。它没有选项来定义这些东西，我想知道我是否应该关心。 （到目前为止我只做了非常简单的查询）。
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 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).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
实际上，您可以在具有任意列的两个表之间创建联接。拥有主键和外键的全部原因是为了加强两个表之间的关系。换句话说，为了在子表中添加行，您需要将父表的主键用作子表中的外键。否则，插入将会失败。
使用外键的另一个原因是允许父表执行级联删除。这样，您不必先手动从子表中删除行，然后从父表中删除......如果您正确设置了级联删除，则只需删除表行，所有子行都会也消失吧。
也就是说，如果您不想将该特定列显式定义为外键，您仍然可以毫无问题地执行查询，但这会使您的同事难以阅读 ER 图，因为两个表之间的关系不是明确定义。
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.
外键非常有用。假设您有一个包含国家和城市的应用程序。
每个城市都有一个外键，表示该城市位于哪个国家/地区。因为您已将“cities”表中的“countryId”定义为指向“country”表中的“id”列的外键，您可以得到MySQL在删除一个国家时自动删除所有对应的城市。
Foreign keys are very useful. Say you have an application with Countries and Cities.
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.