mysql 外键notnull_mysql中外键的一些基本操作

1 带外键的两个表，当插入记录的时候必须先在父表中插入记录，然后在子表中插入记录2 父表3 mysql>create table provinces(4 ->id smallint unsigned primary key auto_increment,5 -> pname varchar(30) not null

6 ->);7

8 查看数据表引擎9 mysql>show create table provinces;10 +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

11 | Table | Create Table |

12 +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

13 | provinces |CREATE TABLE `provinces` (14 `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,15 `pname` varchar(30) NOT NULL,16 PRIMARY KEY (`id`)17 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

18 +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

19 1 row in set (0.00sec)20

21 子表(子表中的外键的数据类型和父表中对应做外键的键的数据类型要一致，不然会出错)22 创造外键23 mysql>create table users(24 ->id smallint unsigned primary key auto_increment,25 -> username varchar(30) not null,26 ->pid smallint unsigned,27 ->foreign key (pid) references provinces (id)28 ->);29 Query OK, 0 rows affected (0.65sec)30

31 cascade 从父表删除或更新且自动删除或更新子表中匹配的行32 set null从父表删除或更新行，并设置子表中的外键列为null。如果使用该选项，必须保证33 子表列没有指定not null

34 restrict 拒绝对父表的删除或更新操作35 no action 标准SQL关键字，在MySQL中雨restrict相同36

37 创建数据表，加上cascade38 mysql>create table users1(39 ->id smallint unsigned primary key auto_increment,40 -> username varchar(30) not null,41 ->pid smallint unsigned,42 -> foreign key (pid) references provinces (id) on delete cascade ->);43 Query OK, 0 rows affected (0.65sec)44

45

46 对数据表进行添加删除47 mysql> insert provinces (pname) values ('A');48 Query OK, 1 row affected (0.11sec)49

50 mysql> insert provinces (pname) values ('B');51 Query OK, 1 row affected (0.08sec)52

53 mysql> insert provinces (pname) values ('C');54 Query OK, 1 row affected (0.05sec)55

56 mysql> select * fromprovinces;57 +----+-------+

58 | id | pname |

59 +----+-------+

60 | 1 | A |

61 | 2 | B |

62 | 3 | C |

63 +----+-------+

64 3 rows in set (0.00sec)65

66 mysql> insert users1 (username, pid) values('Mike', 3);67 Query OK, 1 row affected (0.09sec)68

69 由于父表中不存在id等于7的情况，所以报错70 mysql> insert users1 (username, pid) values('John', 7);71 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`t1`.`users1`, CONSTRAINT `users1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`) ON DELETE CASCADE)72 mysql> insert users1 (username, pid) values('John', 2);73 Query OK, 1 row affected (0.24sec)74

75 mysql> insert users1 (username, pid) values('Tom', 1);76 Query OK, 1 row affected (0.09sec)77

78 mysql> select * fromusers1;79 +----+----------+------+

80 | id | username | pid |

81 +----+----------+------+

82 | 1 | Mike | 3 |

83 | 3 | John | 2 |

84 | 4 | Tom | 1 |

85 +----+----------+------+

86 3 rows in set (0.00sec)87

88 删除父表中id为3的记录89 mysql> delete from provinces where id = 3;90 Query OK, 1 row affected (0.15sec)91

92 mysql> select * fromprovinces;93 +----+-------+

94 | id | pname |

95 +----+-------+

96 | 1 | A |

97 | 2 | B |

98 +----+-------+

99 2 rows in set (0.00sec)100

101 对子表产生了影响102 mysql> select * fromusers1;103 +----+----------+------+

104 | id | username | pid |

105 +----+----------+------+

106 | 3 | John | 2 |

107 | 4 | Tom | 1 |

108 +----+----------+------+

109 2 rows in set (0.00sec)110

111 数据表的更新操作：112 update set命令用来修改表中的数据。113 update set命令格式：update 表名 set 字段=新值,… where条件;114

115 mysql> update users1 set username = 'wuxie' where id = 3;116 Query OK, 1 row affected (0.15sec)117 Rows matched: 1 Changed: 1 Warnings: 0

118

119 mysql> select * fromusers1;120 +----+----------+------+

121 | id | username | pid |

122 +----+----------+------+

123 | 3 | wuxie | 2 |

124 | 4 | Tom | 1 |

125 +----+----------+------+

126 2 rows in set (0.00sec)127

128 mysql> update users1 set username = 'John', pid = 3 where id = 1;129 Query OK, 0 rows affected (0.00sec)130 Rows matched: 0 Changed: 0 Warnings: 0

131

132 mysql> select * fromusers1;133 +----+----------+------+

134 | id | username | pid |

135 +----+----------+------+

136 | 3 | wuxie | 2 |

137 | 4 | Tom | 1 |

138 +----+----------+------+

139 2 rows in set (0.00 sec)