1、约束的作用
对表中的数据进行限制,保证数据的正确性、有效性和完整性。一个表如果添加了约束,不正确的
数据将无法插入到表中。约束在创建表的时候添加比较合适。
2、约束种类
约束名 | 约束关键字 |
---|---|
主键 | primary key |
唯一 | unique |
非空 | not null |
外键 | foreign key |
2.1 主键约束
主键是用来唯一标识数据库中的每一条记录。
例如:表中的id字段。
注意:通常不用业务字段作为主键,单独给每张表设计一个 id 的字段,把 id 作为主键。主键是给数据库
和程序使用的,不是给最终的客户使用的。所以主键有没有含义没有关系,只要不重复,非空就行。
创建:使用关键字primary key
格式:字段名 字段类型 PRIMARY KEY
特点:非空,不能重复(所以通常把主键设置为自增)
例如:
①创建主键非自增
create table `stu01` (
`id` int(3) primary key, -- id 为主键
`name` varchar(20),
`age` int(3)
);
②创建主键自增
create table `stu02` (
`id` int(3) auto_increment primary key, -- id 为主键且自增
`name` varchar(20),
`age` int(3)
);
添加主键:
alter table stu01 add primary key(id);
删除主键:
alter table stu01 drop primary key;
定义主键从什么地方开始自增
CREATE TABLE 表名(
列名 int primary key AUTO_INCREMENT
) AUTO_INCREMENT=起始值;
创建好以后修改起始值
alter table stu01 auto_increment = 2000;
单列主键和复合主键对比
-- 单列主键
CREATE TABLE table_name (
id INT PRIMARY KEY,
column1 datatype,
column2 datatype,
);
-- 复合主键
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
PRIMARY KEY (column1, column2)
);
复合主键自增情况
当主键由多列组成时,只能选择其中的一个列作为自增列。
在Mysql中
CREATE TABLE table_name (
id INT AUTO_INCREMENT,
column1 datatype,
column2 datatype,
PRIMARY KEY (id, column1)
);
2.2 唯一约束
唯一约束:表中某一列不能出现重复的值
格式:字段名 字段类型 UNIQUE
例如:
create table `stu03` (
id int(11),
name varchar(20) unique
);
向表中插入数据
INSERT INTO stu03 VALUES(1,'小明');
再次向表中插入数据,且name字段的值也为小明
INSERT INTO stu03 VALUES(2,'小明');
出现错误信息
注意:如果插入值为null,不会出现这个问题。
2.3 非空约束
非空约束:某一列不能为 null
格式:字段名 字段类型 NOT NULL
例如:
create table `stu04` (
id int,
name varchar(20) not null,
gender char(1)
);
向表中插入数据
INSERT INTO stu04 VALUES(2,'小明','男');
如果字段被设置成为not null,怎么避免插入数据出错?可以使用设置默认值的方式。
格式:字段名 字段类型 DEFAULT 默认值
例如:
创建一张表,address设置了默认值
create table `stu05` (
id int(10),
name varchar(20),
address varchar(20) not null default '重庆'
);
插入一条记录
insert into `stu05` values (1, '李四', default);
注意:如果一个字段设置了非空与唯一约束,该字段与主键的区别?
主键必须是唯一且非空的,因此从这个角度来看它们是相同的。
但是,一张表只能有一个主键,而您可以有多个唯一的非空键。
2.4 外键约束
外键:在从表中与主表主键对应的那一列。
主表:用来约束别人的表,一方
从表(副表):被别人约束的表,多方
格式
新建表时增加外键:[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名);
创建主键的时候,必须注意主表的主键和从表的外键,也就是这关联的两个字段的字段的类型,约束等一致
-- 主表:用来约束别人的表,一方
CREATE TABLE if not EXISTS rolelist(
roleid int(4) PRIMARY KEY auto_increment,
role_name VARCHAR(255)
)
INSERT into rolelist VALUES (1,"最高权限");
INSERT into rolelist VALUES (2,"中等权限");
INSERT into rolelist VALUES (3,"低级权限");
-- 从表(副表):被别人约束的表,多方
CREATE TABLE if NOT EXISTS user_c(
id int(11) PRIMARY KEY auto_increment,
username VARCHAR(22),
password VARCHAR(255),
roleid int(11) NOT NULL,-- 外键对应着主表的主键
constraint roleid_fk foreign key (roleid) references rolelist(roleid)
)
INSERT into user_c VALUES (null,"aaa","aaa",1);
INSERT into user_c VALUES (null,"bbb","bbb",2);
INSERT into user_c VALUES (null,"ccc","ccc",3);
-- 当运行下面这个语句报错,外键没有等于4的roleid
-- INSERT into user_c VALUES (null,"ddd","ddd",4);
对已有表增加外键
ALTER TABLE 从表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (从表外键字段) REFERENCES 主表名称(主表主键);
删除存在的外键
alter table 表名称 drop foreign key 外键名称;
外键的级联
当俩个表建立连接后,如果主表中要对其主键进行修改,而从表外键不做更改,就会出现错误;
UPDATE rolelist SET id=5 WHERE id=2;
不能直接修改,会报错:Unknown column ‘id’ in ‘where clause’,如果副表中有引用的数据,不能直接修改主表主键。
DELETE FROM rolelist WHERE id = 1;
不能直接删除,会报错:Unknown column ‘id’ in ‘where clause’, 如果副表中有引用的数据,不能直接删除主表数据。
什么是级联操作
在修改和删除主表的主键时,同时更新或删除副表的外键值,称为级联操作
ON UPDATE CASCADE
– 级联更新,主键发生更新时,外键也会更新
ON DELETE CASCADE
– 级联删除,主键发生删除时,外键也会删除
具体操作
- 删除user_c表
- 重新创建user_c表,添加级联更新和级联删除
-- 从表(副表):被别人约束的表,多方
CREATE TABLE if NOT EXISTS user_c(
id int(11) PRIMARY KEY auto_increment,
username VARCHAR(22),
password VARCHAR(255),
roleid int(11) NOT NULL,
-- 添加外键约束,并且添加级联更新和级联删除
constraint roleid_fk foreign key (roleid) references rolelist(roleid) ON UPDATE CASCADE ON DELETE CASCADE
)
再次添加数据到user_c表
INSERT into user_c VALUES (null,"aaa","aaa",1);
INSERT into user_c VALUES (null,"bbb","bbb",2);
INSERT into user_c VALUES (null,"ccc","ccc",3);
从表:
把rolelist表中id等于1的部门改成id等于10
UPDATE rolelist SET roleid=10 WHERE roleid=1;
主表:
从表:
- 删除权限号是2的记录
DELETE FROM rolelist WHERE roleid=2;
主表:
从表: