(四)约束及修改数据表


1、约束概述

约束的目的是为了保证数据的完整性与一致性。

按照约束的范围划分:
列级约束:只对一个数据列建立约束。既可以在列定义时声明,也可以在列定义后声明;
表级约束:对多个数据列建立约束。只能在列定义后声明;

按照约束的功能划分:
非空约束——NOT NULL
主键约束——PRIMARY KEY
唯一约束——UNIQUE KEY
默认约束——DEFAULT
外键约束——FOREIGN KEY

在实际开发中,表级约束很少用到,更多的会使用列级约束;除此之外,在所有的约束中,并不一定每种功能的约束都存在着表级或列级之分。其中,非空约束和默认约束就不存在表级约束,它们只有列级约束;而对于主键约束、唯一约束、外键约束都可以作为表级约束或列级约束。


2、外键约束的要求解析

前四种约束的用法及效果可参见操作数据表,现在来看一下外键约束:外键约束即“FOREIGN KEYp”,可以保证数据的完整性与一致性,实现数据表一对一或一对多的关系,这也是许多数据库被称为“关系型数据库”的根本原因。

创建外键约束需要满足如下要求:

  1. 父表(包含参照列)和子表(包含外键列)必须使用相同的存储引擎,且存储引擎只能为InnoDB,而且禁止使用临时表;
  2. 外键列和参照列必须具有相似的数据类型,其中:数字的长度或是否有符号位必须相同,而字符的则长度可以不同;
  3. 外键列和参照列必须创建索引,如果外键列不存在索引,MySQL将自动创建索引。

创建父表“provinces”,并检查其存储引擎:


创建子表“users”,此表的目的是记录用户的省份信息,在使用外键约束后,只需要父表的“id”就可以获取相应的省份,但是根据要求对于数字的长度必须相同,因此在使用数据类型“BIGINT”后,系统提示错误:


  此时已将数据类型修改为相同的“SMALLINT”,但系统仍然提示错误,原因是父表中注明“UNSIGNED”,即无符号位,而子表中并没有注明,因此出错:



  最终完成子表的创建:

输入“SHOW INDEXES FROM tbl_name”可查看该表的索引情况:


  由于“id”字段使用了主键,而主键会自动创建索引,因此参照列已经具有了索引,再来看外键列:

  可见具有两个索引,一个是“id”字段,另一个则是外键列“p_id”字段,查看该数据表的结构可以发现:

  系统已自动为其创建了索引。

注意:
父表——子表所参照的表“provinces”
子表——含有外键“FOREIGN KEY”的表“users”
参照列——父表中“id”字段列
外键列——子表中“p_id”字段列


3、外键约束的参照操作

外键约束的参照操作,简单理解就是在创建外键约束之后,更新父表时,子表是否进行同样的操作,共有如下4种:

  1. CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行;
  1. SET NULL:从父表删除或更新行,并设置子表中的外键列为“NULL”。如果使用该选项,必须保证子表列没有指定“NOT NLUU”;
  2. RESTRICT:拒绝对父表的删除或更新操作;
  3. NO ACTION:标准SQL的关键字,在MySQL中与“RESTRICT”作用相同。

以“CASCADE”为例进行演示,创建子表“users1”:


在创建外键时添加“ON DELETE CASCADE”
在创建外键时添加“ON DELETE CASCADE”

  之后,需要在两张数据表中添加记录,要注意,必须先在父表中添加记录,子表才能有所参照:

在父表中添加三个省份信息,之后向子表中添加用户信息,只需要写入用户名及父表中省份的“id”即可:


  之所以在添加“John”用户时出错,是因为父表中根本不存在“id”为9的省份,因而报错。查看该数据表的结构可发现:

“id”排列没有序号3是因为,虽然之前输入记录时“‘John’,9”报错没有计入,但是编号已经自动递增一位,因此最终的用户“id”为1、2、4、5。此时删除父表中“id”为3的省份,看子表是否也会发生改变:

可见父表中“id”为3的省份已被删除,而子表中“p_id”为3的用户“Rose”也一同被删除了。

注意:
  关于外键约束,在实际的开发过程中,其实很少使用物理性的外键约束,更多的会去使用逻辑性的外键约束,因为物理性的外键约束只有InnoDB这种引擎才会支持,然而另一种MyISAM引擎则不支持。换句话说,如果创建一张数据表,其存储引擎为MyISAM,但又想使用这种物理性的外键约束的话,其实是不可能实现的,因此,在实际的项目开发中,不会去定义物理性的外键。所谓的逻辑性外键指的就是在创建数据表之前,已经定义好多张数据表的结构,而不去使用“FOREIGN KEY”这个关键词。


4、修改数据表——添加/删除列

