DDL_3_常见约束
/*
含义:
一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
分类:六大约束
NOT NULL:非空,用于保证该字段的值不能为空
比如姓名、学号等
DEFAULT:默认,用于保证该字段有默认值
比如性别
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空
比如学号、员工编号等
UNIQUE:唯一,用于保证该字段的值具有唯一性,可以为空
比如座位号
CHECK:检查约束【mysql中不支持】
比如年龄、性别
FOREIGN KEY:外键,用于限制两个表的关系,用于保证该字段的值必须来自于主表的关联列的值
在从表添加外键约束,用于引用主表中某列的值
比如学生表的专业编号,员工表的部门编号,员工表的工种编号
添加约束的时机:
1.创建表时
2.修改表时
约束的添加分类:
列级约束:
六大约束语法上都支持,但外键约束没有效果
表级约束:
除了非空、默认,其他的都支持
主键和唯一的大对比:
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √,但不推荐
唯一 √ √ 可以有多个 √,但不推荐
组合主键:
primary key(id,stuname)#两条记录的两个值全一样才会报错
(1,ocean)和(1,lily)
(1,ocean)和(2,ocean) 都不会报错
只有(1,ker)和(1,ker)才会报错
外键:
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容,名称无要求
3、主表的关联列必须是一个key(一般是主键或唯一)
4、插入数据时,先插入主表,再插入从表
删除数据时,先删除从表,再删除主表
*/
CREATE TABLE 表名(
字段名 字段类型 列级约束,
字段名 字段类型,
表级约束
)
一、创建表时添加约束
1.添加列级约束
/*
语法:
直接在字段名和类型后面追加 约束类型即可
只支持:默认、非空、主键、唯一
*/
CREATE DATABASE students;
USE studentS;
CREATE TABLE STUINFO(
ID INT PRIMARY KEY,#主键
stuName VARCHAR(20) NOT NULL,#非空
gender CHAR(1) CHECK(gender='男' OR gender='女'),#检查 MYSQL中没效果
SEAT INT UNIQUE,#唯一
AGE INT DEFAULT 18,#默认约束 18岁
majorid INT REFERENCES major(ID)#外键
);
CREATE TABLE major(
ID INT PRIMARY KEY,
majorName VARCHAR(20)
);
DESC STUINFO;
SHOW INDEX FROM STUINFO; #查看索引
2.添加表级约束
/*
语法:
各个字段最下面
【constraint 约束名】 约束类型(字段名)
*/
DROP TABLE IF EXISTS STUINFO;
CREATE TABLE STUINFO(
ID INT,
STUNAME VARCHAR(20),
GENDER CHAR(1),
SEAT INT,
AGE INT,
MAJORID INT,
CONSTRAINT PK PRIMARY KEY(ID),#主键
CONSTRAINT UQ UNIQUE(SEAT),#唯一
CONSTRAINT CK CHECK(gender='男' OR gender='女'),#检查
CONSTRAINT FK_STUINFO_MAJOR FOREIGN KEY(MAJORID) REFERENCES major(ID)#外键
);
3.通用写法
CREATE TABLE IF NOT EXISTS STUINFO(
ID INT PRIMARY KEY,
STUNAME VARCHAR(20) NOT NULL UNIQUE,#可以多个 用空格隔开
SEX CHAR(1),
AGE INT DEFAULT 18,
SEAT INT UNIQUE,
MAJORID INT,
CONSTRAINT FK_STUINFO_MAJOR FOREIGN KEY(MAJORID) REFERENCES major(ID)#外键缩写_从表名_主表名
);
二、修改表时添加约束
/*
1、添加列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
2、添加表级约束
alter table 表名 add 【constraint 约束名】 约束类型(字段名) 【外键的引用】;
*/
DROP TABLE IF EXISTS STUINFO;
CREATE TABLE STUINFO(
ID INT,
STUNAME VARCHAR(20),
GENDER CHAR(1),
SEAT INT,
AGE INT,
MAJORID INT
);
DESC STUINFO;
SHOW INDEX FROM STUINFO;
1.添加非空约束
ALTER TABLE STUINFO MODIFY COLUMN STUNAME VARCHAR(20) NOT NULL;
2.添加默认约束
ALTER TABLE STUINFO MODIFY COLUMN AGE INT DEFAULT 18;
3.添加主键
#1.列级约束
ALTER TABLE STUINFO MODIFY COLUMN ID INT PRIMARY KEY;
#2.表级约束
ALTER TABLE STUINFO ADD PRIMARY KEY(ID);
4.添加唯一
#1.列级约束
ALTER TABLE STUINFO MODIFY COLUMN SEAT INT UNIQUE;
#2. 表级约束
ALTER TABLE STUINFO ADD UNIQUE(SEAT);
5.添加外键
ALTER TABLE STUINFO ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(MAJORID) REFERENCES MAJOR(ID);
三、修改表时删除约束
6.删除非空约束
ALTER TABLE STUINFO MODIFY COLUMN STUNAME VARCHAR(20) NULL;
7.删除默认约束
ALTER TABLE STUINFO MODIFY COLUMN AGE INT;
8.删除主键
ALTER TABLE STUINFO DROP PRIMARY KEY;
9.删除唯一
ALTER TABLE STUINFO DROP INDEX SEAT;
10.删除外键
SHOW CREATE TABLE STUINFO;
ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;
四、练习
例1. 向表 emp2 的 id 列中添加 PRIMARY KEY 约束(my_emp_id_pk)
ALTER TABLE EMP2 MODIFY COLUMN ID INT PRIMARY KEY;#列级
ALTER TABLE EMP2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(ID);#表级
例2. 向表 dept2 的 id 列中添加 PRIMARY KEY 约束(my_dept_id_pk)
ALTER TABLE dept2 MODIFY COLUMN ID INT PRIMARY KEY;#列级
ALTER TABLE dept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(ID);#表级
例3. 向表 emp2 中添加列 dept_id,并在其中定义 FOREIGN KEY 约束,与之相关联的列是dept2 表中的 id 列。
ALTER TABLE EMP2 ADD COLUMN dept_id INT;
ALTER TABLE EMP2 ADD CONSTRAINT FK_EMP2_DEPT2 FOREIGN KEY(dept_id) REFERENCES dept2(ID);
五、列级与表级区别
位置 支持约束类型 是否可以起约束名
列级约束: 列的后面 语法都支持,但外键没有效果 不可以
表级约束: 所有列的下面 默认和非空不支持,其他支持 可以(主键没效果)