约束

语法：[字段名] [数据类型] [约束条件];

非空约束 NOT NULL

某一列的值不能为null;


-- 1. 创建表时添加约束
-- 创建student表，并且name字段不能为null
CREATE TABLE student(
    name VARCHAR(10) NOT NULL,  -- name字段不能为空
    sex VARCHAR(4),
    age INT,
    math INT,
    english INT
);
-- DESC student
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(10) | NO   |     | NULL    |       |
| sex     | varchar(4)  | YES  |     | NULL    |       |
| age     | int         | YES  |     | NULL    |       |
| math    | int         | YES  |     | NULL    |       |
| english | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

-- 2.为已经存在的表添加唯一约束

ALTER TABLE student MODIFY age int NOT NULL;

-- DESC student
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(10) | NO   |     | NULL    |       |
| sex     | varchar(4)  | YES  |     | NULL    |       |
| age     | int         | NO   |     | NULL    |       |
| math    | int         | YES  |     | NULL    |       |
| english | int         | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

-- 3. 删除约束

ALTER TABLE student MODIFY name INT;

-- DESC student
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| name    | int        | YES  |     | NULL    |       |
| sex     | varchar(4) | YES  |     | NULL    |       |
| age     | int        | NO   |     | NULL    |       |
| math    | int        | YES  |     | NULL    |       |
| english | int        | YES  |     | NULL    |       |
+---------+------------+------+-----+---------+-------+

唯一约束:UNIQUE

某一列的值必须唯一;


1. 创建的时候添加唯一约束

CREATE TABLE student(
    name VARCHAR(10) NOT NULL,
    sex VARCHAR(4) NOT NULL,
    phone_number INT UNIQUE,
    identity_id VARCHAR(18)
);

-- DESC student;
-- 表中key 为UNI表示该字段有唯一约束，后面不在赘述
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| name         | varchar(10) | NO   |     | NULL    |       |
| sex          | varchar(4)  | NO   |     | NULL    |       |
| phone_number | int         | YES  | UNI | NULL    |       |
| identity_id  | varchar(18) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

2. 创建表之后添加唯一约束

ALTER TABLE student MODIFY identity_id VARCHAR(18) UNIQUE;

-- DESC student;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| name         | varchar(10) | NO   |     | NULL    |       |
| sex          | varchar(4)  | NO   |     | NULL    |       |
| phone_number | int         | YES  | UNI | NULL    |       |
| identity_id  | varchar(18) | YES  | UNI | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

3. 删除唯一约束

ALTER TABLE student DROP INDEX phone_number; 

-- DESC student 

+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| name         | varchar(10) | NO   |     | NULL    |       |
| sex          | varchar(4)  | NO   |     | NULL    |       |
| phone_number | int         | YES  |     | NULL    |       |
| identity_id  | varchar(18) | YES  | UNI | NULL    |       |
+--------------+-------------+------+-----+---------+-------+

主键约束 PRIMARY KEY

注意：

含义：非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识


-- 创建时添加主键

CREATE TABLE student(
    id INT PRIMARY KEY, -- 添加主键
    name VARCHAR(10),
    sex VARCHAR(4)
);

-- DESC student
-- NULL 为NO Key为PRI表示该字段为主键，后面不在赘述 
+-------+-------------+------+-----+---------+-------+ 
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+ 
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 删除主键

ALTER TABLE student DROP PRIMARY KEY;

-- DESC student
-- 这里要注意 删除主键时 不能把非空删了
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

-- 创建表之后，添加主键

ALTER TABLE student MODIFY id INT PRIMARY KEY;

-- DESC student
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

自动增长 AUTO_INCREMENT

概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长

注：如果自动增长的字段我们没有赋值则会根据上一条数据自动加一，如果我们指定了数值就会用我们指定的

AUTO_INCREMENT一般和PREIARY KEY 一起使用


1. 创建时增加 自动增长主键

CREATE TABLE student(
    id INT PRIMARY KEY AUTO_INCREMENT,-- 设置为主键，并自动增加
    name VARCHAR(10) ,
    sex VARCHAR(4);
)