添加单列
ALTER TABLE tbl_name ADD [COLUMN] col_name columan_definition [FIRST | AFTER col_name]
** “FIRST col_name”指所插入的列位于指定列之前;“AFTER col_name”指所插入的列位于指定列之后;如果都省略,则默认在所有列之后插入该列。**

首先查看“users1”的数据结构,之后插入数据列“age”,且不指定其位置,再次查看该表结构可发现,新添加的列“age”确实位于所有列之后,排在末尾:


添加一列“password”,指定其位置在“username”列之后:


再添加一列“truename”,指定其位置在所有列之前:


添加多列
ALTER TABLE tbl_name ADD [COLUMN] (col_name columan_definition , ……)
添加多列时,列名与定义信息要放在小括号内,且不能指定位置,只能放在已有列的末尾

一次性向数据表“users1”中添加多列:

删除列
ALTER TABLE tbl_name DROP [COLUMN] col_name , ……
可以删除一列,可以删除多列,还可以在删除的同时新增列

删除“username”列:


删除“age”列同时,新增“username”列:


一次性删除多列:



5、修改数据表——添加/删除约束

添加主键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,……)
其中“CONSTRAINT”关键字可以自定义主键名,而“index_type”索引类型后面会讲到。

创建新的数据表“users2”,且不设置主键:


新增一列“id”,且为其添加主键,主键名为“PK_users2_id”:


添加唯一约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,……)

为“username”添加唯一约束:


添加外键约束
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,……) reference_definition

为“p_id”添加外键约束:

添加默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DEFAULT literal

为新建字段“age”添加默认约束:


删除主键约束
ALTER TABLE tbl_name DROP PRIMARY KEY

删除唯一约束
ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
**由于一张数据表可以有多个唯一约束,因此删除时需要添加索引名 **

首先查看“username”字段上唯一约束的索引名:


“Key_name”即索引名
“Key_name”即索引名

删除的“username”是索引名即唯一约束,而不是字段:


删除外键约束
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
**注意删除时需要添加外键约束名 **

首先查看“p_id”字段上的外键约束名:


系统自动添加的外键约束名“users2_ibfk_1”
系统自动添加的外键约束名“users2_ibfk_1”

删除“p_id”字段上的外键约束:



  可见已经没有“FOREIGN KEY”等信息:


删除默认约束
ALTER TABLE tbl_name ALTER [COLUMN] col_name DROP DEFAULT

删除字段“age”的默认约束:



6、修改数据表——修改列定义/更名操作

修改列定义
ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST|AFTER col_name]

修改字段“id”的数据类型为“TINYINT”并将其挪至数据表起始位置:


注意:
  由范围较大的数据类型(例如“SMALLINT”)修改成范围较小的数据类型(例如“TINYINT”),可能会造成数据的丢失。


修改列名称
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name col_definition [FIRST|AFTER col_name]
使用“CHANGE”关键字不仅包含“MODIFY”关键字的功能,还可以修改字段名称。

修改字段“p_id”的名称为“pid”,数据类型为“TINYINT”并将其挪至“age”字段的后面:


修改数据表名称
方法一:
ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
方法二:
RENAME TABLE tbl_name TO new_tbl_name [, tbl_name TO new_tbl_name]……

使用方法一将数据表“users2”名称修改为“users3”:


使用方法二将数据表“users3”名称修改为“users2”:


注意:
  实际上应尽量少的使用数据列及数据表的更名操作,因为当创建了索引或使用过视图、存储过程之后,在表名或列名被引用的情况下,修改其名称可能会导致视图或存储过程等无法正常工作。


7、修改数据表的SQL语句汇总:

  • 添加/删除列
    添加单列:ALTER TABLE tbl_name ADD [COLUMN] col_name columan_definition [FIRST | AFTER col_name];
    添加多列:ALTER TABLE tbl_name ADD [COLUMN] (col_name columan_definition , ……);
    删除列:ALTER TABLE tbl_name DROP [COLUMN] col_name , ……;

  • 添加/删除约束
    添加主键约束:ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,……);
    添加唯一约束:ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,……);
    添加外键约束:ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,……) reference_definition;
    添加默认约束:ALTER TABLE tbl_name ALTER [COLUMN] col_name SET DEFAULT literal;
    删除主键约束:ALTER TABLE tbl_name DROP PRIMARY KEY;
    删除唯一约束:ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
    删除外键约束:ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
    删除默认约束:ALTER TABLE tbl_name ALTER [COLUMN] col_name DROP DEFAULT;

  • 修改列定义/更名操作
    修改列定义:ALTER TABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST|AFTER col_name];
    修改列名称:ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name col_definition [FIRST|AFTER col_name];
    修改数据表名称
    方法一:ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
    方法二:RENAME TABLE tbl_name TO new_tbl_name [, tbl_name TO new_tbl_name]……;


版权声明:欢迎转载,欢迎扩散,但转载时请标明作者以及原文出处,谢谢合作!             ↓↓↓

推荐阅读更多精彩内容