MySQL中约束类型总结

无情 阅读:231 2021-06-03 14:46:44 评论:0

来源:IT虾米网

常见约束


1.NOT NULL
2.UNIQUE
3.PRIMARY KEY
4.FOREIGN KEY
5.CHECK
6.AUTO_INCREMENT
7.ZEROFILL
8.DEFAULT
9.UNSIGNED


NOT NULL(非空约束)

添加非空约束 
1)建表时直接添加 
CREATE TABLE t_user(user_id INT(10) NOT NULL); 
2)通过ALTER语句 
ALTER TABLE t_user MODIFY user_id INT(10) NOT NULL; 
ALTER TABLE t_user CHANGE user_id user_id INT(10) NOT NULL; 
3)删除非空约束 
1)ALTER TABLE t_user MODIFY user_id INT(10); 
2)ALTER TABLE t_user CHANGE user_id user_id INT(10);

UNIQUE(唯一约束)

添加唯一约束 
1)建表时直接添加 
CREATE TABLE t_user(user_id INT(10) UNIQUE); 
CREATE TABLE t_user( 
user_id INT(10), 
user_name VARCHAR(30), 
CONSTRAINT UN_PHONE_EMAIL UNIQUE(user_id,user_name)#复合约束 
); 
CREATE TABLE t_user( 
user_id INT(10), 
UNIQUE KEY(user_id) 
); 
2)通过ALTER语句 
ALTER TABLE t_user MODIFY user_id INT(10) UNIQUE; 
ALTER TABLE t_user CHANGE user_id user_id INT(10) UNIQUE; 
ALTER TABLE t_user ADD UNIQUE(user_id); 
ALTER TABLE t_user ADD UNIQUE KEY(user_id); 
ALTER TABLE t_user ADD CONSTRAINT UN_ID UNIQUE(user_id); 
ALTER TABLE t_user ADD CONSTRAINT UN_ID UNIQUE KEY(user_id); 
3)删除唯一性约束 
ALTER TABLE t_user DROP INDEX user_id; 
注:唯一但是可以为空(空和空不相等)

PRIMARY KEY(主键约束)

主键设计: 
1.单字段主键,单列作为主键,建议使用,复合主键,使用多列充当主键,不建议 
2.主键分为两种: 
(1)自然主键:使用有业务含义的列作为主键(不推荐使用) 
(2)代理主键:使用没有业务含义的列作为主键(推荐使用) 
 
添加主键约束 
1)建表时直接添加 
CREATE TABLE t_user(user_id INT(10) PRIMARY KEY); 
CREATE TABLE t_user( 
user_id INT(10), 
user_name VARCHAR(30), 
CONSTRAINT PK_ID_NAME PRIMARY KEY(user_id,user_name)#复合约束 
); 
CREATE TABLE t_user( 
user_id INT(10), 
PRIMARY KEY(user_id) 
); 
2)通过ALTER语句 
ALTER TABLE t_user MODIFY user_id INT(10) PRIMARY KEY; 
ALTER TABLE t_user CHANGE user_id user_id INT(10) PRIMARY KEY; 
ALTER TABLE t_user ADD PRIMARY KEY(user_id); 
ALTER TABLE t_user ADD CONSTRAINT PK_ID PRIMARY KEY(user_id); 
删除主键约束 
3)ALTER TABLE t_user DROP PRIMARY KEY; 
注:主键约束相当于(唯一约束+非空约束) 
一张表中最多有一个主键约束,如果设置多个主键,就会出现如下提示: 
Multiple primary key defined!!! 
删除主键约束前,如果有自增长需要先删除自增长,如果不删除自增长就无法删除主键约束

FOREIGN KEY(外键约束,对应的字段只能是主键或者唯一约束修饰的字段)