-- DESC student;
-- Extra中有auto_increment 表示该字段为自动增长
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| sex   | varchar(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

2. 删除自动增长

ALTER TABLE student MODIFY id INT;

-- DESC studentl 注**这里没有删除主键**
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
| sex   | varchar(4)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

3. 表创建完成之后，增加 自动增长

ALTER TABLE student MODIFY id INT AUTO_INCREMENT;

-- DESC student;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| sex   | varchar(4)  | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+

外键约束 FOREIGN KEY

让表于表产生关系，从而保证数据的正确性

1. 创建外键关联

create table [表名](
....
外键列
constraint [外键名称] foreign key ([外键列名称]) references [主表名称]([ 主表列名称 ])
);

2 删除外键关联

ALTER TABLE [表名] DROP FOREIGN KEY [外键列名称];

3. 创建表之后添加外键关联

ALTER TABLE [表名] ADD CONSTRAINT [外键名称] FOREIGN KEY([外键字段名称]) REFERENCES[主表名称]([ 主表列名称 ]);

4. 级联操作

语法：ALTER TABLE [表名] ADD CONSTRAINT [外键名称]
FOREIGN KEY ([外键字段名称]) REFERENCES 主表名称([主表列名称]) ON UPDATE CASCADE ON DELETE CASCADE ;

ON UPDATE CASCADE 级联更新
级联删除：ON DELETE CASCADE 级联删除

4 .外键相关操作示例

/*
    现在有一个员工（employee）表 与部门（department）表
    想在需要将员工与部门关联
*/
-- 1. 创建的时候关联

-- 员工表关联部门表，所以部门表为主表，所以需要先创建部门表

CREATE TABLE department(
    id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(20)
);
-- 插入数据
INSERT INTO department(id,department_name) VALUES
(
    NULL,'移动开发部门'
),
(
    NULL,'JAVA开发部门'
),
(
    NULL,'UI设计部门'
),
(
    NULL,'人力资源部门'
);

-- SELECT * FROM department;
+----+--------------------+
| id | department_name    |
+----+--------------------+
|  1 | 移动开发部门       |
|  2 | JAVA开发部门       |
|  3 | UI设计部门         |
|  4 | 人力资源部门       |
+----+--------------------+

-- 创建员工表
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10),
    sex VARCHAR(4),
    external_id INT,-- 外键 ，该字段与主表主键关联
/*  constraint [外键名称] foreign key ([外键列名称]) references [主表名称]([主表列名称])*/
    CONSTRAINT department_id FOREIGN KEY (external_id) REFERENCES department(id)
);

-- 插入数据

INSERT INTO employee(name,sex,external_id) VALUES
(
    '张三','男',1 -- 如果external_id 在关联表中找不到就会报错
),
(
    '小龙女','女',3
),
(
    '乔峰','男',2
),
(
    '乐芙兰','女',4
);

-- DESC employee;
-- Key 为MUL 表示字段为外部关联字段
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int         | NO   | PRI | NULL    | auto_increment |
| name        | varchar(10) | YES  |     | NULL    |                |
| sex         | varchar(4)  | YES  |     | NULL    |                |
| external_id | int         | YES  | MUL | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

-- SELECT * FROM employee;
+----+-----------+------+-------------+
| id | name      | sex  | external_id |
+----+-----------+------+-------------+
|  1 | 张三      | 男   |           1 |
|  2 | 小龙女    | 女   |           3 |
|  3 | 乔峰      | 男   |           2 |
|  4 | 乐芙兰    | 女   |           4 |
+----+-----------+------+-------------+

注：这时候去删除部门表（department）中部门，如果被删除的部门被外键引用则删除会报错


-- 删除关联

**ALTER TABLE [表名] DROP FOREIGN KEY [外键名称];**
注：这里需要注意的是 外键名称！=字段名
SHOW CREATE TABLE employee; --查询创建表的SQL信息

| employee | CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `sex` varchar(4) DEFAULT NULL,
  `external_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `department_id` (`external_id`),
  CONSTRAINT `department_id` FOREIGN KEY (`external_id`) REFERENCES `department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 |

这里可以看出来外键名称是 department_id

ALTER TABLE employee DROP FOREIGN KEY department_id; --删除外键

-- 创建表完成后添加关联

ALTER TABLE employee ADD  CONSTRAINT department_id FOREIGN KEY (external_id) REFERENCES department(id);

目录CONTENT

mysql-常用约束

约束