6．1．3

删除unique约束

alter table persons drop index uc＿PersonID；Query OK， 3 rows affected （0．02 sec）Records： 3  Duplicates： 0  Warnings： 0desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ NO   ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．2 not null约束

6．2．1

not null 约束强制列不接受null值。

再拿6．1．3的表persons做说明，可以看到id和lastname字段是存在not null约束的，所以在insert时必须要传入这两个字段的值

insert into persons（id，lastname） values （3，＇q＇）；Query OK， 1 row affected （0．00 sec）

插入成功，下面试一下insert时不给id赋值的情况：

insert into persons（lastname） values （＇q＇）；ERROR 1364 （HY000）： Field ＇id＇ doesn＇t have a default value

成功的报错了，除非你有给id定一个default（默认值），否则在insert时不给id赋值一定会报错。

select ＊ from persons；＋－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋｜ id ｜ lastname ｜ firstName ｜ city ｜＋－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋｜  1 ｜ z        ｜ q         ｜ NULL ｜｜  2 ｜ z        ｜ q         ｜ NULL ｜｜  2 ｜ zz       ｜ q         ｜ NULL ｜｜  3 ｜ q        ｜ NULL      ｜ NULL ｜＋－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋

总结：有not null约束的字段一定要赋值，没有not null约束的比如city字段在insert时不赋值会自动为null

6．2．2

删除not null 约束

alter table persons modify id int null；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．3 check 约束

6．3．1

ALTER TABLE Persons ADD CHECK （id＞0）；insert into persons（id，lastname） values （－2，＇qq＇）；Query OK， 1 row affected （0．00 sec）

非常奇怪，check约束明明加进去了，但是id＝－2还可以被插入；

select ＊ from persons；＋－－－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋｜ id   ｜ lastname ｜ firstName ｜ city ｜＋－－－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋｜    1 ｜ z        ｜ q         ｜ NULL ｜｜    2 ｜ z        ｜ q         ｜ NULL ｜｜    2 ｜ zz       ｜ q         ｜ NULL ｜｜    3 ｜ q        ｜ NULL      ｜ NULL ｜｜   －2 ｜ qq       ｜ NULL      ｜ NULL ｜＋－－－－－－＋－－－－－－－－－－＋－－－－－－－－－－－＋－－－－－－＋

刚刚insert的数据已经进入表中了。。。

6．3．2

百度以后，发现这个mysql本身的问题，想要达到约束效果可以使用type＝enum（）；具体方法如下：

在原表基础上加入gender字段，规定gender只能为male或者female；

alter table persons add gender enum（＇male＇ ，＇female＇）；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type                  ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）               ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255）          ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255）          ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255）          ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ enum（＇male＇，＇female＇） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

表persons的gender成功变为type为enum（＇male＇ ，＇female＇）；

insert into persons1（id，lastname，gender） values （3，＇qqq＇，＇male1＇）；ERROR 1265 （01000）： Data truncated for column ＇gender＇ at row 1insert into persons1（id，lastname，gender） values （3，＇qqq＇，＇male＇）；Query OK， 1 row affected （0．00 sec）

6．3．3

删除enum（＇male＇ ，＇female＇）约束

alter table persons modify gender varchar（255）；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．4 default 约束

6．4．1

创建default 约束

alter table persons alter city set default ＇上海＇；Query OK， 0 rows affected （0．01 sec）Records： 0  Duplicates： 0  Warnings： 0desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ 上海    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．4．2

删除default 约束

alter table persons alter city drop  default；Query OK， 0 rows affected （0．01 sec）Records： 0  Duplicates： 0  Warnings： 0desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ YES  ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．5 primary key

primary key约束：

用来唯一标识数据库表中的每条记录；主键必须包含唯一的值且不能含有null值。与6．1unique约束不同的是，unique约束能同时对好几个字段增加唯一约束，而primary约束只能对某一列创建唯一约束。在一个表中只能有一个primary约束。

