Mysql LEFT JOIN doesn't work properly for tables that have foreign key constraint?

Question

Any mysql expert?

I was doing a left join select query on two tables (one with primary key and one with foreign key) and I was using those two fields on the join.

I thought the left join will display all the result on left table even though the data field specific still not on the right table but it didn't show up.

But then I try it again with tables that doesn't have keys and it worked.

Is left join doesn't work on keys or what? Any idea?

Answer 1

Without seeing your SQL statement, it is difficult to say. However, left joining on key-constrained columns shouldn't be an issue so long as the primary key is in the left table.



http://dev.mysql.com/doc/refman/5.0/en/example-foreign-keys.html



If the primary key is in the right table, then you might get unintended results.



Suppose you have TableA with A_ID as a primary key and TableB with B_ID as a foreign key for TableA A_ID. This should return all records in TableA and matching records / nulls for TableB:



SELECT TableA.A_ID, TableB.B_ID

FROM TableA

LEFT JOIN TableB ON TableA.A_ID = TableB.B_ID



However, the following would only return all records in TableB, with no nulls because the key constraint would prevent TableA from having records that aren't in TableB:



SELECT TableA.A_ID, TableB.B_ID

FROM TableB

LEFT JOIN TableA ON TableA.A_ID = TableB.B_ID



The following would also return only those records that are in TableB:



SELECT TableA.A_ID, TableB.B_ID

FROM TableA

LEFT JOIN TableB ON TableA.A_ID = TableB.B_ID

WHERE TableB.B_ID IS NOT NULL



And depending on any groupings you are using, you might get bad results. There are other possibilities, but without seeing your syntax and data, I can't tell.

Answer 2

I don't believe that MySQL properly deals with Foreign Keys

by default. You can make something a Foreign Key but it is just really for show. For example if you say something like ON DELETE CASCADE it doesn't do the right thing you still need to manuall delete the foreign key referenced fields in other tables. Not sure if this is related but more info never hurts. MySQL is great but for this and other reasons I personally go with postgres myself.

Answer 3

2 motives, i think of. a million) there is an assumption that application good judgment could make up for what's lost by using no longer having ref integrity on the db point. 2) i'm much less helpful approximately this, yet i think of distant places keys may well be seen as a variety of decadence--a function of "high priced" products like sq. Server or Oracle. Decadence unbecoming to the open source crowd.

Answer 4

Your 100% correct, but remember that if you want to use Foreign Keys, your database table should be InnoDB not MyISAM. Recreate your table of type InnoDB.



The foreign key does not exist in any MyISAM (which is the default schema) so it disregards that key.



Try again with MyISAM and it will work fine.



CREATE TABLE mytable (

.....

.....



) ENGINE = InnoDB;



Good Luck

Answer 5

Please post the query .

May be you can contact an expert. Check http://k.aplis.net/