i dont understand one thing. why do i have to use foreign key to link to a primary key in another table?
when i query i type like "where table1.foreignkeycolumn = table2.primarykeycolumn.
does this not work if i havent assigned a foreign key to primarykey?
It works fine without the foreign key. You do not need a foreign key / primary key relationship to JOIN tables in a query.
The purpose of primary and foreign keys is to instruct the database engine itself to perform the following tests, and to reject data that fails these tests:
For a column declared as a primary key, every row must have a non-NULL value and that value must be unique across all rows in the table. (If you declare several columns to collectively form the primary key, then all columns must contain non-NULL values and the combination of values must be unique among all rows).
For a column declared as a foreign key, if a value is given for a row (foreign key columns can contain NULL values if not otherwise specified) then that value must exist in the linked primary key column.
For a column declared as a primary key, if you attempt to delete the row or change the primary key value, it must be true that no foreign key columns linked to that primary key contain that value.
It's a little more elaborate (methods exist to handle primary key updates by cascading the changes to foreign key tables, for instance), but that's the gist. PK/FK are there to get the database to do some of the integrity checking for you, and to guarantee that the relationships will always be valid.
The select..where will work fine, but what the foreign key adds is a constraint that enforces the rule that keys entered in the foreign key column in table 1 must match existing values in table 2.
You can join two tables together on whatever conditions you feel like. That is about how you read and process the data.
Foreign and primary keys are about enforcing constraints and building indexes. Adding foreign keys is good practise in almost all cases, but not essential.
You need it to be sure that your data is consistent. It will save your valuable time which you may spend searching for foreign keys pointing to missing rows.
When you create foreign key, you add a constraint, in other words enforce certain rules. On the logical level it works fine without actually creating one. For example MySQL with MyIsam engine does not support foreign keys constraints, but you can still work on "logical" level and take care of referential integrity on the application level.
Foreign Key Constraints have nothing to do with queries or how you join tables in queries. They exist to enforce and control data consistency.
If the only possible shipping metods are USPS and FedEx, then applications should not be allowed to put Carrier Pigeon in the Shipping method column in another table.
If your company has two office locations, one in New York and one in Chicago, then application logic should be inhibited from putting Boston in an employees OfficeAssigned column