目录
操作表的约束
MySQL软件所支持的完整性约束
完整性约束关键字 | 含义 |
---|---|
NOT NULL | 约束字段的值不能为空 |
DEFAULT | 设置字段的默认值 |
UNIQUE KEY (UK) | 约束字段的值是唯一 |
PRIMARY KEY (PK) | 约束字段为表的主键,可以作为该表记录的唯一标识 |
AUTO_INCREMENT | 约束字段的值为自动增加 (扩展约束) |
FOREIGN KEY (FK) | 约束字段为表的外键 |
1、设置非空约束(NOT NULL,NK)
约束该字段所有记录不能为空值,若为空则报错
语法形式:
CREATE TABLE table_name(
属性名 数据类型 NOT NULL,
…… ……
);
例:建立t_dept表,其中deptno的值约束为“不能为空”
mysql> CREATE TABLE t_dept(
-> deptno INT(20) NOT NULL,
-> dname VARCHAR(20),
-> loc VARCHAR(40)
-> );
Query OK, 0 rows affected, 1 warning (0.02 sec)
验证:
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int | NO | | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
如果用户插入的记录中该字段为空值,则数据库管理系统会报如下错误:“字段’depton’不能为空”
ERROR 1048(23000):Column 'deptno' cannot be null
2、设置字段的默认值(DEFAULT)
插入记录时,如果没有给该字段赋值,则系统自动为这个字段插入默认值。
语法形式:
CREATE TABLE table_name(
属性名 数据类型 DEFAULT 默认值,
…… ……
);
例:建立表,并将dname字段默认值设为CXW
mysql> CREATE TABLE t_dept(
-> depto INT NOT NULL,
-> dname VARCHAR(20) DEFAULT 'CXW',
-> loc VARCHAR(40)
-> );
Query OK, 0 rows affected (0.01 sec)
验证:
mysql> DESC t_dept;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| depto | int | NO | | NULL | |
| dname | varchar(20) | YES | | CXW | |
| loc | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
如果用户插入的新纪录中,该字段为空值时,则数据库管理系统会自动插入值CXW。
3、设置唯一约束(UNIQUE, UK)
加上“UNIQUE”约束条件后,记录中该字段的值不能重复,若重复则报错。
语法形式:
CREATE TABLE table_name (
属性名 数据类型 UNIQUE,
…… ……
);
例1:设置 dname 字段为UK约束
mysql> CREATE TABLE t_dept(
-> depto INT,
-> dname VARCHAR(20) UNIQUE,
-> loc VARCHAR(40)
-> );
Query OK, 0 rows affected (0.01 sec)
验证:
mysql> DESC t_dept;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| depto | int | YES | | NULL | |
| dname | varchar(20) | YES | UNI | NULL | |
| loc | varchar(40) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
如果用户插入的记录中该字段上有重复的值,则系统报如下错误:字段‘dname’中存在重复的条目‘**’
ERROR 1062(23000):Duplicate entry '**' for key 'dname'
例2:用CONSTRAINT 在进行UK约束的同时给UK设置一个名字
mysql> CREATE TABLE t_dept(
-> depto INT,
-> dname VARCHAR(20),
-> loc VARCHAR(40),
-> CONSTRAINT uk_dname UNIQUE(dname)
-> );
Query OK, 0 rows affected (0.01 sec)
在为约束设置标识符时,推荐使用 “约束缩写_字段名”,因此设置为uk_dname。
4、设置主键约束(PRIMARY KEY,PK)
主键字段的值是唯一、非空的。
4.1单字段主键
语法形式:
CREATE TABLE table_name(
数据名 数据类型 PRIMARY KEY,
…… ……
);
例1:建立表,并将字段“deptno”设为主键
mysql> CREATE TABLE t_dept(
-> deptno INT PRIMARY KEY,
-> dname VARCHAR(20),
-> loc VARCHAR(40)
-> );
Query OK, 0 rows affected (0.01 sec)
验证:主键不能为空且唯一
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int | NO | PRI | NULL | |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
例2:用CONSTRAINT 在进行PRIMARY约束的同时给PRIMARY设置一个名字
mysql> CREATE TABLE t_dept(
-> deptno INT,
-> dname VARCHAR(20),
-> loc VARCHAR(40),
-> CONSTRAINT pk_dname PRIMARY KEY(dname)
-> );
Query OK, 0 rows affected (0.01 sec)
4.2 多字段主键
当主键由多个字段组合而成,需要用CONSTRAINT来实现
语法形式:
CREAGTE TABLE table_name(
属性名 数据类型,
…… ……
【CONSTRAINT 约束名】 PRIMARY KEY(属性名,属性名…… ……)
);
例:建立表,并设置 deptno 和 dname 为 PK 约束
mysql> CREATE TABLE t_dept(
-> deptno INT,
-> dname VARCHAR(20),
-> loc VARCHAR(40),
-> CONSTRAINT pk_dname_deptno PRIMARY KEY(deptno,dname)
-> );
Query OK, 0 rows affected (0.01 sec)
验证:联合主键deptno 和 dname 值不能为空 且唯一
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int | NO | PRI | NULL | |
| dname | varchar(20) | NO | PRI | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
5、设置字段值自动增加(AUTO_INCREMENT)
唯一扩展的完整性约束,当增加一个记录时,已经设为该约束的字段上的值会自动生成唯一的ID
一个数据表中只能有一个字段使用该约束
该字段的数据类型必须是整数类型
该字段也会经常设置成PK主键
语法形式:
CREATE TABLE table_name(
属性名 数据类型 AUTO_INCREMENT,
…… ……
);
例:建立表,将字段 deptno 设置为AUTO_INCREMENT 和 PK 约束
mysql> CREATE TABLE t_dept(
-> deptno INT PRIMARY KEY AUTO_INCREMENT,
-> dname VARCHAR(20),
-> loc VARCHAR(40)
-> );
Query OK, 0 rows affected (0.01 sec)
验证:字段 deptno 被设置成主键(不能为空且唯一),且值自动增加(AUTO_INCREMENT)
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| deptno | int | NO | PRI | NULL | auto_increment |
| dname | varchar(20) | YES | | NULL | |
| loc | varchar(40) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
6、设置外键约束(FOREIGN KEY,FK)
- 设置外键约束的两个表之间具有父子关系,即子表中某个字段的取值范围由父表决定。
- 子表通过设置为外键约束的字段同父表的主键字段建立父子关系,从而通过该字段使两个表建立关系。
- 子表外键约束的字段数据类型必须与父表所参考的字段数据类型保持一致
- 理解:外键,与外部保持联系的键(涉外的字段)
语法形式:
CREATE TABLE table_name(
属性名 数据类型,
属性名 数据类型,
…… ……
CONSTRAINT 外键约束名 FOREIGN KEY (属性名1)
REFERENCES 表名(属性名2)
例:创建两个表,父表 ( t_dept ) 和子表(t-employee),设置父表的 deptno 字段为主键约束,子表的 deptno 字段为外键约束。
1、建立父表 并设置deptno 为主键
mysql> CREATE TABLE t_dept(
-> deptno INT PRIMARY KEY,
-> dname VARCHAR(20),
-> loc VARCHAR(40)
-> );
Query OK, 0 rows affected (0.01 sec)
2、建立子表,并设置字段 deptno 为外键
mysql> CREATE TABLE t_employee(
-> empno INT PRIMARY KEY,
-> ename varchar(20),
-> job VARCHAR(40),
-> MGR INT,
-> Hireadate DATE,
-> sal DOUBLE(10,2),
-> comm DOUBLE(10,2),
-> deptno INT,
-> CONSTRAINT fk_deptno FOREIGN KEY(deptno)
-> REFERENCES t_dept(deptno)
-> );
Query OK, 0 rows affected, 2 warnings (0.03 sec)
由于上一步出现了两个警告信息,因此执行 SHOW WARNINGS; 进行查看:
mysql> show warnings;
+---------+------+--------------------------------------------------------------
----------------------------------------------------+
| Level | Code | Message
|
+---------+------+--------------------------------------------------------------
----------------------------------------------------+
| Warning | 1681 | Specifying number of digits for floating point data types is
deprecated and will be removed in a future release. |
| Warning | 1681 | Specifying number of digits for floating point data types is
deprecated and will be removed in a future release. |
+---------+------+--------------------------------------------------------------
----------------------------------------------------+
2 rows in set (0.01 sec)
为什么会出现这种情况,我转载了网上大侠的文章,有兴趣的盆友可以看看【mysql 关于数字类型的几个坑】
验证:子表 t_employee 中 deptno 字段已经设置为FK约束。
mysql> DESC t_employee;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| empno | int | NO | PRI | NULL | |
| ename | varchar(20) | YES | | NULL | |
| job | varchar(40) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| Hireadate | date | YES | | NULL | |
| sal | double(10,2) | YES | | NULL | |
| comm | double(10,2) | YES | | NULL | |
| deptno | int | YES | MUL | NULL | |
+-----------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
如果用户插入的记录中,该字段没有参考父表 t_dept 中字段 deptno 的值,则系统会报如下错误:
ERROR 1452 (23000) : Cannot add or update a child row : a
foreign key constraint fails(省略……)
不能添加或更新子表记录,一个外键约束失效
生词表
单词 | 读音 | 译文 | MySQL |
---|---|---|---|
Null | 英[nʌl] | n. 数字零; 空位; 空白符号; 无信号 | 空值 |
Default | 英[dɪˈfɔːlt] | n. 默认; 违约(尤指未偿付债务); 系统设定值; 预置值; | 默认值 |
Unique | 英 [juˈniːk] | adj.唯一的;独一无二的; | 唯一 |
Primary | 英 [ˈpraɪməri] | adj.主要的;最重要的; | 主要 |
Increment | 英 [ˈɪŋkrəmənt] | n.定期的加薪;增量;增加 | 增加 |
Foreign | 英 [ˈfɒrən] | adj.外国的;涉外的; | 外部 |
Duplicate | 英 [ˈdjuːplɪkeɪt , ˈdjuːplɪkət] | adj.完全一样的;复制的;副本的 | 重复的 |
entry | 英 [ˈentri] | adj.n.进入(指权利等);进入(指行动);参与,加入(指权利、机会);参赛作品;参赛;参赛人数;(词典等的)条目;登记; | 条目 |
Constraint | 英 [kənˈstreɪnt] | 約束;约束条件;限制;束缚;制约 | 约束 |
References | 英 [ˈrefrənsɪz] | v.查阅;参考;给(书等)附参考资料; | 参考 |
如有错误敬请高人指点,书写的易读性可否,希望大家多提意见。