(mysql.info.gz) example-Foreign keys

Info Catalog

(mysql.info.gz) example-user-variables

(mysql.info.gz) Examples

(mysql.info.gz) Searching on two keys

 
 3.6.6 Using Foreign Keys
 ------------------------
 
 In MySQL 3.23.44 and up, `InnoDB' tables support checking of foreign
 key constraints.  `InnoDB' InnoDB.  See also  ANSI diff
 Foreign Keys.
 
 You don't actually need foreign keys to join two tables.  For table
 types other than `InnoDB', the only things MySQL currently doesn't do
 are 1) `CHECK' to make sure that the keys you use really exist in the
 table or tables you're referencing and 2) automatically delete rows
 from a table with a foreign key definition. Using your keys to join
 tables will work just fine:
 
      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 |
      +----+-------+--------+-------+
 

Info Catalog

(mysql.info.gz) example-user-variables

(mysql.info.gz) Examples

(mysql.info.gz) Searching on two keys