首先创建两张表:class,students 
主表: 
CREATE TABLE class( 
cla_id INT(6) AUTO_INCREMENT PRIMARY KEY, 
cla_name VARCHAR(30) NOT NULL UNIQUE 
); 
从表: 
CREATE TABLE students( 
stu_id INT(10) AUTO_INCREMENT PRIMARY KEY, 
stu_name VARCHAR(30) NOT NULL, 
stu_score FLOAT(5,2) DEFAULT 0.0, 
cla_id INT(10), 
CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id)#添加外键约束 
); 
也可以这样添加: 
ALTER TABLE students ADD CONSTRAINT FK_CLA_ID FROEIGN KEY(cla_id) REFERENCES class(cla_id); 
删除外键约束 
ALTER TABLE students DROP FOREIGN KEY FK_CLA_ID;
#外键中的级联关系有以下几种情况: 
#ON DELETE CASCADE 删除主表中的数据时,从表中的数据随之删除 
#ON UPDATE CASCADE 更新主表中的数据时,从表中的数据随之更新 
#ON DELETE SET NULL 删除主表中的数据时,从表中的数据置为空 
#默认 删除主表中的数据前需先删除从表中的数据,否则主表数据不会被删除 
CREATE TABLE students( 
stu_id INT(10) AUTO_INCREMENT PRIMARY KEY, 
stu_name VARCHAR(30) NOT NULL, 
stu_score FLOAT(5,2) DEFAULT 0.0, 
cla_id INT(10), 
CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON DELETE CASCADE 
); 
CREATE TABLE students( 
stu_id INT(10) AUTO_INCREMENT PRIMARY KEY, 
stu_name VARCHAR(30) NOT NULL, 
stu_score FLOAT(5,2) DEFAULT 0.0, 
cla_id INT(10), 
CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON UPDATE CASCADE 
); 
CREATE TABLE students( 
stu_id INT(10) AUTO_INCREMENT PRIMARY KEY, 
stu_name VARCHAR(30) NOT NULL, 
stu_score FLOAT(5,2) DEFAULT 0.0, 
cla_id INT(10), 
CONSTRAINT FK_CLA_ID FOREIGN KEY(cla_id) REFERENCES class(cla_id) ON DELETE SET NULL 
); 
注:插入数据时,先插入主表中的数据,再插入从表中的数据。 
    删除数据时,先删除从表中的数据,再删除主表中的数据。

CHECK(检查约束)

CREATE TABLE class( 
cla_id INT(6) AUTO_INCREMENT PRIMARY KEY, 
cla_name VARCHAR(30) NOT NULL UNIQUE, 
CHECK(cla_id>0) 
); 
注:mysql不支持检查约束,但是写上检查约束不会报错 
 
其他: 
一.AUTO_INCREMENT(自增长) 
添加自增长 
1)在创建表的时候添加 
CREATE TABLE t_user(user_id INT(10) AUTO_INCREMENT PRIMARY KEY); 
2)通过ALTER语句 
ALTER TABLE t_user MODIFY user_id INT(10) AUTO_INCREMENT; 
ALTER TABLE t_user CHANGE user_id user_id INT(10) AUTO_INCREMENT; 
删除自增长 
ALTER TABLE t_user MODIFY user_id INT(10); 
ALTER TABLE t_user CHANGE user_id user_id INT(10); 
注:There can be only one auto column and it must be defined as a key. 
一张表只能有一个自增长列,并且该列必须定义了约束(可以是主键约束,也可以是唯一约束,也可以是外键约束,但是不可以是非空和检查约束) 
不过自增长一般配合主键使用,并且只能在数字类型中使用

ZEROFILL(零填充)

添加零填充 
1)在创建表的时候添加 
CREATE TABLE t_user(user_id INT(10) ZEROFILL); 
2)通过ALTER语句 
ALTER TABLE t_user MODIFY user_id INT(10) ZEROFILL; 
ALTER TABLE t_user CHANGE user_id user_id INT(10) ZEROFILL; 
删除零填充 
ALTER TABLE t_user MODIFY user_id INT(10); 
ALTER TABLE t_user CHANGE user_id user_id INT(10); 
注:零填充会将未将有效位以外的位用零来显示,比如某字段数据类型为INT(5),而插入的值为2,那么零填充会显示00002 
但是,这个效果在Navicat for MySQL中显示不出来,只有在DOS窗口下才能显示

DEFAULT(默认)

添加默认约束 
1)在创建表的时候添加 
CREATE TABLE t_user(user_id INT(10) DEFAULT  3); 
2)通过ALTER语句 
ALTER TABLE t_user MODIFY user_id INT(10) DEFAULT  2; 
ALTER TABLE t_user CHANGE user_id user_id INT(10) DEFAULT  2; 
删除默认约束 
ALTER TABLE t_user MODIFY user_id INT(10); 
ALTER TABLE t_user CHANGE user_id user_id INT(10);

UNSIGNED(无符号位)

添加无符号 
1)在创建表的时候添加 
CREATE TABLE t_user(user_id INT(10) UNSIGNED); 
2)通过ALTER语句 
ALTER TABLE t_user MODIFY user_id INT(10) UNSIGNED; 
ALTER TABLE t_user CHANGE user_id user_id INT(10) UNSIGNED; 
删除无符号 
ALTER TABLE t_user MODIFY user_id INT(10); 
ALTER TABLE t_user CHANGE user_id user_id INT(10); 
注:无符号作用于数值类型 
 
#从查询information_schema中查询指定表中的约束 
USE INFORMATION_SCHEMA; 
SELECT CONSTRAINT_NAME FROM TABLE_CONSTRAINTS WHERE TABLE_NAME='student';

标签:mysql
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

发表评论
搜索
排行榜
KIKK导航

KIKK导航

关注我们