6．5．1创建primary 约束

alter table persons add primary key（id）；ERROR 1062 （23000）： Duplicate entry ＇2＇ for key ＇PRIMARY＇

原因：给表persons的id字段增加primary约束，这需要id字段的内容保持唯一性，而现在id＝2有好几条数据，所以报错。

先将表中id＝2的字段删除，再给id增加primary约束

delete from persons where id＝2；alter table persons add primary key（id）；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ NO   ｜ PRI ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．5．2 删除primary 约束

alter table persons drop primary key；desc persons；＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ Field     ｜ Type         ｜ Null ｜ Key ｜ Default ｜ Extra ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋｜ id        ｜ int（11）      ｜ NO   ｜     ｜ NULL    ｜       ｜｜ lastname  ｜ varchar（255） ｜ NO   ｜     ｜ NULL    ｜       ｜｜ firstName ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ city      ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜｜ gender    ｜ varchar（255） ｜ YES  ｜     ｜ NULL    ｜       ｜＋－－－－－－－－－－－＋－－－－－－－－－－－－－－＋－－－－－－＋－－－－－＋－－－－－－－－－＋－－－－－－－＋

6．6 foreign key

6．6．1

ALTER TABLE a ADD FOREIGN KEY （P＿Id） REFERENCES b（P＿Id）

给表a增加外键P＿Id；且此外键的参照为表b的P＿Id列；关联的操作为删除和更新；

注意：表b为为主表，表a为从表，表b的更新和删除时将会联动表a中外键与其关联对应的记录做更新或删除操作

6．6．2

在做这个操作时，表newstudent和表test都是有数据的，所以就一直报错

alter table newstudent add foreign key （id） references testdatabase．test（id）；ERROR 3734 （HY000）： Failed to add the foreign key constraint． Missing column ＇userid＇ for constraint ＇newstudent＿ibfk＿1＇ in the referenced table ＇test＇

通过这两步，将表中的数据都删除；

delete from newstudent；Query OK， 7 rows affected （0．01 sec）delete from test；Query OK， 7 rows affected （0．01 sec）

6．6．3

alter table newstudent add foreign key （id） references testdatabase．test（id）；Query OK， 0 rows affected （0．05 sec）Records： 0  Duplicates： 0  Warnings： 0

此时，给表newstudent增加外键id；且此外键的参照为表test的id项；这时表test为主表；表newstudent为从表

重点：主表和从表一定要分清楚，因为主表的更新和删除时将会联动从表中外键与其关联对应的记录做更新或删除操作

6．6．4

之前没有注意，在两个表都为空表时，先给从表newstudent赋值插入，结果就是报错

insert into newstudent（userid，id，name，gender，score）  values（100，2，＇张三我＇，＇男＇，85）；ERROR 1452 （23000）： Cannot add or update a child row： a foreign key constraint fails （｀testdatabase｀．｀newstudent｀， CONSTRAINT ｀newstudent＿ibfk＿1｀ FOREIGN KEY （｀id｀） REFERENCES ｀test｀ （｀id｀））

先给test赋值，id＝2；再给newstudent赋值id＝2的数据，就可以成功插入

insert into test（id，name，score，course） values（2，＇王五的＇，90，＇语文＇）；Query OK， 1 row affected （0．00 sec）insert into newstudent（userid，id，name，gender，score）  values（100，2，＇张三我＇，＇男＇，85）；Query OK， 1 row affected （0．00 sec）

6．6．5

如果给newstudent赋值id＝3会报错，因为这个时候test表没有id＝3的数据

insert into newstudent（userid，id，name，gender，score）  values（100，3，＇张三我＇，＇男＇，85）；ERROR 1452 （23000）： Cannot add or update a child row： a foreign key constraint fails （｀testdatabase｀．｀newstudent｀， CONSTRAINT ｀newstudent＿ibfk＿1｀ FOREIGN KEY （｀id｀） REFERENCES ｀test｀ （｀id｀））

