MySQL Tutorial/Table/Foreign Keys

Материал из SQL эксперт
Перейти к: навигация, поиск

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

   <source lang="sql">

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)</source>


Alter table to drop primary key and foreign key

   <source lang="sql">

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)</source>


FOREIGN KEY ON DELETE CASCADE ON UPDATE CASCADE

   <source lang="sql">

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></source>


Implement a many-to-many map

   <source lang="sql">

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></source>


One table with two foreign keys

   <source lang="sql">

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)</source>


Using AUTO_INCREMENT

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



   <source lang="sql">

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></source>


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.



   <source lang="sql">

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></source>

Источник — «http://sqle.ru/index.php?title=MySQL_Tutorial/Table/Foreign_Keys&oldid=50»