[MySQL] - 约束

1. 主键约束

• 主键：一张表的唯一标识。原则上：每张表必须有主键，且只能有一个主键
• 主键约束：
• 特点：被主键约束的字段，值必须是非空、唯一
• 语法：primary key

--创建一张商品表product（商品id 主键， 商品名称pname， 商品价格price）
CREATE TABLE product(
    id INT PRIMARY KEY,
    pname VARCHAR(100),
    price DOUBLE
);


-- 2. 验证主键的效果：值必须唯一、非空
INSERT INTO product (id,pname,price) VALUES (1,'Thinkpad T490', 8999);  -- 正常插入
INSERT INTO product (id,pname,price) VALUES (1,'macbook pro', 15999);   -- 不能插入，主键值重复了
INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);      -- 不能插入，主键值为空了

• 主键自增策略：

• 要求：主键字段必须是整数类型，MySql会自动生成主键值：1,2,3, … +1
• 语法：primary key auto_increment

-- 1. 创建一张商品表product（商品id 主键 自增， 商品名称pname， 商品价格price）
CREATE TABLE product(
    id INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(100),
    price DOUBLE
);
​
-- 2. 验证主键自增的效果：主键不设置值，或者设置为null，MySql会自动生成主键值
INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', 8999);  -- 正常插入
INSERT INTO product (id,pname,price) VALUES (NULL,'macbook pro', 15999);   -- 正常插入
INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 正常插入

2. 唯一性约束

• 效果：唯一性约束的字段，值不能重复，但是可以为null
• 语法：字段名 类型 unique

-- 1. 创建一张商品表product（商品id 主键 自增， 商品名称pname 唯一， 商品价格price）
CREATE TABLE product(
    id INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(100) UNIQUE,
    price DOUBLE
);
​
-- 2. 验证唯一性约束的效果：值不能重复
INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', 8999);  -- 正常插入
INSERT INTO product (id,pname,price) VALUES (NULL,'macbook pro', 15999);   -- 正常插入
INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 正常插入
INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 不能插入，pname值不唯一
INSERT INTO product (id,pname,price) VALUES (NULL,NULL, 5999);             -- 正常插入

3. 非空约束

• 效果：非空约束的字段，值不能为null
• 语法：字段名 类型 not null

-- 1. 创建一张商品表product（商品id 主键 自增， 商品名称pname 唯一、非空， 商品价格price）
CREATE TABLE product(
    id INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(100) UNIQUE NOT NULL,
    price DOUBLE
);
​
-- 2. 验证非空约束的效果：值不能为空
INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', 8999);  -- 正常插入
INSERT INTO product (id,pname,price) VALUES (NULL,'macbook pro', 15999);   -- 正常插入
INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 正常插入
INSERT INTO product (id,pname,price) VALUES (NULL,'lenovo', 5999);         -- 不能插入，pname值不唯一
INSERT INTO product (id,pname,price) VALUES (NULL,NULL, 5999);             -- 不能插入，pname值为空了

4. 默认值约束

• 效果：默认值约束的字段，如果不指定值，取默认值（如果指定null值，值就是null，不取默认值）
• 语法：字段名 类型 default 默认值

-- 1. 创建一张商品表product（商品id 主键 自增， 商品名称pname 唯一、非空， 商品价格price 默认值0）
CREATE TABLE product(
    id INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(100) UNIQUE NOT NULL,
    price DOUBLE DEFAULT 0
);
​
-- 2. 验证默认值的效果：不设置值的时候，取默认值
INSERT INTO product (id,pname,price) VALUES (NULL,'Thinkpad T490', NULL);  -- 插入null值，不取默认值
INSERT INTO product (id,pname) VALUES (NULL,'macbook pro');   -- 正常插入

5. 外键约束

• 效果：外键约束的字段，必须从指定表的主键中取值。目的：保证数据的一致性和完整性，避免脏数据
• 语法：

• 创建表时设置外键：

[constraint 约束名称] foreign key(外键字段) references 主表(主键)

• 已有表里设置外键：

alter table 表名称 add [constraint 约束名称] foreign key(外键字段) references 主表(主键)

-- 1. 创建一张商品分类表category（主键id， 分类名称cname）
CREATE TABLE category(
    id INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(50) NOT NULL UNIQUE
);
-- 2. 准备一些商品分类信息
INSERT INTO category (id,cname) VALUES (NULL, '家用电器'); -- id值是1
INSERT INTO category (id,cname) VALUES (NULL, '手机数码'); -- id值是2
INSERT INTO category (id,cname) VALUES (NULL, '电脑办公'); -- id值是3
​
-- 3. 创建一张商品表product（主键id，商品名称pname，商品价格price, 所属分类cid 外键 从category的id里取值）
CREATE TABLE product(
    id INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(100) NOT NULL UNIQUE,
    price DOUBLE DEFAULT 0,
    cid INT NOT NULL,
    CONSTRAINT product_category_fk FOREIGN KEY(cid) REFERENCES category(id)
);
​
-- 4. 准备一些商品数据
INSERT INTO product (id,pname,price,cid) VALUES (NULL, '小米电视4A 65', 2999, 1); 
-- 正常插入，cid的值是从category表的id里取的
INSERT INTO product (id,pname,price,cid) VALUES (NULL, '吉普JEEP牛仔裤男士青年商务休闲长裤子', 133.2, 4);-- 不能插入，但是插入的是脏数据，没有对应的分类
INSERT INTO product (id,pname,price,cid) VALUES (NULL, '吉普JEEP牛仔裤男士青年商务休闲长裤子', 133.2, NULL); -- 不能插入（因为外键字段上有约束not null）