mysql常见约束

最新推荐文章于 2025-04-04 17:47:22 发布

Admin-user

最新推荐文章于 2025-04-04 17:47:22 发布

阅读量438

点赞数 10

文章标签： mysql android 数据库

本文链接：https://blog.youkuaiyun.com/u013172533/article/details/144128348

版权

#常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性(或者叫一致性)

分类：六大约束
    NOT NULL：非空，用于保证该字段的值不能为空
    比如姓名、学号等
    DEFAULT:默认，用于保证该字段有默认值
    比如性别
    PRIMARY KEY:主键，用于保证该字段的值具有唯一性，并且非空
    比如学号、员工编号等
    UNIQUE:唯一，用于保证该字段的值具有唯一性，可以为空
    比如座位号
    CHECK:检查约束【mysql中不支持】
    比如年龄、性别
    FOREIGN KEY:外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值
        在从表添加外键约束，用于引用主表中某列的值
    比如学生表的专业编号，员工表的部门编号，员工表的工种编号

添加约束的时机：
    1.创建表时
    2.修改表时

约束的添加分类：
    列级约束：注意：列级约束不支持取名字，即列级约束无法取约束名
        六大约束语法上都支持，但外键约束没有效果

    表级约束：表级约束支持取名字，即表级约束可以取约束名

        除了非空、默认，其他的都支持


主键和唯一的大对比：

        保证唯一性是否允许为空       一个表中可以有多少个          是否允许组合
    主键    √        ×        至多有1个(也可以没有)        √，但不推荐
    唯一    √       √(只能有一个为空)    可以有多个                   √，但不推荐
外键：
    1、要求在从表设置外键关系
    2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
    3、主表的关联列必须是一个key（一般是主键或唯一）
    4、插入数据时，先插入主表，再插入从表
    删除数据时，先删除从表，再删除主表

/*
CREATE TABLE 表名(
    字段名字段类型列级约束,
    字段名字段类型,
    表级约束

);
*/

#一、创建表时添加约束

#1.添加列级约束
/*
语法：

直接在字段名和类型后面追加约束类型即可。

只支持：默认、非空、主键、唯一

#mysql中不支持CHECK检查约束，写了CHECK检查约束不会报错，写了CHECK检查约束，语法不会报错，但是不起效果
CREATE TABLE person(
    id INT PRIMARY KEY,#主键
    uName VARCHAR(20) NOT NULL,#非空
    gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查(虽然语法不会报错，但是该约束不起效果，即不支持检查约束)
    seat INT UNIQUE,#唯一
    age INT DEFAULT 20,#默认约束
    countryId INT REFERENCES country(id)#外键(虽然语法不会报错，但是该约束不起效果，即不支持列级外键约束)
);
#
DESC person;
#
#查看person表中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM person;

#
CREATE TABLE myUser(
    id INT PRIMARY KEY,#主键
    uName VARCHAR(30) NOT NULL UNIQUE,#非空约束
    #在mysql中不支持检查约束
    gender CHAR(1) CHECK(gender IN ('男', '女')),#检查(虽然语法不会报错，但是该约束不起效果，即不支持检查约束)
    seat INT UNIQUE,#唯一
    age INT DEFAULT 23, #默认约束
    countryId INT REFERENCES country(id)#外键(虽然语法不会报错，但是该约束不起效果，即不支持列级外键约束)
);

CREATE TABLE country(
id INT PRIMARY KEY,
cName VARCHAR(20)
);
#
DESC country;
#
DESC myUser;
#查看myUser表中的所有索引，包括主键、外键、唯一
SHOW INDEX FROM myUser;

#2.添加表级约束
/*

语法：在各个字段的最下面
【constraint 约束名】约束类型(字段名)
注意：可以不起约束名，会有一个默认的约束名
*/

/*表级约束：除了非空、默认，其他的都支持*/

CREATE TABLE myUser2(
    id INT,
    uName VARCHAR(30),
    gender CHAR(1),
    seat INT ,
    age INT,
    countryId INT,
    CONSTRAINT pk_id PRIMARY KEY(id),#主键
    CONSTRAINT uq_uName UNIQUE(uName),#唯一键
    #在mysql中不支持CHECK检查约束，但是写了CHECK检查约束，语法也是不会报错的
    CONSTRAINT ck_gender CHECK(gender='男' OR gender='女'),#检查约束
    CONSTRAINT fk_myUser2_country FOREIGN KEY(countryId) REFERENCES country(id) #外键约束
);
#
DESC myUser2;
#
SHOW INDEX FROM myUser2;

#注意：可以不起约束名，会有一个默认的约束名
CREATE TABLE myUser3(
    id INT,
    uName VARCHAR(30),
    gender CHAR(1),
    seat INT ,
    age INT,
    countryId INT,
    #不起约束名，会有一个默认的约束名
    PRIMARY KEY(id),#主键
    UNIQUE(uName),#唯一键
    FOREIGN KEY(countryId) REFERENCES country(id) #外键约束
);

#
DESC myUser3;
#
SHOW INDEX FROM myUser3;

