MySQL数据库操作（4）表约束

偏执的太偏执、 2023-08-17 15:29 108阅读 0赞

在创建表或者插入列时，可以对列的值进行约束，当插入条目时如果不符合约束，将会报错，拒绝插入。

## 1、非空约束 ##

`NOT NULL` 字段值不能为空  
例：

#创建表时添加非空约束
    CREATE TABLE tb1( id INT, name VARCHAR(20) NOT NULL );
    INSERT INTO tb1(id) VALUE(1); # 报错，没有传入name
    #注意：在mysql 里面，'' 不等于null
    #修改表添加非空约束
    ALTER TABLE tb1 MODIFY id INT NOT NULL;
    #取消非空约束
    ALTER TABLE tb1 MODIFY id INT;

## 2、唯一约束 ##

`UNIQUE KEY` 确保字段中的值的唯一  
例：

#创建表时添加唯一约束
    CREATE TABLE tb2(id INT NOT NULL UNIQUE KEY, name VARCHAR(20) NOT NULL);
    INSERT INTO tb2 VALUE(1,'张三');
    INSERT INTO tb2 VALUE(1,'李四'); # 报错，id字段的值有重复
    #修改表添加唯一约束
    ALTER TABLE `tb2` ADD UNIQUE KEY(`name`);
    #删除唯一约束
    ALTER TABLE tb2 DROP KEY name;
    #联合唯一约束
    ALTER TABLE tb2 ADD aa INT, add bb INT;
    ALTER TABLE tb2 ADD UNIQUE KEY (aa,bb);
    INSERT INTO tb2 VALUE(4,'佳能',1,2);
    INSERT INTO tb2 VALUE(5,'哈哈',1,2); # 报错，(aa,bb)联合字段有重复
    #删除联合唯一
    SHOW CREATE TABLE tb2; #查看约束名
    ALTER TABLE tb2 DROP KEY aa; #通过约束名删除约束，联合约束默认名为联合列中的第一列

## 3、主键约束 ##

`PRIMARY KEY` == `NOT NULL + UNIQUE KEY`  
主键保证记录的唯一性， 唯一标识每一条数据  
主键自动为`NOT NULL`  
每张数据表只能存在一个主键  
当一张表里没有一个主键的时候，第一个出现的非空且为唯一的列被视为有主键。  
例：

#创建表时添加主键约束
    CREATE TABLE tb3( id INT PRIMARY KEY,name VARCHAR(20) NOT NULL);
    #删除主键约束
    ALTER TABLE tb3 DROP PRIMARY KEY;
    #添加主键约束
    ALTER TABLE tb3 ADD PRIMARY KEY(id);
    #联合主键
    CREATE TABLE tb4(id_a INT , id_b INT, content VARCHAR(20), PRIMARY KEY(id_a,id_b) );
    #添加联合主键
    ALTER TABLE tb4 ADD PRIMARY KEY(id_a,id_b);

## 4、自增长 ##

`AUTO_INCREMENT`自动编号，一般与主键组合使用。一个表里面只有一个自增  
默认情况下，起始值为1，每次的增量为1。  
当插入记录时，如果为`AUTO_INCREMENT`数据列明确指定了一个数值，则会出现两种情况，  
情况一，如果插入的值与已有的编号重复，则会出现出错信息，因为AUTO\_INCREMENT数据列的值必须是唯一的；  
情况二，如果插入的值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。如果自增序列的最大值被删除了，则在插入新记录时，该值被重用。（可以调大，不可以缩小）  
例：

#创建表时添加自增长
    CREATE TABLE tb5( id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20))AUTO_INCREMENT =100;
     # 如果不写，默认从1开始
    #删除自动增长
    ALTER TABLE tb5 MODIFY id INT;
    #修改表添加自动增长
    ALTER TABLE tb5 MODIFY id INT AUTO_INCREMENT;

## 5、默认约束 ##

`DEFAULT`初始值设置，插入记录时，如果没有明确为字段赋值，则自动赋予默认值。  
例：

#创建表时添加默认约束
    CREATE TABLE tb6(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,age INT NOT NULL DEFAULT 18);
    #删除默认约束
    ALTER TABLE tb6 MODIFY age INT NOT NULL;
    ALTER TABLE tb6 ALTER age DROP DEFAULT;
    #添加默认约束
    ALTER TABLE tb6 MODIFY age INT DEFAULT 20;
    ALTER TABLE tb6 ALTER age SET DEFAULT 21;

6、外键约束  
外键约束`FOREIGN KEY`，保持数据一致性，完整性实现一对一或一对多关系。  
外键约束的要求：  
数据表的存储引擎只能为InnoDB  
外键列和参照列数据类型一致  
外键必须关联到键上面去，一般情况是关联到，另一张表的主键  
例：

#创建表时添加外键约束
    CREATE TABLE `a`( a_id INT PRIMARY KEY,a_name VARCHAR(20) NOT NULL);
    CREATE TABLE `b`(b_id INT PRIMARY KEY,b_name VARCHAR(20) NOT NULL,fy_id INT NOT NULL,CONSTRAINT AB_id FOREIGN KEY(fy_id) REFERENCES `a`(a_id)); #AB_id是外键的别名，如果没有别名则无法删除该外键，fy_id是本表中要关联的列，`a`(a_id)是关联到a表的a_id列，fy_id的取值必须在a表中的a_id列中出现过才能用。
    #删除外键
    ALTER TABLE `b` DROP FOREIGN KEY AB_id;
    #增加外键
    ALTER TABLE `b` add CONSTRAINT AB_id FOREIGN KEY(fy_id) REFERENCES `a`(a_id);