mysql-约束

什么是约束

约束就是表中数据的限制条件，表设计时加入约束的目的就是为了保证表中的记录完整和有效

 例如注册时要保证邮箱不重复，就可以使用约束

约束种类

• 非空约束(not null)
• 唯一性约束(unique)
• 主键约束(primary key) PK
• 外键约束(foreign key) FK

表级约束以及列级约束

列级约束：对一个数据列建立的约束，既可以在列定义时声明，也可以在列定义之后声明

表级约束：对多个数据列建立的约束，只能在列定义之后声明。

1、非空约束 NOT NULL

not null约束的字段不能为null值

//创建表 username 不能为空)
CREATE TABLE users (
    user_id int,
    username VARCHAR(20) NOT NULL COMMENT '非空约束'
); 
//插入数据没有值会报错
// INSERT INTO users (user_id) VALUES (1);
//ERROR 1364 (HY000): Field 'username' doesn't have a default value

2、唯一约束 UNIQUE

唯一约束的字段值不能重复，保证数据的唯一性，但是可以为多个null。

同一个表可以有多个唯一约束，多个列组合的约束

// 列级约束
CREATE TABLE users (
    id INT,
    email VARCHAR(50) UNIQUE
);

// 表级约束 `CONSTRAIANT name` 为给约束取的别名，方便以后删除，name为别名，可不写。
CREATE TABLE users (
    id INT,
    email VARCHAR(50),
    CONSTRAINT name UNIQUE KEY (email)
);
// 插入相同的数据会报错 执行两次插入语句
INSERT INTO users (id,email) VALUES (1,'ievi@Live.com'); 
// 第二次会报错：ERROR 1062 (23000): Duplicate entry 'ievi@Live.com' for key 'email'
// id 没有约束，所以不会报错


// 多个数据列约束 注意：两个或以上的字段同时与另一条记录相等才会报错

CREATE TABLE users (
    id int,
    username VARCHAR(20),
    email VARCHAR(50),
    UNIQUE KEY (username,email)
);

// 插入第一条数据
INSERT INTO users (id,username,email) VALUES (1,'张伟','ievi@Live.com');
// 没问题，插入第二条
INSERT INTO users (id,username,email) VALUES (1,'王刚','ievi@Live.com');
// 没问题，插入第三条
INSERT INTO users (id,username,email) VALUES (1,'张伟','ievi@Live.com');
// 由于满足两个字段都重复，所以报错
// ERROR 1062 (23000): Duplicate entry '张伟-ievi@Live.com' for key 'username'

 4、主键约束 PRIMARY KEY

为表中的某一个字段添加主键约束后，该字段就是主键字段，主键字段中出现的每一个数据都可称为主键值。

主键约束只能有一个(约束只能有一个，但可以作用到好几个字段) primary key(column1,column1,...)

主键约束要求作为主键的字段非空且唯一。(默认非空）

主键约束会默认添加`索引-index`

另：自增AUTO_INCREMENT必须与主键PRIMARY KEY组合使用，PRIMARY KEY可以单独使用

 

// 列级
CREATE TABLE users(
    id INT PRIMARY KEY,
    username VARCHAR(20)
);

// 表级 也可使用关键字`CONSTRAINT`起别名
CREATE TABLE users(
    id INT,
    username VARCHAR(20),
    PRIMARY KEY (id)
);

// 多个字段
CREATE TABLE users(
    id INT,
    username VARCHAR(20),
    email VARCHAR(50),
    PRIMARY KEY (id,email)
);

// 使用自增,值无需插入，自动生成，从1开始，以1递增
CREATE TABLE users(
    id INT AUTO_INCREMENT,
    username VARCHAR(20),
    PRIMARY KEY (id)
);
// 插入数据， 两次
INSERT INTO users (username) VALUES ('gif')
// +----+---------+
// | id | username|
// +----+---------+
// |  1 |   gif   |
// |  2 |   jpg   |
// +----+---------+

5、外键约束 FOREIGN KEY

一张表的一个字段受限于另外一张表的一个字段对应的值。涉及到的两张表：被引用的表叫主表（父表），另外一张叫从表（子表）。

两张表存储引擎必须为InnoDB,且外键字段与父表参照字段数据类型必须相似，数字长度及有无符号必须相同，两字段必须创建索引，如果外键字段无索引，MYSQL会自动创建

某个字段添加外键约束之后，该字段称为外键字段，外键字段中每个数据都是外键值

一张表可以有多个外键字段

只能表记定义

 

// 两张表用来储存用户及地区
// 省份表
CREATE TABLE provinces (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)
);

