mysql 中的约束
1、约束保证数据的完整性和一致性。
2、约束分为表级约束和列级约束。（前：针对多个字段，后：针对一个字段）
3、约束类型包括：
not null(非空约束)
primary key(主键约束)
unique key(唯一约束)
default(默认约束)
foreign key(外键约束)
4、foreign key(外键约束)：
约束保证数据的完整性和一致性
实现一对一或一对多的关系
4.1、要求：
（1）、父表和子表必须使用相同的存储引擎，而且禁止使用临时表；
（2）、数据表的存储引擎只能为InnoDB；
（3）、外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符
的长度则可以不同；
（4）、外键列和参照列必须创建索引。如果外键列不存在索引的话，mysql将自动创建索引。
drop table tb_name;删除表
4.2、编辑数据表的默认存储引擎
MySQL配置文件：my-default.ini中：
添加：default-storeage-engine=INNODB
4.3、| jisuanji |
| kaoshi |
| kepuduwu |
| liangxingguanxi |
| lishi |
| lvyou |
| nonglin |
| pengren |
| qinchunwenxue |
| qinzi |
| shehuikexue |
| shishang |
| shougong |
| tb2 |
| tb3 |
| tiyu |
| tongshu |
| touzilicai |
| waiyu |
| wenhua |
| wenxue |
| xiaoshuo |
| xinlixue |
| xiuxian |
| yinwenyuanbanshu |
| yishu |
| yixue |
| yuer |
| yunchan |
| zhengzhi |
| zhexue |
| zhongxiaoxuejiaofu |
| zhuanji |
| zirankexue |
+--------------------+
46 rows in set (0.90 sec)
mysql> drop tb3;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'tb3'
at line 1
mysql> drop table tb3;
Query OK, 0 rows affected (1.48 sec)
mysql> drop table tb2;
Query OK, 0 rows affected (0.50 sec)
mysql> show tables;
+--------------------+
| Tables_in_test |
+--------------------+
| baojian |
| bookdatafenxi |
| chenggong |
| dongman |
| falv |
| gongjushu |
| gongyesheji |
| guanli |
| jianzhu |
| jiaocai |
| jiating |
| jingji |
| jisuanji |
| kaoshi |
| kepuduwu |
| liangxingguanxi |
| lishi |
| lvyou |
| nonglin |
| pengren |
| qinchunwenxue |
| qinzi |
| shehuikexue |
| shishang |
| shougong |
| tiyu |
| tongshu |
| touzilicai |
| waiyu |
| wenhua |
| wenxue |
| xiaoshuo |
| xinlixue |
| xiuxian |
| yinwenyuanbanshu |
| yishu |
| yixue |
| yuer |
| yunchan |
| zhengzhi |
| zhexue |
| zhongxiaoxuejiaofu |
| zhuanji |
| zirankexue |
+--------------------+
44 rows in set (0.00 sec)
mysql> drop tables;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
mysql> create table provice(
-> id smallint unsigned primary key,
->
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 2
mysql> create table provice(
-> id smallint unsigned primary key auto-increment,
-> pname varchar(20) not null);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'auto-
increment,
pname varchar(20) not null)' at line 2
mysql> CREATE TABLE provice(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO-INCREMENT,
-> pname VARCHAR(20) NOT NULL
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'AUTO-
INCREMENT,
pname VARCHAR(20) NOT NULL
)' at line 2
mysql> CREATE TABLE provice(
-> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> pname VARCHAR(20) NOT NULL
-> );
Query OK, 0 rows affected (1.10 sec)
mysql> show create table provice;
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+
| Table | Create Table
|
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+
| provice | CREATE TABLE `provice` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`pname` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------+
1 row in set (0.15 sec)
mysql> create table users(
-> id smallint unsigned primary key auto_increment,
-> username varchar(10) not null,
-> pid smallint,
-> foreion key (pid) references provice (id)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'key (
pid) references provice (id)
)' at line 5
mysql> create table users(
-> id smallint unsigned primary key auto_increment,
-> username varchar(10) not null,
-> pid smallintunsigned,
-> foreion key (pid) references provice (id)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'small
intunsigned,
foreion key (pid) references provice (id)
)' at line 4
mysql> create table users(
-> id smallint unsigned primary key auto_increment,
-> username varchar(10) not null,
-> pid smallint unsigned,
-> foreion key (pid) references provice (id)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'key (
pid) references provice (id)
)' at line 5
mysql> create table users(
-> id smallint unsigned primary key auto_increment,
-> username varchar(10) not null,
-> pid smallint unsigned,
-> foreion key(pid) references provice(id)
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'key(p
id) references provice(id)
)' at line 5
mysql> create table users(
-> id smallint unsigned primary key auto_increment,
-> username varchar(10) not null,
-> pid smallint unsigned,
-> foreign key(pid) references provice(id)
-> );
Query OK, 0 rows affected (0.78 sec)
mysql> show indexes from provice;
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Car
dinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+---------------+
| provice | 0 | PRIMARY | 1 | id | A |
0 | NULL | NULL | | BTREE | | |
+---------+------------+----------+--------------+-------------+-----------+----
---------+----------+--------+------+------------+---------+---------------+
1 row in set (0.16 sec)
mysql> show indexes from provice\G;
*************************** 1. row ***************************
Table: provice
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show indexes from users\G;
*************************** 1. row ***************************
Table: users
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: users
Non_unique: 1
Key_name: pid
Seq_in_index: 1
Column_name: pid
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.21 sec)
ERROR:
No query specified
mysql> show create table users;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
+
| users | CREATE TABLE `users` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `users_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provice` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
+
1 row in set (0.02 sec)
mysql>
5、外键约束的参照操作
5.1、cascade:从父表删除或更新且自动删除或更新子表中匹配的行
5.2、set null:从父表删除或更新行，并且设置子表中的外键列
为null。如果使用该选项，必须保证子表列没有指定的not null；
5.3、restrict: 拒绝对父表的删除或更新
5.4、no action:标准sql的关键字，在mysql中与restrict相同
注：先在父表插入记录
mysql> create table user1(
-> id smallint unsigned primary key auto_increment,
-> username varchar(10) not null,
-> pid smallint unsigned,
-> foreign key(pid) references provice(id) cascade
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'casca
de
)' at line 5
mysql> create table user1(
-> id smallint unsigned primary key auto_increment,
-> username varchar(10) not null,
-> pid smallint unsigned,
-> foreign key(pid) references provice(id) on delete cascade
-> );
Query OK, 0 rows affected (0.66 sec)
mysql> show create table user1;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------+
| user1 | CREATE TABLE `user1` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `user1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provice` (`id`) ON D
ELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------+
1 row in set (0.08 sec)
mysql> show create table user1\G;
*************************** 1. row ***************************
Table: user1
Create Table: CREATE TABLE `user1` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`pid` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
CONSTRAINT `user1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provice` (`id`) ON D
ELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> insert provice(pname) values('A');
Query OK, 1 row affected (0.19 sec)
mysql> insert provice(pname) values('B');
Query OK, 1 row affected (0.12 sec)
mysql> insert provice(pname) values('C');
Query OK, 1 row affected (0.04 sec)
mysql> select * from provice;
+----+-------+
| id | pname |
+----+-------+
| 1 | A |
| 2 | B |
| 3 | C |
+----+-------+
3 rows in set (0.11 sec)
mysql> insert user1(username,pid) values('Tom',3);
Query OK, 1 row affected (0.10 sec)
mysql> insert user1(username,pid) values('Jon',7);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test`.`user1`, CONSTRAINT `user1_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `
provice` (`id`) ON DELETE CASCADE)
mysql> insert user1(username,pid) values('Rose',1);
Query OK, 1 row affected (0.11 sec)
mysql> insert user1(username,pid) values('Jon',3);
Query OK, 1 row affected (0.12 sec)
mysql> select * from user1;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 1 | Tom | 3 |
| 3 | Rose | 1 |
| 4 | Jon | 3 |
+----+----------+------+
3 rows in set (0.00 sec)
mysql> delete from provice where id=3;
Query OK, 1 row affected (0.15 sec)
mysql> select * from provice;
+----+-------+
| id | pname |
+----+-------+
| 1 | A |
| 2 | B |
+----+-------+
2 rows in set (0.00 sec)
mysql> select * from user1;
+----+----------+------+
| id | username | pid |
+----+----------+------+
| 3 | Rose | 1 |
+----+----------+------+
1 row in set (0.00 sec)