那在重新给newstudent赋值id＝1的数据呢？结果也是报错，因为id有primary约束，不能重复插入

insert into newstudent（userid，id，name，gender，score）  values（100，1，＇张三我＇，＇男＇，85）；ERROR 1062 （23000）： Duplicate entry ＇1＇ for key ＇PRIMARY＇

6．6．6

外键起到的作用就是保持连接的数据表的一致性，当对主表进行删除或更新操作时，从表也会一起删除或更新；

update test set id＝3；ERROR 1451 （23000）： Cannot delete or update a parent row： a foreign key constraint fails （｀testdatabase｀．｀newstudent｀， CONSTRAINT ｀newstudent＿ibfk＿1｀ FOREIGN KEY （｀id｀） REFERENCES ｀test｀ （｀id｀））

不能删除或更新这一行，存在外键约束

我们在更新与删除时遇到的外键约束解决方案分别对应设置Update rule与Delete rule。有如下四个选项：

1．CASCADE：从父表删除或更新且自动删除或更新子表中匹配的行。

2．SET NULL：从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL。

3．RESTRICT：拒绝对父表的删除或更新操作。

4．NO ACTION：标准SQL的关键字，在MySQL中与RESTRICT相同。

6．6．7

所以将foreign约束删除后重新构建：

alter table newstudent drop foreign key newstudent＿ibfk＿1；Query OK， 0 rows affected （0．04 sec）Records： 0  Duplicates： 0  Warnings： 0

要点：newstudent＿ibfk＿1是在之前的报错中找到的CONSTRAINT ｀newstudent＿ibfk＿1｀

delete from test；Query OK， 0 rows affected （0．00 sec）delete from newstudent；Query OK， 2 rows affected （0．00 sec）

将表中的内容删除干净

6．6．8

alter table newstudent add foreign key （id） references testdatabase．test（id） on update cascade on delete cascade；Query OK， 0 rows affected （0．04 sec）Records： 0  Duplicates： 0  Warnings： 0

on update cascade表示更新关联；on delete cascade表示删除关联。

6．6．9

将表test的id＝2更新为id＝3，看看表newstudent和表test有什么变化

update test set id＝3 where id＝2；Query OK， 1 row affected （0．00 sec）Rows matched： 1  Changed： 1  Warnings： 0
select ＊ from test；＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜ id ｜ name      ｜ course ｜ score ｜＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜  1 ｜ 王五      ｜ 语文   ｜    90 ｜｜  3 ｜ 王五的    ｜ 语文   ｜    90 ｜＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋

test表的id从2变成了3；

select ＊ from newstudent；＋－－－－－－－－＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜ userid ｜ id ｜ name      ｜ gender ｜ score ｜＋－－－－－－－－＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜    100 ｜  1 ｜ 张三      ｜ 男     ｜ 65    ｜｜    100 ｜  3 ｜ 张三我    ｜ 男     ｜ 85    ｜＋－－－－－－－－＋－－－－＋－－－－－－－－－－－＋－－－－－－－－＋－－－－－－－＋

此时newstudent表的id也从2变成了3；（此时没有直接对newstudent表有过任何操作）

6．6．10

delete from test where id＝3；Query OK， 1 row affected （0．00 sec）

将主表test中id＝3的删除

select ＊ from test；＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜ id ｜ name   ｜ course ｜ score ｜＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜  1 ｜ 王五   ｜ 语文   ｜    90 ｜＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋
select ＊ from newstudent；＋－－－－－－－－＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜ userid ｜ id ｜ name   ｜ gender ｜ score ｜＋－－－－－－－－＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋｜    100 ｜  1 ｜ 张三   ｜ 男     ｜ 65    ｜＋－－－－－－－－＋－－－－＋－－－－－－－－＋－－－－－－－－＋－－－－－－－＋

这时表newstudent的id＝3的数据也删除了