Primary and Foreign keys, and Unique

[westnyorai]

I'm wondering what exactly key assignments do. Can't you just create the ER diagram without using Primary and Foreign keys?

I can easily make a PHP statement that will link two databases together with just ID numbers.

Like...

Table: tableA
id: INT
name: VARCHAR
tableBLink: INT

tableAB
tableAid: INT
tableBid: INT

tableB:
id: INT
name: VARCHAR
desc: VARCHAR

if you simply need a database to connect them, I don't see what primary and foreign keys really do. I understand Unique keys though, but I don't get what UC_Fieldname(fieldname) is for.

Any insights?

[fenway]

That's a two part question. First, PRIMARY keys are used by the DB to uniquely identify each record in a table -- in your example, the ID column would be an excellent choice. This is almost always necessary, especially for any lookups to find said ID. Without an index, the DB would have to scan each and every row of the table to find the id in question. Obviously, PRIMARY keys must be unique; however, you can make a similar index on any other column, unique or not.

As for foriegn keys, you're somewhat correct -- you need not establish this link explicity in the DB (e.g. via InnoDB tables) just to query to two tables. However, you'd be hard pressed to enforce referential integrity without them, or ensure ACID-compliant transactions.