(mysql.info) example-foreign-keys

Info Catalog

(mysql.info) example-user-variables

(mysql.info) examples

(mysql.info) searching-on-two-keys

 
 3.6.6 Using Foreign Keys
 ------------------------
 
 In MySQL, `InnoDB' tables support checking of foreign key constraints.
 See  innodb, and  ansi-diff-foreign-keys.
 
 A foreign key constraint is not required merely to join two tables. For
 storage engines other than `InnoDB', it is possible when defining a
 column to use a `REFERENCES TBL_NAME(COL_NAME)' clause, which has no
 actual effect, and _serves only as a memo or comment to you that the
 column which you are currently defining is intended to refer to a
 column in another table_. It is extremely important to realize when
 using this syntax that:
 
    * MySQL does not perform any sort of `CHECK' to make sure that
      COL_NAME actually exists in TBL_NAME (or even that TBL_NAME itself
      exists).
 
    * MySQL does not perform any sort of action on TBL_NAME such as
      deleting rows in response to actions taken on rows in the table
      which you are defining; in other words, this syntax induces no `ON
      DELETE' or `ON UPDATE' behavior whatsoever. (Although you can
      write an `ON DELETE' or `ON UPDATE' clause as part of the
      `REFERENCES' clause, it is also ignored.)
 
    * This syntax creates a _column_; it does *not* create any sort of
      index or key.
 
    * This syntax will cause an error if used in trying to define an
      `InnoDB' table.
 
 You can use a column so created as a join column, as shown here:
 
      CREATE TABLE person (
          id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
          name CHAR(60) NOT NULL,
          PRIMARY KEY (id)
      );
 
      CREATE TABLE shirt (
          id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
          style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
          color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
          owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
          PRIMARY KEY (id)
      );
 
      INSERT INTO person VALUES (NULL, 'Antonio Paz');
 
      SELECT @last := LAST_INSERT_ID();
 
      INSERT INTO shirt VALUES
      (NULL, 'polo', 'blue', @last),
      (NULL, 'dress', 'white', @last),
      (NULL, 't-shirt', 'blue', @last);
 
      INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
 
      SELECT @last := LAST_INSERT_ID();
 
      INSERT INTO shirt VALUES
      (NULL, 'dress', 'orange', @last),
      (NULL, 'polo', 'red', @last),
      (NULL, 'dress', 'blue', @last),
      (NULL, 't-shirt', 'white', @last);
 
      SELECT * FROM person;
      +----+---------------------+
      | id | name                |
      +----+---------------------+
      |  1 | Antonio Paz         |
      |  2 | Lilliana Angelovska |
      +----+---------------------+
 
      SELECT * FROM shirt;
      +----+---------+--------+-------+
      | id | style   | color  | owner |
      +----+---------+--------+-------+
      |  1 | polo    | blue   |     1 |
      |  2 | dress   | white  |     1 |
      |  3 | t-shirt | blue   |     1 |
      |  4 | dress   | orange |     2 |
      |  5 | polo    | red    |     2 |
      |  6 | dress   | blue   |     2 |
      |  7 | t-shirt | white  |     2 |
      +----+---------+--------+-------+
 
      SELECT s.* FROM person p, shirt s
       WHERE p.name LIKE 'Lilliana%'
         AND s.owner = p.id
         AND s.color <> 'white';
 
      +----+-------+--------+-------+
      | id | style | color  | owner |
      +----+-------+--------+-------+
      |  4 | dress | orange |     2 |
      |  5 | polo  | red    |     2 |
      |  6 | dress | blue   |     2 |
      +----+-------+--------+-------+
 
 When used in this fashion, the `REFERENCES' clause is not displayed in
 the output of `SHOW CREATE TABLE' or `DESCRIBE':
 
      SHOW CREATE TABLE shirt\G
      *************************** 1. row ***************************
      Table: shirt
      Create Table: CREATE TABLE `shirt` (
      `id` smallint(5) unsigned NOT NULL auto_increment,
      `style` enum('t-shirt','polo','dress') NOT NULL,
      `color` enum('red','blue','orange','white','black') NOT NULL,
      `owner` smallint(5) unsigned NOT NULL,
      PRIMARY KEY  (`id`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
 The use of `REFERENCES' in this way as a comment or `reminder' in a
 column definition works with both `MyISAM' and `BerkeleyDB' tables.
 

Info Catalog

(mysql.info) example-user-variables

(mysql.info) examples

(mysql.info) searching-on-two-keys