If i use Natural joins than what is the use of primary key and foreign key?
+1
A:
Primary keys and foreign keys are types of database constraint: their purpose is to ensure the integrity of the data, not to facilitate joins.
Primary Key: there must not be more than one row with the same value for the primary key column(s).
Foreign Key: every value in the foreign key column(s) of each row must correspond to the primary (or unique) key of a row in the referenced/parent table.
Tony Andrews
2010-08-25 08:13:25
+1
A:
Not sure quite what you are asking. Joins do something different to PKs/FKs - and they are clearly not a substitute for each other. PKs and FKs implement constraints to ensure correctness of the data. Joins are a type of query operation.
dportas
2010-08-25 11:39:40
A:
Natural joins are usually a bad idea. You can accidentally join to the wrong field and then your results are all wrong but may appear to be right. Even if they work at first, changes to the database at a later time could drastically alter your query results and be hard to figure out. I would under no circumstances use a natural join.
The PKs and Fks are there to enforce data integrity, it would be an extremly bad idea to remove them whether you use natural joins or not. Databases without profer PKS and FKs almost always end up containing bad data.
HLGEM
2010-08-25 16:54:27
Natural joins are a *good* idea as long as you specify what columns are to be included from the tables being joined. If you do that then the behaviour of a natural join is perfectly predictable and "safe". Unfortunately the SQL syntax for selecting the columns *before* doing the join is a bit verbose (requires a derived table subquery). So that's the real reason why most people tend to avoid natural joins even where SQL supports them - because it requires more code.
dportas
2010-08-26 09:21:51
In general, it's not safe! Just add one column, and it could be picked for the join because its name matches a column from the other table, so the join is now "wrong". Calling it safe is more or less like saying "SELECT * FROM mytable" is safe... yes, it is as long as nobody adds a column, and change the column order...
pascal
2010-08-27 10:59:43