#
#通用的写法：
CREATE TABLE IF NOT EXISTS myUser4(
    id INT PRIMARY KEY,
    uName VARCHAR(20) NOT NULL,
    gender CHAR(1),
    age INT DEFAULT 20,
    seat INT UNIQUE,
    countryId INT,
    CONSTRAINT fk_myUser4_country FOREIGN KEY(countryId) REFERENCES country(id)
);
#
DESC myUser4;
#
SHOW INDEX FROM myUser4;

#主键是否允许组合，是允许的，但不推荐
#唯一键是否允许组合，是允许的，但不推荐
CREATE TABLE IF NOT EXISTS myUser5(
    id INT,
    uName VARCHAR(20),
    hobby VARCHAR(30),
    PRIMARY KEY(id,uName),#主键(2列组合)
    UNIQUE(uName,hobby)#唯一键(2列组合)
);
#
TRUNCATE myUser5
#
INSERT INTO myUser5 VALUES(1, 'jack', 'read');
INSERT INTO myUser5 VALUES(1, 'tom', 'read');
#
SELECT * FROM myUser5;

#列级约束可以给一个字段同时添加多个约束
CREATE TABLE IF NOT EXISTS myUser6(
    id INT,
    uName VARCHAR(20) NOT NULL UNIQUE, #既加非空约束，又加唯一约束，顺序无所谓
    seat VARCHAR(30) UNIQUE NOT NULL #既加非空约束，又加唯一约束，顺序无所谓
);

#
DESC myUser6;
#查看表的索引
SHOW INDEX FROM myUser6;
#查看表的key结构
SHOW KEYS FROM myUser6;

#
DROP TABLE myUser7;
#
DESC myUser7;
SHOW INDEX FROM myUser7;
SHOW KEYS FROM myUser7;
SELECT * FROM myUser7;
#
CREATE TABLE IF NOT EXISTS myUser7(
    id INT,
    uName VARCHAR(20),
    gender CHAR(1),
    age INT,
    seat INT,
    countryId INT
);
#二、修改表时添加约束

/*
1、添加列级约束
alter table 表名 modify column 字段名字段类型新约束;

2、添加表级约束
alter table 表名 add 【constraint 约束名】约束类型(字段名) 【外键的引用】;

*/
/*
修改seat列的列名，名字改成mySeat，改完列名后，再执
行SHOW INDEX FROM myUser7;或SHOW KEYS FROM myUser7;语句，查看一下Key_name是什
么，我们发现Key_name是原来的列名seat，而Column_name是新的列名mySeat，由
此可见Key_name和Column_name并不等价，他们可能是不一致的，这一点需要大家注意
*/
ALTER TABLE myUser7 CHANGE COLUMN seat mySeat INT;
#
DESC myUser7;
SHOW INDEX FROM myUser7;
SHOW KEYS FROM myUser7;
SELECT * FROM myUser7;
#

#1.添加非空约束
ALTER TABLE myUser7 MODIFY COLUMN uName VARCHAR(20) NOT NULL;
#如果我要把非空约束删了，或者说把非空约束改成可以为空(直接在类型后面加个NULL)
ALTER TABLE myUser7 MODIFY COLUMN uName VARCHAR(20) NULL;
#如果我要把非空约束删了，或者说把非空约束改成可以为空(直接在类型后面什么都不加，默认就是null)
ALTER TABLE myUser7 MODIFY COLUMN uName VARCHAR(20);
#可以给字段同时添加多个约束(同时添加非空约束和唯一约束)
ALTER TABLE myUser7 MODIFY COLUMN uName VARCHAR(20) NOT NULL UNIQUE;
ALTER TABLE myUser7 MODIFY COLUMN uName VARCHAR(20) NULL UNIQUE;
#

#2.添加默认约束
DESC myUser7;
ALTER TABLE myUser7 MODIFY COLUMN age INT DEFAULT 18;
#如果我要把默认约束删了，或者说把默认约束的默认值修改一下
ALTER TABLE myUser7 MODIFY COLUMN age INT;
ALTER TABLE myUser7 MODIFY COLUMN age INT DEFAULT 23;

#3.添加主键(2种写法)
DESC myUser7;
#(1)列级约束
ALTER TABLE myUser7 MODIFY COLUMN id INT PRIMARY KEY;
#(2)表级约束
ALTER TABLE myUser7 ADD PRIMARY KEY(id);
#添加主键约束时，给约束取名字
ALTER TABLE myUser7 ADD CONSTRAINT pk_myUser7_id PRIMARY KEY(id);

#4.添加唯一
DESC myUser7;
SHOW INDEX FROM myUser7;
#(1)列级约束
ALTER TABLE myUser7 MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE myUser7 MODIFY COLUMN seat INT UNIQUE NOT NULL;
ALTER TABLE myUser7 MODIFY COLUMN seat INT NOT NULL UNIQUE;
ALTER TABLE myUser7 MODIFY COLUMN seat INT UNIQUE NULL;
ALTER TABLE myUser7 MODIFY COLUMN seat INT NULL UNIQUE;
#(2)表级约束
ALTER TABLE myUser7 ADD UNIQUE(seat);
#添加唯一约束时，给约束取名字
ALTER TABLE myUser7 ADD CONSTRAINT uq_myUser7_seat UNIQUE(seat);

