约束
概念:对表中的数据进行限定,保证数据的正确性、有效性和完整性。
分类:
1. 主键约束:primary key
2. 非空约束:not null
3. 唯一约束:unique
4. 外键约束:foreign key
非空约束:not null,即被修饰的一列,值不能为空
- 创建表时添加约束
CREATE TABLE stu(
id INT,
NAME VARCHAR(20) NOT NULL -- name为非空
);
- 创建表完后添加约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;
- 删除表的非空约束:
ALTER TABLE stu MODIFY NAME VARCHAR(20);
唯一约束:unique,即被修饰的一列,值不能重复
1.-- 创建表时,添加唯一约束:
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE -- 添加唯一约束
);
- 删除唯一约束:
ALTER TABLE stu MODIFY phone_number VARCHAR(20); //错的!
执行没有报错,但是修改列中数据时发现,如果该列值重复,依然会报错。
说明上面的语句并没有删除成功。
解决:
ALTER TABLE stu DROP INDEX phone_number;
- 在创建表后添加唯一约束:
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
注意: 在MySQL里,唯一约束限定的列的值可以存在多个null。
主键约束:primary key
-
注意:
- 含义:非空且唯一
- 一张表只能有一个字段为主键
- 逐渐就是表中记录的唯一标识
-
在创建表时,添加主键约束
CREATE TABLE stu(
id INT PRIMARY KEY, -- 给id添加主键约束
NAME VARCHAR(20)
);
- 删除主键:
ALTER TABLE stu MODIFY id INT;-- 也不能生效。
应该为:
ALTER TABLE stu DROP PRIMARY KEY;
因为一个表中只有一个主键,所以语句中不出现字段。
- 在创建表后添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
这里,如果该列数据有值重复或为空,执行该句会报错。
- 自动增长:
- 概念:
如果某一列是数值类型的,使用auto_increment可以完成值的自动增长 - 在创建表时,添加主键约束,并完成主键的自增长
注意:自动增长时,数据只跟上一行记录有关。
- 概念:
CREATE TABLE stu(
, -- 给id添加主键约束
NAME VARCHAR(20)
);
- 删除自动增长:
ALTER TABLE stu MODIFY id INT;
- 添加自动增长:
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;
外键约束:foreign,让表产生关系,从而保证数据的正确性。
有这么一张表:
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('张三',20,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('李四',21,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('王五',20,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('老王',20,'销售部','深圳');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('大王',20,'销售部','深圳');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('小王',20,'销售部','深圳');
发现数据有冗余,而且修改复杂,于是用到了表的拆分
解决:分成两张表,一张员工表,一张部门表(主表)。
- 在创建表时,可以添加外键
语法:
create table 表名(
……
外键列
constraint 外键名 foreign key (外键列名称) reference 主表名称(主表列名称)
);
所以上述问题的解决方法为:
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT, -- 外键对应主表的主键
CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department(id)
);
INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');
INSERT INTO employee (NAME,age,dep_id) VALUES ('张三',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES ('李四',21,1);
INSERT INTO employee (NAME,age,dep_id) VALUES ('王五',20,1);
INSERT INTO employee (NAME,age,dep_id) VALUES ('老王',20,2);
INSERT INTO employee (NAME,age,dep_id) VALUES ('大王',22,2);
INSERT INTO employee (NAME,age,dep_id) VALUES ('小王',18,2);
- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
- 创建表后添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)
REFERENCES 主表名称(主表列名称);
- 级联操作
1. 分类:
级联更新:
ON UPDATE CASCADE
级联删除:
ON DELETE CASCADE
2. 在添加外键的时候设置级联更新:
ALTER TABLE employee ADD CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department(id);
3. 在添加外键的时候设置级联删除:
ALTER TABLE employee ADD CONSTRAINT emp_dep_id FOREIGN KEY (dep_id) REFERENCES department(id) ON DELETE CASCADE; - 注意:外键列的值可以为null,但是不能为不存在的外键值。
级联操作有优势(方便),也有弊端,特别是级联删除,很危险,而且影响效率。