mysql--约束

骑猪看日落 2022-10-23 08:28 158阅读 0赞

约束

1.  什么是约束?常见的约束有哪些?  
    约束是为了保证数据的完整性,合法性,有效性.
    
    非空约束 (not null)  
    唯一约束 (unique)  
    主键约束 (primary key, PK) 既不能为null也不能重复  
    外键约束 (foreign key, FK) …  
    检查约束 (check) :注意Oracle数据库中有check约束,但mysql没有,目前mysql不支持该约束
2.  非空约束  
    mysql> drop table if exists t\_user;  
    mysql> create table t\_user (  
    \-> username varchar(255) not null,  
    \-> password varchar(255)  
    \-> );  
    mysql> insert into t\_user(username, password) values(‘lancoff’, “1234”)  
    \-> ;  
    Query OK, 1 row affected (0.05 sec)
    
    mysql> select \* from t\_user;  
    ±---------±---------+  
    | username | password |  
    ±---------±---------+  
    | lancoff | 1234 |  
    ±---------±---------+
3.  唯一性约束(unique)  
    不能重复，但能为NULL。  
    drop table if exists t\_user;  
    create table t\_user(  
    id int,  
    username varchar(255) unique //列级约束  
    );
    
    insert into t\_user(id, username) values(1, ‘zhangsan’);  
    insert into t\_user(id, username) values(2, ‘zhangsan’);
    
    ERROR 1062 (23000): Duplicate entry ‘zhangsan’ for key ‘t\_user.username’
    
    案例:给两个列或者多个列添加unique  
    drop table if exists t\_user;
    
    create table t\_user(  
    id int,  
    usercode varchar(255),  
    username varchar(255),  
    unique(usercode, username) //表级约束  
    );
    
    //unique(usercode, username) 两个属性不能同时重复,某一个重复是允许的.  
    列级约束可以写成表级约束,unique()括号里只写一个字段名即可
    
    非空约束(not null)只有列级约束,没有表级约束
4.  主键约束  
    一张表的主键约束只能有一个
    
    怎么给一张表添加主键约束?  
    drop table if exists t\_user;  
    create table t\_user (  
    id int primary key,  
    username varchar(255),  
    email varchar(255)  
    );  
    insert into t\_user(id, username, email) values(1, ‘zs’, ‘zs@123.com’);  
    insert into t\_user(id, username, email) values(2, ‘ls’, ‘ls@123.com’);  
    insert into t\_user(id, username, email) values(3, ‘ww’, ‘ww@123.com’);
    
    主键相关的术语:  
    主键约束  
    主键字段  
    主键值
    
    主键有什么用?  
    表的设计三范式中要求:第一范式就是要求任何一张表都应该有主键.  
    主键是一条记录的唯一标识.
    
    主键的分类:  
    根据主键字段的字段数量来划分  
    单一主键(推荐)  
    复合主键(不建议使用,违背了三范式)  
    根据主键性质来划分:  
    自然主键  
    业务逐渐(主键值和系统的业务挂钩,例如:拿银行卡号做主键. 不建议这么使用)
    
        注意:最好不要拿着和业务挂钩的字段作为主键.因为业务一旦发生改变的时候,主键值可能也需要修改,因为主键约束的存在,修改主键值往往造成值的重复.
    
    使用表级的方式定义主键约束:  
    drop table if exists t\_user;  
    create table t\_user(  
    id int,  
    username varchar(254),  
    email varchar(255),  
    primary key(id)  
    );
    
    mysql提供主键值自增:  
    drop table if exists t\_user;
    
        create table t_user(
         	id int primary key auto_increment,
         	username varchar(255),
         	email varchar(255)
         );
        
        
         insert into t_user(username) values('a');
         insert into t_user(username) values('b');
         insert into t_user(username) values('c');
         insert into t_user(username) values('d');
         
        提示:Oracle当中也提供了一些自增机制,叫做:序列(sequence)
5.  外键约束  
    相关术语:  
    外键字段  
    外键约束  
    外键值
    
    业务背景:  
    请设计数据库表,用来维护学生和班级的信息:
    
        第一种方案:一张表存储所有数据
         	no(pk) 		name		classno		classname
         	--------------------------------------------------
         	1			zs1			101			实验高中高二1班
         	2			zs2			101			实验高中高二1班
         	3			zs3			102			实验高中高二2班
         	4			zs4			102			实验高中高二2班
         	5			zs5			102			实验高中高二2班
         	
         	缺点:冗余, 教室名字多次重复
        
         	第二种方案:两张表(班级表,学生表)
         	t_class
         	cno(pk) 		cname
         	------------------------------
         	101				实验高中高二1班
         	102				实验高中高二2班
        
         	t_student
         	no(pk) 		name		classno(fk)
         	---------------------------------
         	1			zs1			101			
         	2			zs2			101			
         	3			zs3			102			
         	4			zs4			102			
         	5			zs5			102	
        
         	t_student表中的数据的cno值只能取t_class表中已经出现的cno值,否则报错
        
         	将以上的建表语句写出来:
         	t_student中的classno字段引用t_class表中的cno字段,此时t_student表被称为子表,t_class表被叫做父表.
         	
         	先创建父表在创建子表,删表的时候先删子表再删父表, 因为子表需要使用父表的字段.(理解记忆)
        
         	drop table if exists t_student;
         	drop table if exists t_class;
        
         	create table t_class (
         		cno int, 
         		cname varchar(255),
         		primary key(cno)
         	);
        
         	create table t_student (
         		no int primary key,
         		name varchar(255),
         		classno int,
         		foreign key(classno) references t_class(cno) 
         	);
        
         	insert into t_class values(101, 'xxxxxxxx');
         	insert into t_class values(102, 'yyyyyyyy');
        
         	insert into t_student values(1, 'zs1', 101);
         	insert into t_student values(2, 'zs2', 101);
         	insert into t_student values(3, 'zs3', 102);
         	insert into t_student values(4, 'zs4', 102);
         	insert into t_student values(5, 'zs5', 102);
         	insert into t_student values(6, 'zs6', 102);
         	
         	select * from t_class;
         	select * from t_student;
        
         	insert into t_student values(7, 'zs6', 103);
         	ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.`t_student`, CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
        
         外键值可以为NULL吗?
         insert into t_student(no, name) values(8, 'zs8');
         select * from t_student;
         +----+------+---------+
         | no | name | classno |
         +----+------+---------+
         |  1 | zs1  |     101 |
         |  2 | zs2  |     101 |
         |  3 | zs3  |     102 |
         |  4 | zs4  |     102 |
         |  5 | zs5  |     102 |
         |  6 | zs6  |     102 |
         |  8 | zs8  |    NULL |
         +----+------+---------+
        
         外键字段引用其他表的字段的时候,被引用的字段必须是主键吗?
         	不是,但别的字段必须保证有唯一性.