List:General Discussion« Previous MessageNext Message »
From:Roger Baklund Date:July 10 2002 2:10pm
Subject:Re: Foreign keys in MySQL
View as plain text  
* Hanxue Lee
> In the MySQL documentation, it is stated that
>
> "foreign keys in SQL are not used to join tables, but are used mostly for
> checking referential integrity (foreign key constraints). "

This could have been formulated in a better way, IMHO. I would even say the
statement is wrong. In RDBMS terminology, a 'foreign key' is one (or more)
column(s) in a table identifying records in a different table. Consider this
schema:

CREATE TABLE owner (
  id int primary key,
  name varchar(30));

CREATE TABLE pet (
  id int primary key,
  owner int,
  name varchar(30));

The 'owner' field of the 'pet' table is a foreign key if it used to store
the primary keys from the 'owner' table. It _is_ a foreign key, even if no
foreign key _constraints_ have been defined. The "FOREIGN KEY" keywords in
MySQL is used to define such _constraints_, not to define the foreign key
itself.

Maybe the phrase in the manual should have been something like:

"...the "FOREIGN KEY" keywords are not used to join tables, but..."

> Does it mean that constraints are not supported?

Why did you think that? The statement you quoted says "...not used to join
tables... used mostly for... foreign key constraints".

Anyway, the answer is 'yes and no', foreign key _constraints_ are supported,
but only if you use InnoDB tables. Using the FOREIGN KEY keywords on other
table types will not give errors, but it will be ignored.

> I need to have Update and perhaps Delete Referential Integrity.

<URL: http://www.mysql.com/doc/S/E/SEC446.html >
<URL: http://www.mysql.com/doc/e/x/example-Foreign_keys.html >

--
Roger

Thread
Foreign keys in MySQLHanxue Lee10 Jul
  • Re: Foreign keys in MySQLRoger Baklund10 Jul