MySQL基础知识（二)——数据表和约束

十二.约束

约束可以保证数据的完整性和一致性；约束分为表级约束和列级约束。

1.PRIMARY KEY主键约束

• 每张数据表只能存在一个主键
• 主键保证记录的唯一性
• 主键自动为NOT NULL

#自增主键，使用 PRIMARY KEY
CREATE TABLE IF NOT EXISTS user4(id INT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) UNIQUE KEY,age TINYINT UNSIGNED,salary FLOAT(9,2) UNSIGNED);
#自增主键，直接使用 KEY也是OK的，但建议直接使用 PRIMARY KEY，可读性好，比较统一
CREATE TABLE IF NOT EXISTS user5(id INT(2) UNSIGNED AUTO_INCREMENT KEY,username VARCHAR(20) UNIQUE KEY,age TINYINT UNSIGNED,salary FLOAT(9,2) UNSIGNED);
#非自增主键
CREATE TABLE IF NOT EXISTS user6(id INT(2) UNSIGNED PRIMARY KEY,username VARCHAR(20) UNIQUE KEY,age TINYINT UNSIGNED,salary FLOAT(9,2) UNSIGNED);

创建完成后，可以使用desc tablename;命令，查看表结构，主键是否增加成功。

如图所示可以看到主键创建成功。

  

2.UNIQUE KEY 唯一约束

• 唯一约束可以保证记录的唯一性；
• 唯一约束的字段可以为NULL；
• 每张数据表可以存在多个唯一约束。

#为username字段 设置唯一约束
CREATE TABLE IF NOT EXISTS user4(id INT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20) UNIQUE KEY,age TINYINT UNSIGNED,salary FLOAT(9,2) UNSIGNED);

创建完成后，可以使用desc tablename;命令，查看表结构，唯一约束是否增加成功。

如图所示可以看到唯一约束创建成功。

 

设置唯一约束的元素要有唯一性不能重复。如果username重复数据库会报错：

ERROR 1062 (23000): Duplicate entry 'kimtian' for key 'username'

3.DEFAULT 默认约束 

• 默认值；
• 当插入记录时，如果没有明确为字段赋值，则自动赋予默认值。

#为sex性别字段设置默认值，1表示男，2表示女，3表示保密，不传时候默认为3
CREATE TABLE tbl(id INT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,age TINYINT UNSIGNED,sex ENUM('1','2','3') DEFAULT '3');

 向表table中插入一条数据：insert tbl (age) VALUES (18); 我们没并没有给性别赋值。查看表数据，可以看到性别默认被填写了3。

4.NOT NULL 非空约束

#area地域字段不能为空
CREATE TABLE tbl1(id INT(2) UNSIGNED AUTO_INCREMENT PRIMARY KEY,age TINYINT UNSIGNED,sex ENUM('1','2','3') DEFAULT '3',area varchar(20) NOT NULL);

area地域字段不能为空，所以我们在插入数据时候，必须给area字段赋值，否则会报错：ERROR 1364 (HY000): Field 'area' doesn't have a default value

同样如果给area赋值NULL也会报这个错误：

5.FOREIGN KEYp 外键约束

• 保证数据一致性，完整性；
• 实现一对一或一对多关系。

创建外键约束的要求：

• 父表和子表必须使用相同的存储引擎，而且禁止使用临时表；
• 数据表的存储引擎只能为InnoDB；
• 外建列和参照列必须具有相似的数据类型。其中数字长度或是否有符号位必须相同，而字符的长度则可以不同；
• 外键列和参照列必须创建索引，如果外键列不存在索引的话，MySQL将会自动创建索引。

如下，创建两张表，一张省份表，一张城市表，城市表有个外键pid，参照省份表的id列。

#省份表--参照表，也称作父表，id为参照列。
CREATE TABLE provinces(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20) NOT NULL);
#城市表--子表，pid为外键列。
CREATE TABLE citys(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,cityname VARCHAR(20) NOT NULL,pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces(id));

外键约束的参照条件：

CASCADE	从父表删除或更新时，自动删除和更新子表中匹配的行。
SET NULL	从父表删除或更新行时，设置子表中的外键列为NULL，如果使用该选项，必须保证子表外键列没有指定NOT NULL。
RESTRICT	拒绝对父表的更新或删除操作。
NO ACTION	标准SQL的关键字，在MySQL中与RESTRICT相同。
默认	拒绝对父表的更新或删除操作。

用法如下： 

#ON DELETE CASCADE 删除时，自动删除和更新子表中匹配的行。
CREATE TABLE citys1(id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,cityname VARCHAR(20) NOT NULL,pid SMALLINT UNSIGNED, FOREIGN KEY (pid) REFERENCES provinces(id) ON DELETE CASCADE);

使用命令SHOW CREATE TABLE tblname;

可以查看到以下信息：

（1）ENGINE=InnoDB引擎是InnoDB。

（2）KEY `pid` (`pid`)。外键列pid已经被自动创建了索引。

（3）CONSTRAINT `citys_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`)外键索引被成功创建，外键索引名称为citys_ibfk_1。

 也可以使用SHOW INDEX FROM tablename;查看表中索引情况。