They say a foreign key is to make possible a relationship between two tables, but I can do this in my statements with JOINs. Exactly what can I do with a foreing key in a SQL statement that I can't do with a JOIN? Or is a foreign key only to help us while we are working with tables in the database?
Asked
Active
Viewed 708 times
0
-
You can do m any things the most important could be that You can apply constains .. on update. delete.. unique and so on so the table are related with rules .. that are always executed – ScaisEdge Feb 03 '17 at 15:28
-
The foreign key simply enforces the relationship as defined by the dba, and as a bonus you can define [referential actions](https://en.wikipedia.org/wiki/Foreign_key#Referential_actions) to make aspects of managing related tables sane. – cteski Feb 03 '17 at 15:30
-
What does "help us while we are working with tables" mean? – philipxy Feb 04 '17 at 12:54
-
Possible duplicate of [Foreign Keys - What do they do for me?](http://stackoverflow.com/questions/3434951/foreign-keys-what-do-they-do-for-me) – philipxy Feb 04 '17 at 12:56
-
Does this answer your question? [Why is a primary-foreign key relation required when we can join without it?](https://stackoverflow.com/questions/5771190/why-is-a-primary-foreign-key-relation-required-when-we-can-join-without-it) – philipxy Oct 13 '22 at 02:33
3 Answers
1
A foreign key is designed to protect database integrity. You can read data with a join without any foreign key being present (and we do it all the time).
What a foreign key will do is prevent you form corrupting your data by doing things like deleting the parent record that a child record refers to. If you attempt to delete the parent record without deleting the child first, it will error, preventing the data corruption. It can also be configured so that if you delete the parent, child records are automatically deleted.
JohnH
- 81
- 3
1
Relationships between rows of two tables can be established by storing a "common value" in columns of each table. (This is a fundamental tenet of relational database theory.)
A FOREIGN KEY is an integrity constraint in the database. If there is a foreign key constraint defined (and enforced), the database will prohibit invalid values from being stored in a row (by INSERT and UPDATEstatement, and prevent rows from being removed (by DELETE statement.)
A JOIN operation in a SQL statement just allows us to access multiple tables. Typically, a join operation will include conditions that require a "match" of foreign key in one table with a primary key of another table. But this isn't required. It's possible to "join" tables on a huge variety of conditions, or on no condition at all (CROSS JOIN).
spencer7593
- 106,611
- 15
- 112
- 140
0
We don't use FKs (foreign keys) to query or update.
Tables represent application relationships. When some values or entities identified by values are related in a certain way we put that row in the table for that relationship. We get or put rows that participate in relationships combined from base table relationships by writing queries mentioning the base tables. JOIN of tables returns the rows that are related by one's relationship AND by the others. UNION returns the rows that are related by one's relationship OR the other. ON and WHERE become AND. Etc. (Is there any rule of thumb to construct SQL query from a human-readable description?) By setting columns equal we force the same value or entity to play roles in multiple relationships. There might or might not be a FK between them, but we don't need to know that to query or update.
FKs get called "relationships", but they're not. They are facts. (They are also "instances" from a "meta" relationship on tables & columns.) They state that the subrow values for some columns in a table are always also subrow values for some columns that are PRIMARY KEY or UNIQUE in some table. (This also means that a certain implication using the tables' relationships is always true in the application situation.) Declaring a FOREIGN KEY to the DBMS means that it can reject update attempts that don't satisfy that constraint as errors. FK declarations are also tied to CASCADE rules in SQL DBMSs, simplifying updates.
