Jump to content


Photo

Primary and Foreign keys, and Unique


  • Please log in to reply
1 reply to this topic

#1 westnyorai

westnyorai
  • New Members
  • Pip
  • Newbie
  • 4 posts
  • LocationWY

Posted 08 April 2006 - 11:49 PM

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 09 April 2006 - 06:41 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users