mysql 外键约束 笔记

创建一个父表second

    MariaDB [test]> CREATE TABLE second(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> username VARCHAR(10) NOT NULL
    -> );
    Query OK, 0 rows affected (0.30 sec)

再创建一个子表

    CREATE TABLE users(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> pname VARCHAR(10) NOT NULL,
    -> pid BIGINT,   // 外键约束，与外键的值数据类型必须相同！！（否则出现err 150）
                    // pid 的id 数据类型是 SMALLINT UNSIGNED
                    // 所以此处应该是 pid SMALLINT UNSIGNED
    -> FOREIGN KEY(pid) REFERENCES second(id));
            AUTO_INCREMENT, ： 往后的ID自动添加编号
    FOREIGN KEY 约束
        用于指向另外一个表的约束
    REFERENCES	
            Id‘值’参考second(id)

我在创建一个third1

MariaDB [test]> CREATE TABLE third1(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname varchar(10) not null,
-> pid SMALLINT UNSIGNED,
-> foreign key (pid) references second(id) on delete cascade);
On delete cascade （级联表）

当我在third1中插入表时；

MariaDB [test]> SELECT * FROM second;
+----+----------+
| id | username |
+----+----------+
|  1 | A        |
|  2 | B        |
|  3 | C        |
|  4 | YES      |
|  5 | A        |
|  6 | B        |
|  7 | C        |
+----+----------+

当我把id=7的表删除时，以inster inton third(pname,pid) values(“Tom”,7)插入的表也会随之删除

插入

MariaDB [test]> insert into second(username) values('A');
Query OK, 1 row affected (0.05 sec)

MariaDB [test]> insert into second(username) values('B');
Query OK, 1 row affected (0.06 sec)

MariaDB [test]> insert into second(username) values('C');
Query OK, 1 row affected (0.05 sec)

MariaDB [test]> SELECT * FROM second;   //显示父表
+----+----------+ 
| id | username |
+----+----------+
|  1 | A        |
|  2 | B        |
|  3 | C        |
|  4 | YES      |
|  5 | A        |
|  6 | B        |
|  7 | C        |
+----+----------+
7 rows in set (0.00 sec)

插入子标

MariaDB [test]> insert into second(pname,pid) values('Tom',7);
ERROR 1054 (42S22): Unknown column 'pname' in 'field list'   //！！没有secon中没有pname

//使用pid为参考也就是父表 id

MariaDB [test]> insert into third1(pname,pid) values('Tom',7);  
Query OK, 1 row affected (0.07 sec)
MariaDB [test]> insert into third1(pname,pid) values('ord',7);
Query OK, 1 row affected (0.04 sec)
MariaDB [test]> SELECT * FROM third1;
+----+-------+------+
| id | pname | pid  |
+----+-------+------+
|  1 | Tom   |    6 |
|  3 | Tom   |    7 |
|  4 | ord   |    7 |
+----+-------+------+
3 rows in set (0.00 sec)

当我删除父表中的 pid=7 时

delete from second where id=7;

可以看出，子表的内容也相应的改变了

MariaDB [test]> SELECT * FROM third1;
+----+-------+------+
| id | pname | pid  |
+----+-------+------+
|  1 | Tom   |    6 |
+----+-------+------+
1 row in set (0.00 sec)

学习笔记：慕课网：http://www.imooc.com/video/2169