Win a copy of Event Streams in Action this week in the Java in General forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

difference between foreign key & Join

 
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When to use "foreign key"? if we are getting the same result by using "Joins" then why should we use foreign key?
 
Ranch Hand
Posts: 368
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

bandita rani wrote:When to use "foreign key"? if we are getting the same result by using "Joins" then why should we use foreign key?


Foreign keys are used for joining.
 
Bandita Patel
Greenhorn
Posts: 25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ya thats what i want to ask.. whats the difference then?
 
Ranch Hand
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Bandita Acharya wrote:When to use "foreign key"? if we are getting the same result by using "Joins" then why should we use foreign key?



Foreign key primary role is to enforcing referential integrity between data in two tables, not joining.
Here you find the basics: http://en.wikipedia.org/wiki/Foreign_key
 
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The following page discusses the concept of a foreign key.

Define Foreign Key

The following page discusses the concept of an inner join.

SQL Inner Join

 
Bartender
Posts: 20933
127
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Keys aren't actually part of the abstract SQL concept. They're one of the underlays to the implementation to obtain better performance and data integrity.

You can join on just about anything, but if the column(s) you are joining on constitute a schema-defined Foreign Key, you obtain those benefits.
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are some good answers here, so most of my answer will simply repeat them. Nonetheless, i do not think it is stated clearly.

Foreign Keys and joins having absolutely nothing to do with each other. Zero, nothing. Foreign Keys are technically not even SQL (concepts), they are part of the database concept of Relational Integrity, often referred to as RI. Let me explain.

When creating a table, you often want to constrain the data allowed in a column. This comes in three main flavors: Data Type, Validation, and Existence. The Data type must be defined directly after the column name (unless it is implicitly defined by a Foreign Key), Validations are optional Check and Unique Constraints (and Indexes), and Existence comes (when direct) via Foreign Keys. There are variations, but this is the main form of checking the data is what you expect and allow it to be.

A Foreign Key works by making sure the datum (when not null) exists in another table. For example:


A join is a SQL concept to describe the relationship between multiple tables in the FROM clause of a SQL statement. ANSI SQL also uses it as a SQL keyword. For example:


That is a basic "Cartesian" join. No Foreign Keys required.

--

As it so happen, there is a high correlation between tables that have foreign keys and table you will want to join, so much so, that tools that help you write queries will offer to join tables for you based on those joins, and that will often be exactly what you want. Regardless, please make sure you understand the difference between the two concepts, and repeat the mantra: RI and joins are not the same thing!

--

OK, once we're here, it's time for a SQL joke: A query walked into a bar and walking over to two tables asked, "May i join you?"
 
Thomas Hauck
Ranch Hand
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A foreign key consists of a column that references another column in another table. This column that is being referred is most often a primary key of the referenced table.
The purpose of the foreign key is to ensure referential integrity of the data.
More on this topic can be found on the following web page.

Foreign Key
 
Tim Holloway
Bartender
Posts: 20933
127
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Thomas Hauck wrote:A foreign key consists of a column that references another column in another table. This column that is being referred is most often a primary key of the referenced table.
The purpose of the foreign key is to ensure referential integrity of the data.
More on this topic can be found on the following web page.

Foreign Key



Actually, no. I spent many years on a system where the foreign keys were compounded from multiple columns, not just "another column".
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!