mysql——常见约束

常见约束

语法：
CREATE TABLE 表名(
    字段名 类型 列级约束
    字段名 类型
    表级约束
)

约束含义：一种，专门用于表中行或者列的数据，以保证最终数据的可靠性一致性。
分类：
    NOT NULL     ①非空约束    #保证字段值为非空
    DEFAULT      ②默认约束    #保证该字段有默认值
    PRIMARY KEY      ③主键约束    #保证该字段的值具有唯一性，且非空
    UNIQUE         ④唯一约束#保证字段的值具有唯一性
    CHECK         ⑤检查约束#mysql不支持
    FOREIGN KEY     ⑥外键约束#用于两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表中添加外键约束，用于引用主表中某列的值

在创建表或者修改表时添加约束

约束添加的分类
    列级约束：语法上均支持，但外键约束没有效果
    表级约束：除了非空和默认其他的都支持

经典面试题

关于主键和唯一约束的对比

关于主键和唯一约束的对比的验证：
1、唯一约束具有唯一性，也可以为空，但只可以有一个NULL
DROP DATABASE students;
CREATE DATABASE students;
DROP TABLE stuinfo;
DROP TABLE major;
SHOW INDEX FROM stuinfo;
CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20) 
);
CREATE TABLE stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);
TRUNCATE stuinfo;
DELETE FROM major;
INSERT INTO major VALUES(1,'java'),(2,'html');
INSERT INTO stuinfo VALUES(1,'join','男',20,NULL,1);      #唯一约束可以为空，但不可以有多个null
INSERT INTO stuinfo VALUES(2,'join','男',20,NULL,2);

2、一个表中可以有多个相同约束

CREATE TABLE stuinfo(
    id INT PRIMARY KEY,    #主键重复，最多有一个
    id2 INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,    #唯一约束，可以有多个
    seat2 INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

3、是否允许组合（两个列组合设置约束）
DROP DATABASE students;
CREATE DATABASE students;
DROP TABLE stuinfo;
DROP TABLE major;
SHOW INDEX FROM stuinfo;
CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20) 
);
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    sex CHAR(1),
    age INT,
    seat1 INT,
    seat2 INT,
    majorid INT,
    PRIMARY KEY(id,stuname),
    UNIQUE(seat1,seat2)
);
TRUNCATE stuinfo;
DELETE FROM major;
INSERT INTO major VALUES(1,'java'),(2,'html');

INSERT INTO stuinfo VALUES(1,'join','男',20,1,2,1);
INSERT INTO stuinfo VALUES(1,'join','男',20,1,2,2);

外键特点
①在从表设置外键关系
②从表的外键列的类型要求与主表的关联列要求一致或者兼容，名称无要求
③要求主表中的关联列必须是一个KEY(一般为主键、唯一键)DROP DATABASE students;
CREATE DATABASE students;
DROP TABLE stuinfo;
DROP TABLE major;
SHOW INDEX FROM stuinfo;
CREATE TABLE major(
    id INT,        #验证主表关联列必须为主键(或唯一键)

正确写法：id INT UNIQUE；
    majorName VARCHAR(20) 
);
CREATE TABLE stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

DROP DATABASE students;
CREATE DATABASE students;
DROP TABLE stuinfo;
DROP TABLE major;
SHOW INDEX FROM stuinfo;
CREATE TABLE major(
    id INT UNIQUE,        #验证主表关联列必须为唯一键(或主键)
    majorName VARCHAR(20) 
);
CREATE TABLE stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

④插入数据时先插入主表，再插入从表；删除数据时，先删除从表，再删除主表

注意：添加约束时可以同时添加多个(约束之间加空格)
id INT PRIMARY KEY DEFAULT 18;

一、创建表时添加约束

1、添加列级约束    

DROP DATABASE students;
CREATE DATABASE students;
USE students;
CREATE TABLE stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    gender CHAR(1) CHECK(gender='男' OR gender='女'),
    sit INT UNIQUE,
    age INT DEFAULT 18,
    majorid INT REFERENCES major(id)
);
DROP TABLE major;
CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20) 
);
DESC stuinfo;

SHOW INDEX FROM stuinfo;    #查看表中所有的索引

2、添加表级约束

语法：在各个字段的最下面
【CONSTRAINT 约束名】 约束类型(字段名)

CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT,
    CONSTRAINT pk PRIMARY KEY(id),    #pk为主键约束名(自拟)，但是主键名固定为primary，改名无效。为id加主键约束
    CONSTRAINT uq UNIQUE(seat),    #uq为约束名，为seat加唯一键约束
    CONSTRAINT ch CHECK(gender='男' OR gender='女'),#加检查约束
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
    #        外键名            主表中的外键       从表(添加外键的字段)
    #为major表中的id添加外键约束    
);

通用写法

DROP TABLE stuinfo;
CREATE TABLE stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

二、修改表时添加约束

语法：
添加列级约束：ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束类型;
添加表级约束：ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型(字段名)【外键引用】;
DROP TABLE stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT
);
1、添加非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
DESC stuinfo;
2、添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
DESC stuinfo;
3、添加主键
主键支持列级约束和表级约束
①列级约束
ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;
SHOW INDEX FROM stuinfo;
②表级约束
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
4、添加唯一键
①列级约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
②表级约束
ALTER TABLE stuinfo ADD UNIQUE(seat);
5、添加外键约束
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

三、修改表时删除约束

1、删除非空约束
ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;
2、删除默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT; 
3、删除主键
ALTER TABLE stuinfo MODIFY COLUMN id INT;
ALTER TABLE stuinfo DROP PRIMARY KEY; 
4、删除唯一键
ALTER TABLE stuinfo DROP INDEX seat;
5、删除外键约束
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major; 
习题：
1. 向表 emp2 的 id 列中添加 PRIMARY KEY 约束（my_emp_id_pk）

CREATE TABLE emp2(
    id INT
);

ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);
DESC emp2;

2. 向表 dept2 的 id 列中添加 PRIMARY KEY 约束（my_dept_id_pk）

ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id); 

3. 向表 emp2 中添加列 dept_id，并在其中定义 FOREIGN KEY 约束，与之相关联的列是
dept2 表中的 id 列。

DROP TABLE emp5;
ALTER TABLE emp2 ADD COLUMN dept_id INT 
ALTER TABLE emp2 ADD COLUMN CONSTRAINT fk_emp2_emp5 FOREIGN KEY(dept_id) REFERENCES dept2(id);