mysql> insert into t8 values();
mysql> select * from t8;
+------+------+
| id | name |
+------+------+
| NULL | NULL |
| NULL | NULL |
+------+------+
2 rows in set (0.00 sec)
mysql> insert into t8 values(1,‘tom‘);
mysql> insert into t8 values(1,‘mary‘);
ERROR 1062 (23000): Duplicate entry ‘1‘ for key ‘id‘
mysql> alter table t8 modify name char(10) unique;
mysql> insert into t8 values(2,‘mary‘);
mysql> insert into t8 values(3,‘tom‘);
ERROR 1062 (23000): Duplicate entry ‘tom‘ for key ‘name‘
4、自增 auto_increment
要求：
1）该字段必须是数值型
2）字段上要有唯一性索引或者主键
mysql> create table t9 (id int primary key auto_increment);
mysql> desc t9;
+-------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
+-------+---------+------+-----+---------+----------------+
1 row in set (0.00 sec)
mysql> insert into t9 values(); //插入1
mysql> insert into t9 values(3); //插入3
几点说明：
1）当自增字段发生断档时，值会从最大值继续自增
2）当delete删除最大值时，下一个值仍然从删除之前的最大值继续自增
3）当truncate表时，值从1开始重新计算
5、主键 primary key
主键是表中的特殊字段，这个字段能够唯一的标识表中的每一条记录。
一张表最多只能有一个主键。
主键的用途：快速定位数据
主键需要满足的条件：非空且唯一
primary key == not null + unique
1）使用单个字段做主键
a、在字段后直接指定主键约束(列级约束，默认值为NULL)
mysql> create table t10 (id int primary key,age int,name char(10));
mysql> desc t10;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| age | int(11) | YES | | NULL | |
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> insert into t10(id) values(1);
mysql> insert into t10(id) values(1);
ERROR 1062 (23000): Duplicate entry ‘1‘ for key ‘PRIMARY‘
mysql> insert into t10 values();
mysql> insert into t10 values();
ERROR 1062 (23000): Duplicate entry ‘0‘ for key ‘PRIMARY‘
b、整张表的所有字段都定义完成之后再来指定主键(表级约束，默认值是0)
mysql> create table t11 (id int,name char(5),primary key(id));
mysql> desc t11;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | char(5) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t11 values();
mysql> insert into t11 values();
ERROR 1062 (23000): Duplicate entry ‘0‘ for key ‘PRIMARY‘
mysql> insert into t11 values(1,‘hi‘);
mysql> select * from t11;
+----+------+
| id | name |
+----+------+
| 0 | NULL |
| 1 | hi |
+----+------+
2 rows in set (0.00 sec)
2）多个字段联合做主键
mysql> desc mysql.user \G //user和host字段联合做主键
*************************** 1. row ***************************
Field: Host
Type: char(60)
Null: NO
Key: PRI
Default:
Extra:
*************************** 2. row ***************************
Field: User
Type: char(16)
Null: NO
Key: PRI
Default:
Extra:
*************************** 3. row ***************************
Field: Password
Type: char(41)
Null: NO
Key:
Default:
Extra:
注意：联合主键只能在所有字段都定义完成之后，才能定义主键。
mysql> create table t12 (id int,name char(2),age int,primary key(id,name));
mysql> desc t12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | char(2) | NO | PRI | | |
| age | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
mysql> insert into t12(id) values(1);
mysql> select * from t12;
+----+------+------+
| id | name | age |
+----+------+------+
| 0 | | NULL |
| 1 | | NULL |
+----+------+------+
2 rows in set (0.00 sec)
mysql> insert into t12(name) values (‘a‘);
mysql> select * from t12;
+----+------+------+
| id | name | age |
+----+------+------+
| 0 | | NULL |
| 0 | a | NULL |
| 1 | | NULL |
+----+------+------+
3 rows in set (0.00 sec)
6、外键 foreign key
外键：一个表的数据依赖于另一张表的主键列的数据，如果在主键列没有出现的值，是不能够出现在外键字段的。
主键和外键就像粘合剂，能够将多个表联系起来。
创建外键的条件：
1）存储引擎是innodb
2）相关联字段数据类型要一致
3）最好在外键列上建索引(目的就是为了减小扫描范围，不创建也可以，只是影响性能)
例子：
dept：部门表
emp ：员工表
mysql> create table dept (dno int,dname char(10),primary key (dno));
mysql> create table emp (eno int,e_dno int,ename char(15),index(e_dno),foreign key (e_dno) references dept(dno));
向父表中插入数据
mysql> insert into dept values(1,‘sa‘),(2,‘dba‘),(3,‘manager‘);
向子表中插入数据
mysql> select * from dept;
+-----+---------+
| dno | dname |
+-----+---------+
| 1 | sa |
| 2 | dba |
| 3 | manager |
+-----+---------+
3 rows in set (0.00 sec)
mysql> insert into emp values(100,3,‘Tom‘);
mysql> select * from emp;
+------+-------+-------+
| eno | e_dno | ename |
+------+-------+-------+
| 100 | 3 | Tom |
+------+-------+-------+
1 row in set (0.00 sec)
mysql> insert into emp values(101,4,‘Mary‘); //反例：插入父表中不存在的部门号
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`up1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`e_dno`) REFERENCES `dept` (`dno`))
mysql> delete from dept where dno=2;
mysql> delete from dept where dno=3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`up1`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`e_dno`) REFERENCES `dept` (`dno`))
小结：
1）子表中的数据依赖于父表，不能向子表中插入父表中不存在值
2）不能删除父表中被子表所依赖的记录
删除父表中被依赖的行的方法：
1）删除外键约束
2）指定级联操作的选项
on delete cascade：级联删除
on update cascade：级联更新
mysql> drop table emp;
mysql> create table emp (eno int,e_dno int,ename char(15),index(e_dno),foreign key (e_dno) references dept(dno) on delete cascade on update cascade); //完整的外键创建
mysql> insert into emp values(100,1,‘Tom‘),(101,3,‘Mary‘),(103,1,‘Jack‘);
mysql> select * from dept;
+-----+---------+
| dno | dname |
+-----+---------+
| 1 | sa |
| 3 | manager |
+-----+---------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+------+-------+-------+
| eno | e_dno | ename |
+------+-------+-------+
| 100 | 1 | Tom |
| 101 | 3 | Mary |
| 103 | 1 | Jack |
+------+-------+-------+
3 rows in set (0.00 sec)
mysql> delete from dept where dno=1;
Query OK, 1 row affected (0.02 sec)
mysql> select * from emp;
+------+-------+-------+
| eno | e_dno | ename |
+------+-------+-------+
| 101 | 3 | Mary |
+------+-------+-------+
1 row in set (0.00 sec)
mysql> update dept set dno=100 where dno=3;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp;
+------+-------+-------+
| eno | e_dno | ename |
+------+-------+-------+
| 101 | 100 | Mary |
+------+-------+-------+
1 row in set (0.00 sec)
有了级联删除和级联修改选项，父表中的数据发生删除或者更新时，子表中相关数据也会发生相应的变化。
删除外键
alter table 表名 drop foreign key 外键的名字
mysql> show create table emp \G //红色字体为外键的名字
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`eno` int(11) DEFAULT NULL,
`e_dno` int(11) DEFAULT NULL,
`ename` char(15) DEFAULT NULL,
KEY `e_dno` (`e_dno`),
CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`e_dno`) REFERENCES `dept` (`dno`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
mysql> alter table emp drop foreign key emp_ibfk_1;
mysql> show create table emp \G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`eno` int(11) DEFAULT NULL,
`e_dno` int(11) DEFAULT NULL,
`ename` char(15) DEFAULT NULL,
KEY `e_dno` (`e_dno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
MySQL约束
标签：mysql