MySQL中的约束

约束

约束是对表中的数据进行限定,保证数据的正确性、有效性和完整性,约束分为下面几类:

  • 主键约束:primary key
  • 默认约束:default
  • 非空约束:not null
  • 唯一约束:unique
  • 外键约束:foreign key

主键约束:primary key

主键要求主键列的数据必须是唯一的,是表中记录的唯一标识,并且不能为NULL,主键与其他数据的关系类似于身份证与人的关系。

创建表时,添加主键约束

	create table student(
        id int primary key,-- 给id添加主键约束
        name varchar(20)
    );

删除主键

ALTER TABLE student DROP PRIMARY KEY;

创建完表后,添加主键

ALTER TABLE student MODIFY id INT PRIMARY KEY;

设置主键自动增长,在数据库的实际应用中,如果不希望自己手动添加主键,那么可以设置一个自动增长的字段为主键字段,自动增长的字段使用AUTO_INCREMENT实现,,在MySQL中默认的初始值是1,每增加一条记录,字段值会自动增加1,一个表中最多只能有一个字段设置为自动增长,自动增长的字段必须是主键。

在创建表时,添加主键约束,并且完成主键自增长

	create table student(
		id int primary key auto_increment,-- 给id添加主键约束
		name varchar(20)
	);

删除自动增长

ALTER TABLE student MODIFY id INT;

添加自动增长

ALTER TABLE student MODIFY id INT AUTO_INCREMENT;

默认约束

当我们希望给表中的某个字段设置默认值的时候,可以使用默认约束,例如给sex设置默认值为0

create table student(
    id int key,
	sex char(1) DEFAULT 0
)

非空约束

非空约束(NOT NULL)表示该字段的值不能为NULL,在添加数据时,必须指定值,创建表时添加约束

CREATE TABLE student(
    id INT,
    NAME VARCHAR(20) NOT NULL -- name为非空
);

删除name的非空约束

ALTER TABLE student MODIFY NAME VARCHAR(20);

创建表完后,添加非空约束

ALTER TABLE student MODIFY NAME VARCHAR(20) NOT NULL;

唯一约束:unique

唯一性约束(Unique)要求该列的值唯一,允许为空,只能出现一个空值。比如我们注册app或者网站的时候会输入手机号,这个手机号通常可以添加唯一约束,一张表中可以有多个字段设置成唯一约束。

创建表时,添加唯一约束

CREATE TABLE student(
	id INT,
	phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束
);

删除唯一约束

ALTER TABLE student DROP INDEX phone_number;

在创建表后,添加唯一约束

ALTER TABLE student MODIFY phone_number VARCHAR(20) UNIQUE;

外键约束:foreign key

外部的关键字叫做外键,通常为多张表中建立联系,确保表与表之间的数据安全性,一致性,能够减少数据冗余(重复的数据),一个表可以有多个外键,表的外键可以是NULL,如果不为NULL,则外键值必须等于另外一个表中的主键值。定义外键后, 不允许删除在主键表中的关联记录行。

在创建表时,可以添加外键

CREATE TABLE school (
  id int(11) primary key,
  school_name varchar(50) ,
  address varchar(100)
);

CREATE TABLE student (
  id int(11) primary key,
  name varchar(20) DEFAULT NULL,
  schoolid int(11),
  CONSTRAINT schoolid_fk FOREIGN KEY (schoolid) REFERENCES school(id)
);

添加一些数据

INSERT INTO `school` VALUES (1, '清华大学', '北京'),(2, '北京大学', '北京');
INSERT INTO `student` VALUES (101, '张三', 1),(102, '李四', 2);

因为在school表中没有主键是3的内容,执行下面语句时会报错:

INSERT INTO `student` VALUES (103, '王五', 3);

此时如果要删除school中id是1的数据时也会报错,这里存在外键关联,所以无法删除,需要先把对应的外键关联的数据删除之后才能删除该数据。

delete from school WHERE id=1

级联操作

级联更新:ON UPDATE CASCADE

级联删除:ON DELETE CASCADE

设置外键关联之后,我们无法执行上面的sql语句,此时可以设置级联删除,设置成功之后当我们执行上面语句的时候,mysql会将student表中外键是1的数据删除。

先删除,再设置级联

ALTER TABLE student DROP FOREIGN KEY schoolid_fk;
ALTER TABLE student ADD CONSTRAINT schoolid_fk FOREIGN KEY (schoolid) REFERENCES school(id) ON DELETE CASCADE;