I would just add a few words to Nils' excellent reply: In the most general sense, a foreign key is just a primary key value that occurs outside of its native table (i.e. in a "foreign" table, get it?) in order to point to the record in its native table with that value as its primary key.
You can use foreign keys in your database design and programming without actually formally declaring them as such in the database system. Indeed, as you point out, you *have* to use them to establish relationships among your tables. If the database system doesn't recognize the foreign key columns as foreign keys, then you must programatically ensure that referential integrity is maintained: --when you insert a new record, make sure that any foreign key points to an existing record (a record with that key value as its primary key) --don't ever change the value of a record's primary key (that is, unless you are prepared to change the value of all the foreign keys in all the records pointing to that record) --don't ever delete a record that has other records pointing to it via foreign keys (unless you delete all those records too). Many people assume that a relational database system *must* recognize such foreign keys (and relationships among the tables) in order to be truly relational. But the term "relational" refers, not to the relationships among the tables, but to the basic relation that is defined by each table (and Codd's theory that even the most complex data structure can be broken down into such simple relations). --John On Thursday 24 July 2003 08:00 pm, Nils Valentin wrote: > Hi Eli, > > The short answer is .. you create relationships between > tables by creating foreign keys and primary keys. > However, you might not even need them ;-) - I explain > below. > > Foreign keys and primary keys are used to create > relations between tables. > > Using them will bind you to some rules which you can use > to your advantage (or not), thats entirely up to you. > > If you define a primary key than any foreign key can link > to the primary key. However , a foreign ke can only > contain a value which is already defined in a primary > key. > > This can be used to restrict that only valid values are > insert for foreign keys or f.e no primary key is deleted > where still existing foreign keys are pointing to the > primary key record. Currently only the InnoDB table > format supports Foreign keys. > > > In order to use a join you dont need to create any keys. > You can free define the columns to be used for the join > condition. > > Please see the JOIN Syntax for more information. > > http://www.mysql.com/doc/en/JOIN.html > > Best regards > > Nils Valentin > Tokyo/Japan > > 2003年 7月 25日 金曜日 03:36、eli さんは書きました: > > Hi, > > > > Can anyone explain me how to create relationships > > between tables in Mysql? > > > > For making some kind of functions like joins, it is > > indispensable to have relationships defined? > > > > > > Thanks in advanced. > > > > > > eli -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