#
#5.添加外键
#添加约束时，省略约束名
ALTER TABLE myUser7 ADD FOREIGN KEY(countryId) REFERENCES country(id);
#添加约束时，给约束取名字
ALTER TABLE myUser7 ADD CONSTRAINT fk_myUser7_country FOREIGN KEY(countryId) REFERENCES country(id);

#三、修改表时删除约束

#1.删除非空约束
ALTER TABLE myUser7 MODIFY COLUMN uname VARCHAR(20) NULL;
#或者
ALTER TABLE myUser7 MODIFY COLUMN uname VARCHAR(20);

#2.删除默认约束
ALTER TABLE myUser7 MODIFY COLUMN age INT;

#3.删除主键
#这种写法没效果
ALTER TABLE myUser7 MODIFY COLUMN id INT;
#这种写法有效果
ALTER TABLE myUser7 DROP PRIMARY KEY;

#4.删除唯一(删除唯一约束的约束名)
SHOW INDEX FROM myUser7;
/*
删除该列的唯一约束其实就是删除该列的索引，即删除该列的Key_name，Key_name通
过SHOW INDEX FROM 表名;这条语句可以查出来
*/
ALTER TABLE myUser7 DROP INDEX seat;
ALTER TABLE myUser7 DROP INDEX uq_myUser7_seat;

#5.删除外键(删除外键约束的约束名)
/*
如下语句是没问题的，可是执行该语句却报错，在网
上查资料，说这是mysql的一个bug,我用的mysql版本是5.5.15
出现这种情况的具体原因我没有去深究
*/
ALTER TABLE myUser7 DROP FOREIGN KEY fk_myUser7_country;
#
SELECT VERSION();

#
DESC myUser7;
SHOW INDEX FROM myUser7;
SHOW INNODB STATUS;
SHOW CREATE TABLE myUser7;
#
CREATE TABLE myEmp2(
id INT,
ename VARCHAR(50)
);

CREATE TABLE myDept2(
    id INT,
    dname VARCHAR(30)
);
#
DESC myEmp2;
DESC myDept2;
SHOW INDEX FROM myEmp2;
SHOW INDEX FROM myDept2;
/*
列级约束：注意：列级约束不支持取名字，即列级约束无法取约束名
表级约束：注意：表级约束支持取名字，即表级约束可以取约束名
*/
#1.向表myEmp2的id列中添加PRIMARY KEY约束(my_emp_id_pk)
ALTER TABLE myEmp2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE myEmp2 ADD PRIMARY KEY(id);
ALTER TABLE myEmp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id);

#2.向表myDept2的id列中添加PRIMARY KEY约束(my_dept_id_pk)
ALTER TABLE myDept2 MODIFY COLUMN id INT PRIMARY KEY;
ALTER TABLE myDept2 ADD PRIMARY KEY(id);
ALTER TABLE myDept2 ADD CONSTRAINT my_dept_id_pk PRIMARY KEY(id);

#3.向表myEmp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是myDept2表中的id列。
ALTER TABLE myEmp2 ADD COLUMN dept_id INT;

ALTER TABLE myEmp2 ADD FOREIGN KEY(dept_id) REFERENCES myDept2(id);
ALTER TABLE myEmp2 ADD CONSTRAINT fk_myEmp2_myDept2 FOREIGN KEY(dept_id) REFERENCES myDept2(id);

#总结
#列级约束和表级约束的区别
#注意：在mysql中不支持CHECK检查约束
/*
        位置        支持的约束类型              是否可以起约束名
列级约束：    列的后面    语法都支持，但外键没有效果     不可以
表级约束：    所有列的下面    默认和非空不支持，其他支持     可以(但给主键取名字没有效果，主键约束名默认是PRIMARY)
*/

#
#创建表的时候写注释
CREATE TABLE userinfo(
    id INT COMMENT '编号',
    uname VARCHAR(40) COMMENT '用户名',
    address VARCHAR(120) COMMENT '家庭住址',
    hobby VARCHAR(200) COMMENT '爱好'

)COMMENT = '用户信息表';

#修改表的注释
ALTER TABLE userinfo COMMENT '用户信息资料表';

#修改字段的注释，注意：字段名和字段类型照写就行
ALTER TABLE userinfo MODIFY COLUMN uname VARCHAR(40) COMMENT '姓名';

#查看表注释的方法，在生成的SQL语句中看
SHOW CREATE TABLE userinfo;
#在元数据的表里面看
USE information_schema;
SELECT * FROM TABLES WHERE TABLE_SCHEMA='shoppingcart' AND TABLE_NAME='userinfo';

#查看字段注释的方法
SHOW FULL COLUMNS FROM userinfo;
#在元数据的表里面看
SELECT * FROM COLUMNS WHERE TABLE_SCHEMA='shoppingcart' AND TABLE_NAME='userinfo';