MySQL Tutorial/Table/Foreign Keys

Add Index and primary key to a table in table creation command

mysql>
mysql> CREATE TABLE myTable
    -> (
    ->    OrderID SMALLINT UNSIGNED NOT NULL,
    ->    ModelID SMALLINT UNSIGNED NOT NULL,
    ->    PRIMARY KEY (OrderID),
    ->    INDEX (ModelID)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> desc myTable;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| OrderID | smallint(5) unsigned | NO   | PRI |         |       |
| ModelID | smallint(5) unsigned | NO   | MUL |         |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)

Alter table to drop primary key and foreign key

mysql>
mysql>
mysql> CREATE TABLE Publishers
    -> (
    ->    PubID SMALLINT NOT NULL DEFAULT 1
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql>
mysql> CREATE TABLE Books
    -> (
    ->    BookID SMALLINT NOT NULL,
    ->    BookName VARCHAR(40) NOT NULL,
    ->    PubID SMALLINT NOT NULL DEFAULT 1
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql>
mysql> ALTER TABLE Books
    -> ADD PRIMARY KEY (BookID),
    -> ADD CONSTRAINT fk_1 FOREIGN KEY (PubID) REFERENCES Publishers (PubID),
    -> ADD COLUMN Format ENUM("paperback", "hardcover") NOT NULL AFTER BookName;
ERROR 1005 (HY000): Can"t create table ".\test\#sql-a98_3a.frm" (errno: 150)
mysql>
mysql> desc Books;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| BookID   | smallint(6) | NO   |     |         |       |
| BookName | varchar(40) | NO   |     |         |       |
| PubID    | smallint(6) | NO   |     | 1       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> desc Publishers;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| PubID | smallint(6) | NO   |     | 1       |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql>
mysql>
mysql>
mysql> ALTER TABLE Books
    -> DROP PRIMARY KEY,
    -> DROP FOREIGN KEY fk_1,
    -> DROP COLUMN Format;
ERROR 1091 (42000): Can"t DROP "PRIMARY"; check that column/key exists
mysql>
mysql>
mysql> desc Books;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| BookID   | smallint(6) | NO   |     |         |       |
| BookName | varchar(40) | NO   |     |         |       |
| PubID    | smallint(6) | NO   |     | 1       |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql>
mysql> desc Publishers;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| PubID | smallint(6) | NO   |     | 1       |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql>
mysql> drop table Books;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> drop table Publishers;
Query OK, 0 rows affected (0.03 sec)

FOREIGN KEY ON DELETE CASCADE ON UPDATE CASCADE

mysql>
mysql> CREATE TABLE Models
    -> (
    ->    ModelID SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->    Name VARCHAR(40) NOT NULL,
    ->    PRIMARY KEY (ModelID)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> CREATE TABLE Orders
    -> (
    ->    ID          SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    ModelID     SMALLINT UNSIGNED NOT NULL,
    ->    Description VARCHAR(40),
    ->    FOREIGN KEY (ModelID) REFERENCES Models (ModelID)
    ->       ON DELETE CASCADE ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> desc Models;
+---------+----------------------+------+-----+---------+----------------+
| Field   | Type                 | Null | Key | Default | Extra          |
+---------+----------------------+------+-----+---------+----------------+
| ModelID | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| Name    | varchar(40)          | NO   |     |         |                |
+---------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql>
mysql> desc Orders;
+-------------+----------------------+------+-----+---------+-------+
| Field       | Type                 | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| ID          | smallint(5) unsigned | NO   | PRI |         |       |
| ModelID     | smallint(5) unsigned | NO   | MUL |         |       |
| Description | varchar(40)          | YES  |     | NULL    |       |
+-------------+----------------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql>
mysql> drop table Orders;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop table Models;
Query OK, 0 rows affected (0.00 sec)
mysql>

Implement a many-to-many map

mysql>
mysql>
mysql> CREATE TABLE Books(
    ->    BookID SMALLINT NOT NULL PRIMARY KEY,
    ->    BookTitle VARCHAR(60) NOT NULL,
    ->    Copyright YEAR NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> INSERT INTO Books VALUES (12786, "Java",           1934),
    ->                          (13331, "MySQL",          1919),
    ->                          (14356, "PHP",            1966),
    ->                          (15729, "PERL",           1932),
    ->                          (16284, "Oracle",         1996),
    ->                          (17695, "Pl/SQL",         1980),
    ->                          (19264, "JavaScript",     1992),
    ->                          (19354, "www.sqle.ru", 1993);
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0
mysql>
mysql>
mysql> CREATE TABLE Authors(
    ->    AuthID SMALLINT NOT NULL PRIMARY KEY,
    ->    AuthFN VARCHAR(20),
    ->    AuthMN VARCHAR(20),
    ->    AuthLN VARCHAR(20)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> INSERT INTO Authors VALUES (1006, "H", "S.", "T"),
    ->                            (1007, "J", "C",  "O"),
    ->                            (1008, "B", NULL, "E"),
    ->                            (1009, "R", "M",  "R"),
    ->                            (1010, "J", "K",  "T"),
    ->                            (1011, "J", "G.", "N"),
    ->                            (1012, "A", NULL, "P"),
    ->                            (1013, "A", NULL, "W"),
    ->                            (1014, "N", NULL, "A");
Query OK, 9 rows affected (0.02 sec)
Records: 9  Duplicates: 0  Warnings: 0
mysql>
mysql>
mysql> CREATE TABLE AuthorBook(
    ->    AuthID SMALLINT NOT NULL,
    ->    BookID SMALLINT NOT NULL,
    ->    PRIMARY KEY (AuthID, BookID),
    ->    FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
    ->    FOREIGN KEY (BookID) REFERENCES Books (BookID)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> INSERT INTO AuthorBook VALUES (1006, 14356),
    ->                               (1008, 15729),
    ->                               (1009, 12786),
    ->                               (1010, 17695),
    ->                               (1011, 15729),
    ->                               (1012, 19264),
    ->                               (1012, 19354),
    ->                               (1014, 16284);
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0
mysql>
mysql>
mysql> select * from Authors;
+--------+--------+--------+--------+
| AuthID | AuthFN | AuthMN | AuthLN |
+--------+--------+--------+--------+
|   1006 | H      | S.     | T      |
|   1007 | J      | C      | O      |
|   1008 | B      | NULL   | E      |
|   1009 | R      | M      | R      |
|   1010 | J      | K      | T      |
|   1011 | J      | G.     | N      |
|   1012 | A      | NULL   | P      |
|   1013 | A      | NULL   | W      |
|   1014 | N      | NULL   | A      |
+--------+--------+--------+--------+
9 rows in set (0.00 sec)
mysql> select * from Books;
+--------+----------------+-----------+
| BookID | BookTitle      | Copyright |
+--------+----------------+-----------+
|  12786 | Java           |      1934 |
|  13331 | MySQL          |      1919 |
|  14356 | PHP            |      1966 |
|  15729 | PERL           |      1932 |
|  16284 | Oracle         |      1996 |
|  17695 | Pl/SQL         |      1980 |
|  19264 | JavaScript     |      1992 |
|  19354 | www.sqle.ru |      1993 |
+--------+----------------+-----------+
8 rows in set (0.00 sec)
mysql> select * from AuthorBook;
+--------+--------+
| AuthID | BookID |
+--------+--------+
|   1009 |  12786 |
|   1006 |  14356 |
|   1008 |  15729 |
|   1011 |  15729 |
|   1014 |  16284 |
|   1010 |  17695 |
|   1012 |  19264 |
|   1012 |  19354 |
+--------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> SELECT BookTitle, Copyright, AuthID
    -> FROM Books AS b, AuthorBook AS ab
    -> WHERE b.BookID=ab.BookID
    -> ORDER BY BookTitle;
+----------------+-----------+--------+
| BookTitle      | Copyright | AuthID |
+----------------+-----------+--------+
| Java           |      1934 |   1009 |
| JavaScript     |      1992 |   1012 |
| Oracle         |      1996 |   1014 |
| PERL           |      1932 |   1008 |
| PERL           |      1932 |   1011 |
| PHP            |      1966 |   1006 |
| Pl/SQL         |      1980 |   1010 |
| www.sqle.ru |      1993 |   1012 |
+----------------+-----------+--------+
8 rows in set (0.00 sec)
mysql>
mysql> drop table AuthorBook;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table Books;
Query OK, 0 rows affected (0.05 sec)
mysql> drop table Authors;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql>

One table with two foreign keys

mysql>
mysql> CREATE TABLE Books(
    ->    BookID SMALLINT NOT NULL PRIMARY KEY,
    ->    BookTitle VARCHAR(60) NOT NULL,
    ->    Copyright YEAR NOT NULL
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> INSERT INTO Books VALUES (12786, "Java",           1934),
    ->                          (13331, "MySQL",          1919),
    ->                          (14356, "PHP",            1966),
    ->                          (15729, "PERL",           1932),
    ->                          (16284, "Oracle",         1996),
    ->                          (17695, "Pl/SQL",         1980),
    ->                          (19264, "JavaScript",     1992),
    ->                          (19354, "www.sqle.ru", 1993);
Query OK, 8 rows affected (0.05 sec)
Records: 8  Duplicates: 0  Warnings: 0
mysql>
mysql>
mysql> CREATE TABLE Authors(
    ->    AuthID SMALLINT NOT NULL PRIMARY KEY,
    ->    AuthFN VARCHAR(20),
    ->    AuthMN VARCHAR(20),
    ->    AuthLN VARCHAR(20)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> INSERT INTO Authors VALUES (1006, "H", "S.", "T"),
    ->                            (1007, "J", "C",  "O"),
    ->                            (1008, "B", NULL, "E"),
    ->                            (1009, "R", "M",  "R"),
    ->                            (1010, "J", "K",  "T"),
    ->                            (1011, "J", "G.", "N"),
    ->                            (1012, "A", NULL, "P"),
    ->                            (1013, "A", NULL, "W"),
    ->                            (1014, "N", NULL, "A");
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0
mysql>
mysql>
mysql> CREATE TABLE AuthorBook(
    ->    AuthID SMALLINT NOT NULL,
    ->    BookID SMALLINT NOT NULL,
    ->    PRIMARY KEY (AuthID, BookID),
    ->    FOREIGN KEY (AuthID) REFERENCES Authors (AuthID),
    ->    FOREIGN KEY (BookID) REFERENCES Books (BookID)
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql>
mysql> INSERT INTO AuthorBook VALUES (1006, 14356),
    ->                               (1008, 15729),
    ->                               (1009, 12786),
    ->                               (1010, 17695),
    ->                               (1011, 15729),
    ->                               (1012, 19264),
    ->                               (1012, 19354),
    ->                               (1014, 16284);
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0
mysql>
mysql>
mysql> select * from Authors;
+--------+--------+--------+--------+
| AuthID | AuthFN | AuthMN | AuthLN |
+--------+--------+--------+--------+
|   1006 | H      | S.     | T      |
|   1007 | J      | C      | O      |
|   1008 | B      | NULL   | E      |
|   1009 | R      | M      | R      |
|   1010 | J      | K      | T      |
|   1011 | J      | G.     | N      |
|   1012 | A      | NULL   | P      |
|   1013 | A      | NULL   | W      |
|   1014 | N      | NULL   | A      |
+--------+--------+--------+--------+
9 rows in set (0.00 sec)
mysql> select * from Books;
+--------+----------------+-----------+
| BookID | BookTitle      | Copyright |
+--------+----------------+-----------+
|  12786 | Java           |      1934 |
|  13331 | MySQL          |      1919 |
|  14356 | PHP            |      1966 |
|  15729 | PERL           |      1932 |
|  16284 | Oracle         |      1996 |
|  17695 | Pl/SQL         |      1980 |
|  19264 | JavaScript     |      1992 |
|  19354 | www.sqle.ru |      1993 |
+--------+----------------+-----------+
8 rows in set (0.00 sec)
mysql> select * from AuthorBook;
+--------+--------+
| AuthID | BookID |
+--------+--------+
|   1009 |  12786 |
|   1006 |  14356 |
|   1008 |  15729 |
|   1011 |  15729 |
|   1014 |  16284 |
|   1010 |  17695 |
|   1012 |  19264 |
|   1012 |  19354 |
+--------+--------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT BookTitle, AuthID FROM Books, AuthorBook;
+----------------+--------+
| BookTitle      | AuthID |
+----------------+--------+
| Java           |   1006 |
| MySQL          |   1006 |
| PHP            |   1006 |
| PERL           |   1006 |
| Oracle         |   1006 |
| Pl/SQL         |   1006 |
| JavaScript     |   1006 |
| www.sqle.ru |   1006 |
| Java           |   1008 |
| MySQL          |   1008 |
| PHP            |   1008 |
| PERL           |   1008 |
| Oracle         |   1008 |
| Pl/SQL         |   1008 |
| JavaScript     |   1008 |
| www.sqle.ru |   1008 |
| Java           |   1009 |
| MySQL          |   1009 |
| PHP            |   1009 |
| PERL           |   1009 |
| Oracle         |   1009 |
| Pl/SQL         |   1009 |
| JavaScript     |   1009 |
| www.sqle.ru |   1009 |
| Java           |   1010 |
| MySQL          |   1010 |
| PHP            |   1010 |
| PERL           |   1010 |
| Oracle         |   1010 |
| Pl/SQL         |   1010 |
| JavaScript     |   1010 |
| www.sqle.ru |   1010 |
| Java           |   1011 |
| MySQL          |   1011 |
| PHP            |   1011 |
| PERL           |   1011 |
| Oracle         |   1011 |
| Pl/SQL         |   1011 |
| JavaScript     |   1011 |
| www.sqle.ru |   1011 |
| Java           |   1012 |
| MySQL          |   1012 |
| PHP            |   1012 |
| PERL           |   1012 |
| Oracle         |   1012 |
| Pl/SQL         |   1012 |
| JavaScript     |   1012 |
| www.sqle.ru |   1012 |
| Java           |   1012 |
| MySQL          |   1012 |
| PHP            |   1012 |
| PERL           |   1012 |
| Oracle         |   1012 |
| Pl/SQL         |   1012 |
| JavaScript     |   1012 |
| www.sqle.ru |   1012 |
| Java           |   1014 |
| MySQL          |   1014 |
| PHP            |   1014 |
| PERL           |   1014 |
| Oracle         |   1014 |
| Pl/SQL         |   1014 |
| JavaScript     |   1014 |
| www.sqle.ru |   1014 |
+----------------+--------+
64 rows in set (0.00 sec)
mysql>
mysql> drop table AuthorBook;
Query OK, 0 rows affected (0.03 sec)
mysql> drop table Books;
Query OK, 0 rows affected (0.06 sec)
mysql> drop table Authors;
Query OK, 0 rows affected (0.05 sec)

Using AUTO_INCREMENT

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

mysql>
mysql> CREATE TABLE employee (
    ->      id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->      name CHAR(30) NOT NULL,
    ->      PRIMARY KEY (id)
    ->  );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> desc employee;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| name  | char(30)     | NO   |     |         |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.02 sec)
mysql>
mysql> INSERT INTO employee (name) VALUES ("A"),
    ->                                    ("B"),
    ->                                    ("C"),
    ->                                    ("D"),
    ->                                    ("E"),
    ->                                    ("F");
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
mysql>
mysql> SELECT * FROM employee;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | D    |
|  5 | E    |
|  6 | F    |
+----+------+
6 rows in set (0.00 sec)
mysql>
mysql> drop table employee;
Query OK, 0 rows affected (0.00 sec)
mysql>

Using Foreign Keys

In MySQL, InnoDB tables support checking of foreign key constraints.

For non-InnoDB tables, REFERENCES tbl_name(col_name) clause has no actual effect.

It serves only as a comment to the column.

mysql> CREATE TABLE person (
    ->     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     name CHAR(60) NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> CREATE TABLE shirt (
    ->     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->     owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> desc person;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(60)             | NO   |     |         |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> desc shirt;
+-------+----------------------+------+-----+---------+----------------+
| Field | Type                 | Null | Key | Default | Extra          |
+-------+----------------------+------+-----+---------+----------------+
| id    | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| owner | smallint(5) unsigned | NO   |     |         |                |
+-------+----------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql>
mysql> drop table shirt;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table person;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>