MySQL数据库——表的约束（一）

五、表的约束

表的约束：表中一定要有各种约束，通过约束，让我们未来插入数据库表中的数据是符合预期的。约束本质是通过技术手段，倒逼程序员，插入正确的数据。反过来，站在mysql的视角，凡是插入进来的数据，都是符合数据约束的！

真正约束字段的是数据类型，但是数据类型约束很单一，需要有一些额外的约束，更好的保证数据的合法性，从业务逻辑角度保证数据的正确性。比如有一个字段是email,要求是唯一的。

约束的最终目标：保证数据的完整性和可预期性

表的约束很多，这里主要介绍如下几个：nu11/not null，default，comment，zerofill，primary key，auto_increment，unique key

1.空属性

• 两个值：null（默认的）和not null（不为空）
• 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

-- name和room都设置为非空
mysql> create table myclass(
    -> class_name varchar(20) not null,
    -> class_room varchar(20) not null,
    -> other varchar(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc myclass;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| class_name | varchar(20) | NO   |     | NULL    |       |
| class_room | varchar(20) | NO   |     | NULL    |       |
| other      | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table myclass\G
*************************** 1. row ***************************
       Table: myclass
Create Table: CREATE TABLE `myclass` (
  `class_name` varchar(20) NOT NULL,
  `class_room` varchar(20) NOT NULL,
  `other` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into myclass (class_name,class_room,other) values ('高三2班','101教室','普通班');
Query OK, 1 row affected (0.01 sec)

mysql> select* from myclass;
+------------+------------+-----------+
| class_name | class_room | other     |
+------------+------------+-----------+
| 高三2班    | 101教室    | 普通班    |
+------------+------------+-----------+
1 row in set (0.00 sec)

-- other可以是NULL
mysql> insert into myclass (class_name,class_room) values ('高三3班','103教室');
Query OK, 1 row affected (0.00 sec)

mysql> select* from myclass;
+------------+------------+-----------+
| class_name | class_room | other     |
+------------+------------+-----------+
| 高三2班    | 101教室    | 普通班    |
| 高三3班    | 103教室    | NULL      |
+------------+------------+-----------+
2 rows in set (0.00 sec)

-- 由于name和room都设置了非空，都不能设置NULL或者不进行插入数据
mysql> insert into myclass (class_name,class_room) values ('高三3班',NULL);
ERROR 1048 (23000): Column 'class_room' cannot be null
mysql> insert into myclass (class_name) values ('高三3班');
ERROR 1364 (HY000): Field 'class_room' doesn't have a default value

2.默认值

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。

mysql> create table t14(
    -> name varchar(20) not null,
    -> age tinyint unsigned default 18,
    -> gender char(1) default '男'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc t14;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 18      |       |
| gender | char(1)             | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t14 (name,age,gender) values ('张三',19,'女');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t14;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
+--------+------+--------+
1 row in set (0.00 sec)

-- 默认值的生效：数据在插入的时候不给该字段赋值，就使用默认值
mysql> insert into t14 (name) values ('李四');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t14;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
| 李四   |   18 | 男     |
+--------+------+--------+
2 rows in set (0.00 sec)

default：如果设置了，用户插入的时候，有具体的数据就用用户的，如果没有就用默认的

如果我们没有明确指定一列要插入，用的是default，如果建表中，对应列默认没有设置default值，无法直接插入。

default和not null 不冲突，而是相互补充的

mysql> create table t15(
    -> name varchar(20) not null,
    -> age tinyint default 18,
    -> gender char(1) not null default '男'
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t15;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name   | varchar(20) | NO   |     | NULL    |       |
| age    | tinyint(4)  | YES  |     | 18      |       |
| gender | char(1)     | NO   |     | 男      |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> show create table t15;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t15   | CREATE TABLE `t15` (
  `name` varchar(20) NOT NULL,
  `age` tinyint(4) DEFAULT '18',
  `gender` char(1) NOT NULL DEFAULT '男'
) ENGINE=InnoDB DEFAULT CHARSET=utf8  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into t15 (name,age,gender) values ('张三',20,'男');
Query OK, 1 row affected (0.00 sec)
-- name不能为空
mysql> insert into t15 (name,age,gender) values (NULL,20,'男');
ERROR 1048 (23000): Column 'name' cannot be null
-- name没有默认值，且不能为空，必须指定
mysql> insert into t15 (age,gender) values (20,'男');
ERROR 1364 (HY000): Field 'name' doesnt have a default value
-- gender不能为空
mysql> insert into t15 (name,age,gender) values ('张三',20,NULL);
ERROR 1048 (23000): Column 'gender' cannot be null
-- gender有默认值，可以不指定
mysql> insert into t15 (name,age) values ('张三',20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t15;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   20 | 男     |
| 张三   |   20 | 男     |
+--------+------+--------+
2 rows in set (0.00 sec)

-- age有默认值且没有非空的约束
mysql> insert into t15 (name,age,gender) values ('张三',30,'女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t15 (name,age,gender) values ('张三',NULL,'女');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t15 (name,gender) values ('张三','女');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t15;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   20 | 男     |
| 张三   |   20 | 男     |
| 张三   |   30 | 女     |
| 张三   | NULL | 女     |
| 张三   |   18 | 女     |
+--------+------+--------+
5 rows in set (0.00 sec)

下面这种情况，并没有指定默认值，但是却可以不写入，因为mysql做了优化，给我们加了默认值NULL

mysql> create table t16(
    -> name varchar(20),
    -> age int
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t16 (name,age) values ('张三',18);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t16;
+--------+------+
| name   | age  |
+--------+------+
| 张三   |   18 |
+--------+------+
1 row in set (0.00 sec)

mysql> insert into t16 (name,age) values (NULL,18);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t16;
+--------+------+
| name   | age  |
+--------+------+
| 张三   |   18 |
| NULL   |   18 |
+--------+------+
2 rows in set (0.00 sec)

mysql> insert into t16 (age) values (18);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t16;
+--------+------+
| name   | age  |
+--------+------+
| 张三   |   18 |
| NULL   |   18 |
| NULL   |   18 |
+--------+------+
3 rows in set (0.00 sec)

mysql> show create table t16\G
*************************** 1. row ***************************
       Table: t16
Create Table: CREATE TABLE `t16` (
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3.列描述

列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

注意：not null 和default一般不需要同时出现，因为default本身有默认值，不会为空

mysql> create table t17(
    -> name varchar(20) not null comment '用户名',
    -> age tinyint unsigned default 18 comment '用户年龄',
    -> gender char(1) default '男' comment '性别'
    -> );
Query OK, 0 rows affected (0.01 sec)

-- 通过desc看不到注释信息
mysql> desc t17;
+--------+---------------------+------+-----+---------+-------+
| Field  | Type                | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| name   | varchar(20)         | NO   |     | NULL    |       |
| age    | tinyint(3) unsigned | YES  |     | 18      |       |
| gender | char(1)             | YES  |     | 男      |       |
+--------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into t17 values('张三',19,'女');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t17;
+--------+------+--------+
| name   | age  | gender |
+--------+------+--------+
| 张三   |   19 | 女     |
+--------+------+--------+
1 row in set (0.00 sec)

-- 通过show可以看到
mysql> show create table t17\G
*************************** 1. row ***************************
       Table: t17
Create Table: CREATE TABLE `t17` (
  `name` varchar(20) NOT NULL COMMENT '用户名',
  `age` tinyint(3) unsigned DEFAULT '18' COMMENT '用户年龄',
  `gender` char(1) DEFAULT '男' COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4.zerofill

mysql> create table t18(
    -> a int unsigned not null,
    -> b int unsigned not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc t18;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| a     | int(10) unsigned | NO   |     | NULL    |       |
| b     | int(10) unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 可以看到int(10),这个代表什么意思呢？整型不是4字节码？这个10又代表什么呢？
-- 其实没有zerofilli这个属性，括号内的数字是毫无意义的。

mysql> show create table t18\G
*************************** 1. row ***************************
       Table: t18
Create Table: CREATE TABLE `t18` (
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into t18 (a,b) values (1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t18;
+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

-- 但是对列添加了zerofill)属性后，显示的结果就有所不同了，修改表的属性：
mysql> alter table t18 modify b int unsigned zerofill not null;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t18;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| a     | int(10) unsigned          | NO   |     | NULL    |       |
| b     | int(10) unsigned zerofill | NO   |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

-- 这次可以看到的值由原来的1变成00001，这就是zerofill/属性的作用
-- 如果宽度小于设定的宽度（这里设置的是5)，自动填充0。
-- 要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。
mysql> select * from t18;
+---+------------+
| a | b          |
+---+------------+
| 1 | 0000000002 |
+---+------------+
1 row in set (0.00 sec)

mysql> insert into t18 (a,b) values (100,200);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t18;
+-----+------------+
| a   | b          |
+-----+------------+
|   1 | 0000000002 |
| 100 | 0000000200 |
+-----+------------+
2 rows in set (0.00 sec)

mysql> select * from t18 where b=200;
+-----+------------+
| a   | b          |
+-----+------------+
| 100 | 0000000200 |
+-----+------------+
1 row in set (0.00 sec)

mysql> select hex(3.14);
+-----------+
| hex(3.14) |
+-----------+
| 3         |
+-----------+
1 row in set (0.00 sec)
-- 用hex函数，可以看出数据库内部存储还是200,0000000200只是设置了zerofill属性后的一种格式化输出而已
-- 这里的C8是八进制，可以用程序员计算器查看

mysql> select a,hex(b) from t18;
+-----+--------+
| a   | hex(b) |
+-----+--------+
|   1 | 2      |
| 100 | C8     |
+-----+--------+
2 rows in set (0.00 sec)

mysql> desc t18;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| a     | int(10) unsigned          | NO   |     | NULL    |       |
| b     | int(10) unsigned zerofill | NO   |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 修改为4，也就是少于四位补0，多于就正常显示
mysql> alter table t18 modify b int(4) unsigned zerofill;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t18;
+-----+------+
| a   | b    |
+-----+------+
|   1 | 0002 |
| 100 | 0200 |
+-----+------+
2 rows in set (0.00 sec)

mysql> insert into t18 values(1,11);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t18 values(1,11);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t18 values(1,111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t18 values(1,1111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t18 values(1,11111);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t18 values(1,111111);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t18;
+-----+--------+
| a   | b      |
+-----+--------+
|   1 |   0002 |
| 100 |   0200 |
|   1 |   0011 |
|   1 |   0011 |
|   1 |   0111 |
|   1 |   1111 |
|   1 |  11111 |
|   1 | 111111 |
+-----+--------+
8 rows in set (0.00 sec)

-- 查询还是正常查询
mysql> select * from t18 where b=11111;;
+---+-------+
| a | b     |
+---+-------+
| 1 | 11111 |
+---+-------+
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from t18 where b=11111;
+---+-------+
| a | b     |
+---+-------+
| 1 | 11111 |
+---+-------+
1 row in set (0.00 sec)

mysql> select * from t18 where b=11;
+---+------+
| a | b    |
+---+------+
| 1 | 0011 |
| 1 | 0011 |
+---+------+
2 rows in set (0.00 sec)

mysql> show create table t18\G
*************************** 1. row ***************************
       Table: t18
Create Table: CREATE TABLE `t18` (
  `a` int(10) unsigned NOT NULL,
  `b` int(4) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

5.主键

主键：primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个（不意味着一个表中的主键，只能添加给一列，一个主键可以被添加到一列或者多列，称为复合主键）

主键：主键所在的列通常是整数类型。

mysql> create table test_key(
    -> id int unsigned primary key comment '学号',
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table test_key;
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                          |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_key | CREATE TABLE `test_key` (
  `id` int(10) unsigned NOT NULL COMMENT '学号',
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8   |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 自动添加not null约束
mysql> show create table test_key\G
*************************** 1. row ***************************
       Table: test_key
Create Table: CREATE TABLE `test_key` (
  `id` int(10) unsigned NOT NULL COMMENT '学号',
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into test_key values (1,'张飞');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_key values (1,'刘备');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into test_key values (2,'刘备');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 刘备   |
+----+--------+
2 rows in set (0.00 sec)

mysql> select * from test_key where id=2;
+----+--------+
| id | name   |
+----+--------+
|  2 | 刘备   |
+----+--------+
1 row in set (0.00 sec)

mysql> update test_key set name='关羽' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 关羽   |
+----+--------+
2 rows in set (0.00 sec)

删除主键：

alter table 表名 drop primary key;

mysql> show create table test_key\G
*************************** 1. row ***************************
       Table: test_key
Create Table: CREATE TABLE `test_key` (
  `id` int(10) unsigned NOT NULL COMMENT '学号',
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test_key drop primary key;
Query OK, 2 rows affected (0.34 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   |     | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 关羽   |
+----+--------+
2 rows in set (0.00 sec)

mysql> insert into test_key values (2,'刘备');
Query OK, 1 row affected (0.01 sec)

mysql> select * from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 关羽   |
|  2 | 刘备   |
+----+--------+
3 rows in set (0.00 sec)

添加主键：

alter table 表名 add primary key(字段列表)

mysql> show create table test_key\G
*************************** 1. row ***************************
       Table: test_key
Create Table: CREATE TABLE `test_key` (
  `id` int(10) unsigned NOT NULL COMMENT '学号',
  `name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

-- 添加主键失败，因为此时表里边id有两个是一样的
mysql> alter table test_key add primary key(id);
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

mysql> select * from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 关羽   |
|  2 | 刘备   |
+----+--------+
3 rows in set (0.00 sec)

-- 删除一个数据
mysql> delete from test_key where name='刘备';
Query OK, 1 row affected (0.01 sec)

-- 添加主键
mysql> alter table test_key add primary key(id);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 添加成功
mysql> desc test_key;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | NO   | PRI | NULL    |       |
| name  | varchar(20)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> show create table test_key\G
*************************** 1. row ***************************
       Table: test_key
Create Table: CREATE TABLE `test_key` (
  `id` int(10) unsigned NOT NULL COMMENT '学号',
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from test_key;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张飞   |
|  2 | 关羽   |
+----+--------+
2 rows in set (0.00 sec)

复合主键：

在创建表的时候，在所有字段之后，使用primary key(主键字段列表)来创建主键，如果有多个字段作为主键，可以使用复合主键。

mysql> create table pick_course(
    -> id int unsigned,
    -> course_id int unsigned comment '课程编号',
    -> score tinyint unsigned comment '考试分数',
    -> primary key (id,course_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc pick_course;
+-----------+---------------------+------+-----+---------+-------+
| Field     | Type                | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| id        | int(10) unsigned    | NO   | PRI | NULL    |       |
| course_id | int(10) unsigned    | NO   | PRI | NULL    |       |
| score     | tinyint(3) unsigned | YES  |     | NULL    |       |
+-----------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into pick_course values (1234,40,90);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pick_course;
+------+-----------+-------+
| id   | course_id | score |
+------+-----------+-------+
| 1234 |        40 |    90 |
+------+-----------+-------+
1 row in set (0.00 sec)

mysql> insert into pick_course values (1235,40,85);
Query OK, 1 row affected (0.01 sec)

mysql> select * from pick_course;
+------+-----------+-------+
| id   | course_id | score |
+------+-----------+-------+
| 1234 |        40 |    90 |
| 1235 |        40 |    85 |
+------+-----------+-------+
2 rows in set (0.00 sec)

mysql> insert into pick_course values (1234,41,90);
Query OK, 1 row affected (0.01 sec)

-- 出现冲突
mysql> insert into pick_course values (1234,41,85);
ERROR 1062 (23000): Duplicate entry '1234-41' for key 'PRIMARY'