一、主键约束 primary key

1. 一个表中只能有一个主键，可将一字段或者多个字段设置成联合主键
2. 主键值不能重复，不能为null空值

建表时定义主键约束
每个表只能定义一个主键

CREATE TABLE `user`(
    id INT PRIMARY KEY, -- PRIMARY KEY 主键约束
    username VARCHAR(22),
    `password` VARCHAR(22)
)

修改表时添加和删除主键约束

-- 添加主键约束
ALTER TABLE `user` ADD PRIMARY KEY (id);
-- 添加联合主键约束
ALTER TABLE 表名 ADD PRIMARY KEY (字段1,字段2);

-- 删除主键约束
ALTER TABLE `user` DROP PRIMARY KEY;

主键自增长 auto_increment

-- 默认从1开始，每插入一条数据 +1
CREATE TABLE `user`(
    id INT PRIMARY KEY AUTO_INCREMENT, 
    username VARCHAR(22),
    `password` VARCHAR(22)
)

-- 设置自增的初始值，从100开始
CREATE TABLE `user`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(22),
    `password` VARCHAR(22)
)
INSERT INTO `user` (id,username,`password`) VALUES (99,'azbycx','qe213dsd376'); -- 先初始插入一个id为99的列，再正式插入数据
INSERT INTO `user` (username,`password`) VALUES ('alias1','123456'); -- 100
INSERT INTO `user` (username,`password`) VALUES ('alias2','123456'); -- 101

二、外键约束 foreign key

1. 外键约束将两个或多个表表关联起来，一个表中可以有多个外键
2. 外键所在的表为从表，外键关联的主键所在的表为主表，从表的外键只能是主表的主键，不可以是其他值
3. 一般通过主表查询和删除从表，删除主表前，必须先删除从表（删除用户前，必须把用户相关的信息都删掉）
创建从表前必须先创建主表
4. 创建外键时不设置外键名，会自动添加默认的外键名 pk_主表名

建表时定义外键约束
用户详情表的uid作为外键，关联user表的主键id

-- 主表
CREATE TABLE `user`(
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(22),
    `password` VARCHAR(22)
)

-- 从表，添加外键约束：CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 主表名(主表主键)
CREATE TABLE user_info(
    id INT PRIMARY KEY AUTO_INCREMENT,
    age SMALLINT,
    height SMALLINT,
    uid INT,
    CONSTRAINT fk_user FOREIGN KEY(uid) REFERENCES user(id)  -- FOREIGN KEY 外键约束
)

修改表时添加和删除外键约束

-- 添加外键约束：ALTER TABLE 从表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名) REFERENCES 主表名(主表主键);
ALTER TABLE user_info ADD CONSTRAINT fk_user FOREIGN KEY(uid) REFERENCES user(id);

-- 删除外键约束：ALTER TABLE 从表名 DROP FOREIGN KEY 外键名;
ALTER TABLE user_info DROP FOREIGN KEY fk_user;

三、唯一约束 unique

字段值不能重复，只能有一个值为null空值

建表时定义唯一约束

CREATE TABLE `user`(
    id INT,
    username VARCHAR(22) UNIQUE, -- UNIQUE 唯一约束
    `password` VARCHAR(22)
)

修改表时添加和删除唯一约束

-- 添加唯一约束：ALTER TABLE 表名 ADD CONSTRAINT 唯一约束名 UNIQUE(字段名);
ALTER TABLE `user` ADD CONSTRAINT un_username UNIQUE(username);

-- 删除唯一约束：ALTER TABLE 表名 DROP INDEX 唯一约束名;
ALTER TABLE `user` DROP INDEX un_username;

四、非空约束 not null

字段的值不能为空

建表时定义非空约束

CREATE TABLE `user`(
    id INT,
    username VARCHAR(22) NOT NULL, -- NOT NULL 非空约束
    `password` VARCHAR(22) NOT NULL -- NOT NULL 非空约束
)

修改表时添加和删除非空约束

-- 添加非空约束：ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 数据类型 NOT NULL;
ALTER TABLE `user` CHANGE COLUMN username username int NOT NULL;

-- 删除非空约束：ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 数据类型 NULL;
ALTER TABLE `user` CHANGE COLUMN username username int NULL;

五、默认值约束 default

插入数据时，如果没有给默认值字段插入值，那么这个字段自动写入默认值

建表时定义默认值约束

-- 不插入password时，默认 '123456'
CREATE TABLE `user`(
    id INT,
    username VARCHAR(22),
    `password` VARCHAR(22) DEFAULT '123456' -- DEFAULT 默认值约束
)

-- 插入数据测试
INSERT INTO `user`(id,username) VALUES (1,'alias1');
INSERT INTO `user`(id,username) VALUES (2,'alias2');

修改表时添加和删除默认值约束

-- 添加默认值约束：ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 数据类型 DEFAULT 默认值;
ALTER TABLE `user` CHANGE COLUMN `password` `password` VARCHAR(22) DEFAULT '123456';


-- 删除默认值约束：ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 数据类型 DEFAULT NULL;
ALTER TABLE `user` CHANGE COLUMN `password` `password` VARCHAR(22) DEFAULT NULL;

六、检查约束 check

插入和修改数据时，对数据的值进行约束，可使用查询子句

建表时定义检查约束

-- age的范围大于0，小于150
CREATE TABLE user_info(
    id INT,
    age SMALLINT,
    CHECK(age > 0 AND age<150)
)

-- 插入数据测试
INSERT INTO user_info (id,age) VALUES (1,0);
INSERT INTO user_info (id,age) VALUES (2,100);
INSERT INTO user_info (id,age) VALUES (3,170);

修改表时添加和删除检查约束

-- 添加检查约束：ALTER TABLE 表名 ADD CONSTRAINT 检查约束名 CHECK检查约束表达式;
ALTER TABLE user_info ADD CONSTRAINT check_age CHECK(age > 0 AND age<250);

-- 删除检查约束：ALTER TABLE 表名 DROP CONSTRAINT  检查约束名;
ALTER TABLE user_info DROP CONSTRAINT check_age;

查看表约束

-- 方式一：查看表结构和表约束
DESC 表名;

-- 方式二：可查看表的所有约束信息
SHOW CREATE TABLE user_info;
-- Create Table 值如下：
-- CREATE TABLE `user_info` (
--   `id` int(11) DEFAULT NULL,
--   `age` smallint(6) DEFAULT NULL,
--   CONSTRAINT `check_age` CHECK (`age` > 0 and `age` < 250)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

-- 方式三：可查看表的约束名和约束类型
SELECT 
CONSTRAINT_CATALOG, -- 约束方案
CONSTRAINT_SCHEMA, -- 库名
CONSTRAINT_NAME, -- 约束名
TABLE_SCHEMA, -- 库名
TABLE_NAME,  -- 表名
CONSTRAINT_TYPE -- 约束类型
FROM
information_schema.TABLE_CONSTRAINTS
WHERE
CONSTRAINT_SCHEMA = '库名' AND TABLE_NAME='表名'