MySQL/mariadb知识点——约束管理、键管理语句

关系型数据库中数据约束，可以理解为想数据表中插入数据时需遵守的限制规则；MySQL中常见的约束有主键约束、唯一键约束、外键约束、非空约束等；

主键：primary key，表上一个或多个字段的组合，填入主键字段中的数据；必须不同于已经存在的其他行中相同字段上的数据，且不为空；一个表只能存在唯一主键，一个主键可由多个字段组成；

唯一键：unique key，表上一个或多个字段的组合，填入其中字段中的数据，必须不同于已经存在其他行上相同字段的数据；可以为空，可有多个唯一键；

外键：foreign key，一个表中外键字段所能够插入的取值范围，取决于引用另一个表上主键字段上已存在的数据集合；

检查条件约束：check，自定义的逻辑表达式；

非空约束

添加非空约束

设置非空字段，在testtb表中的name字段添加非空约束；

MariaDB [testdb]> alter table testtb modify name varchar(100) not null;

删除非空约束

MariaDB [testdb]> alter table testtb modify name varchar(100) null;

自动增长

添加自动增长

在testtb表中的id字段设置自动增长；

MariaDB [testdb]> alter table testtb modify id int auto_increment;

也可用如下使用

MariaDB [testdb]> alter table testtb change id id int auto_increment;

删除自动增长

MariaDB [testdb]> alter table testtb change id id int;
MariaDB [testdb]> alter table testtb modify id int;

主键约束

添加主键约束

使用如下两条语句中的任意一条均可；

MariaDB [testdb]> alter table testtb4 add primary key(id);
MariaDB [testdb]> alter table testtb4 add constraint primary key(id);

删除主键约束

删除主键时，不能直接删除，需要先删除自动增长，再删除主键；同理，如果当前表主键已经在其他表中字段当做外键；应先删除其他表中主外键关系，才可删除当前主键；

MariaDB [testdb]> alter table testtb drop primary key;

唯一键约束

添加唯一键约束；

默认情况下唯一键的名称为字段名称

MariaDB [testdb]> alter table testtb add unique key(uid);

创建唯一键，并制定唯一键名称为uni_test

MariaDB [testdb]> alter table testtb add unique key uni_test(test);

删除唯一约束键

MariaDB [testdb]> alter table testtb drop index uni_test;

查看约束

查看主键、查看唯一键、查看外键

MariaDB [testdb]> select * from information_schema.key_column_usage where table_name='test1';

查看主外键对应关系

查看test1表中已用那些表中的主键作为自己的外键；也可理解为查看test1的外键

MariaDB [testdb]> select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where TABLE_NAME = 'test1' and REFERENCED_TABLE_NAME is not null;

查看test2表的主键被那些表已用为外键

MariaDB [testdb]> select REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME,CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME = 'test2';

外键约束

 添加外键约束

 使用下列两条语句设置外键，在testtb表中创建一个新字段tid，并且添加外键testtb_tid_fk,外键，testtb表中的tid字段引用了表testtb2表中的id字段；

MariaDB [testdb]> alter table testtb add column tid int default 0 not null;
MariaDB [testdb]> alter table testtb add constraint testtb_tid_fk foreign key(tid) references testtb2(id);

删除外键约束

查找test4表中的外键

MariaDB [testdb]> select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from information_schema.KEY_COLUMN_USAGE where TABLE_NAME = 'test4' and REFERENCED_TABLE_NAME is not null;

删除表test4中的test_tid_fk外键

MariaDB [testdb]> alter table test4 drop foreign key test_tid_fk;