// 用户表 外键字段引用的父表的字段必须具有唯一性
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    user_sex tinytext,
    pid INT,
    FOREIGN KEY (pid) REFERENCES privinces (id)
);
// 现在写入数据 
// 必须先写父表
// 删除先删除子表数据
INSERT INTO provinces (name) VALUES ('河南');
INSERT INTO provinces (name) VALUES ('广东');
INSERT INTO provinces (name) VALUES ('浙江');
// 现在是这样
// +----+--------+
// | id | name   |
// +----+--------+
// |  2 | 广东   |
// |  1 | 河南   |
// |  3 | 浙江   |
// +----+--------+

// 写入用户表
INSERT INTO users (username,pid) VALUES ('麻花',2);
INSERT INTO users (username,pid) VALUES ('玛运',3);
INSERT INTO users (username,pid) VALUES ('加印',1);

// +------+----------+----------+-----+
// | user | username | user_sex | pid |
// +------+----------+----------+-----+
// |    1 | 麻花     | NULL     |   2 |
// |    2 | 玛运     | NULL     |   3 |
// |    3 | 加印     | NULL     |   1 |
// +------+----------+----------+-----+

// 如果写入父表不存在的值就会报错 ：
INSERT INTO users (username,pid) VALUES ('加印',9);
// ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`users `, CONSTRAINT `users _ibfk_1` FOREIGN KEY (`pid`) REFERENCES `provinces` (`id`))

// 查询
SELECT * FROM provinces_foreign INNER JOIN provinces ON users.pid = provinces.id;

// +------+----------+----------+-----+----+--------+
// | user | username | user_sex | pid | id | name   |
// +------+----------+----------+-----+----+--------+
// |    1 | 麻花     | NULL     |   2 |  2 | 广东   |
// |    2 | 玛运     | NULL     |   3 |  3 | 浙江   |
// |    3 | 加印     | NULL     |   1 |  1 | 河南   |
// +------+----------+----------+-----+----+--------+

 外键引用约束的参照操作

在使用外键时候可以参照的依赖关系，例如父类删除或更新子类也跟着删除或更新。

4种外键约束

    1、cascade:从父表删除或者更新且自动删除或更新子表中匹配的行

    2、set null:从父表删除或更新行，并设置子表中的外键行为null,如果使用该选项，必须保证子表列没有指定not null

    3、restrict：拒绝对父表的删除或更新操作。

    4、no action:标准SQL的关键字，在mysql中于restrict相同

 

// 演示 cascade 其他同理
// 新建表，加ON DELETE CASCADE
CREATE TABLE provinces_foreign (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    user_sex tinytext,
    pid INT,
    FOREIGN KEY (pid) REFERENCES privinces (id) ON DELETE CASCADE
);

//现在,当父表删除时子表也会一起删除
DELETE FROM provinces WHERE id = 1;
// 查看一下
SELECT * FROM provinces_foreign;

// +------+----------+----------+-----+
// | user | username | user_sex | pid |
// +------+----------+----------+-----+
// |    1 | 麻花     | NULL     |   2 |
// |    2 | 玛运     | NULL     |   3 |
// +------+----------+----------+-----+

ps:新手学习记录，有错误处请告知，谢谢！