MySQL中的约束

约束

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

  • 主键约束:primary key
  • 非空约束: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;

非空约束

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

创建表时添加约束

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

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

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

删除name的非空约束

        ALTER TABLE student MODIFY NAME VARCHAR(20);

唯一约束:unique

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

创建表时,添加唯一约束

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

        );

删除唯一约束

        ALTER TABLE stu DROP INDEX phone_number;

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

        ALTER TABLE stu 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, '清华大学', '北京');
INSERT INTO `school` VALUES (2, '北京大学', '北京');
INSERT INTO `student` VALUES (101, '张三', 1);
INSERT INTO `student` VALUES (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;