MySQL中的约束

MySQL中的约束。

MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；

约束主要完成对数据的检验，保证数据库数据的完整性；如果有相互依赖数据，保证该数据不被删除。

常用五类约束：
not null：非空约束，指定某列不为空
unique： 唯一约束，指定某列和几列组合的数据不能重复
primary key：主键约束，指定某列的数据不能重复、唯一
foreign key：外键，指定该列记录属于主表中的一条记录，参照另一条数据
check：检查，指定一个表达式，用于检验指定数据
注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果；
根据约束数据列限制，约束可分为：
单列约束：每个约束只约束一列
多列约束：每个约束约束多列数据

1、not null

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
Null类型特征： 所有的类型的值都可以是null，包括int、float等数据类型 空字符串””是不等于null，0也不等于null

CREATE TABLE temp(        
	s_id INT NOT NULL,      
	s_name VARCHAR(2) NOT NULL DEFAULT 'abc',      
	s_sex CHAR(2) NULL
)

/*
上面的table加上了非空约束，也可以用alter来修改或增加非空约束
增加非空约束
*/
ALTER TABLE temp MODIFY s_sex VARCHAR(2) NOT NULL;  

#取消非空约束
ALTER TABLE temp MODIFY s_sex VARCHAR(2) NULL;  

#取消非空约束，增加默认值
ALTER TABLE temp MODIFY s_sex VARCHAR(4) DEFAULT 'abc';#默认值的大小应该小于数据类型给定的大小范围 

2、unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。虽然唯一约束不允许出现重复的值，但是可以为多个null，同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。 MySQL会给唯一约束的列上默认创建一个唯一索引；

CREATE TABLE temp (        
	s_id INT NOT NULL,        
	s_name VARCHAR(25),        
	s_password VARCHAR(16),
	CONSTRAINT uk_name_pwd UNIQUE(s_name, s_password)
);

#表示用户名和密码组合不能重复
#添加唯一约束
ALTER TABLE temp ADD UNIQUE(s_name, s_password);
INSERT INTO temp VALUE(23, 'abc', '666');
INSERT INTO temp VALUE(31, 'abc', '666');	#这行肯定报错，违反了唯一性
INSERT INTO temp VALUE(88, 'abc', '678');	#这条语句正确插入表中
INSERT INTO temp VALUE(66, 'xyz', '666');	#同上，正常运行
SELECT * FROM temp;

#修改唯一性约束
#DELETE from temp where s_id = 23;	#修改某列为唯一约束时，该列的数据中不能有重复值
ALTER TABLE temp MODIFY s_name VARCHAR(25) UNIQUE;	#单独给该列增加约束，组合约束依然存在

#删除约束
ALTER TABLE temp DROP INDEX s_name;

3、primary key

主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值；如果的多列组合的主键约束， 那么这些列都不允许为空值，并且组合的值不允许重复。 每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。
MySQL的主键名总是PRIMARY， 当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
列模式：

DROP TABLE temp;
CREATE TABLE temp(    
	s_id INT PRIMARY KEY,    
	s_name VARCHAR(25)
);  
CREATE TABLE temp1(    
	t_id INT NOT NULL,    
	t_name VARCHAR(25),    
	t_pwd VARCHAR(15),    
	CONSTRAINT pk_temp_id PRIMARY KEY(t_id)		#写法二
);  
#组合模式：
CREATE TABLE temp2(    
	p_id INT NOT NULL,    
	p_name VARCHAR(25),    
	p_pwd VARCHAR(15),    
	CONSTRAINT pk_temp_id PRIMARY KEY(p_name, p_pwd)
);

#alter删除主键约束
ALTER TABLE temp1 DROP PRIMARY KEY;  

#alter添加主键
ALTER TABLE temp1 ADD PRIMARY KEY(t_name, t_pwd);#如果其中某个列已经定义为主键，无法执行该语句，因为一个表只能有一个主键
INSERT INTO temp1 VALUE(2, 'abc', '123');
INSERT INTO temp1 VALUE(2, 'abc', '123');#报错
INSERT INTO temp1 VALUE(2, 'abc', '456');#t_name相同，但是该行数据可以插入
INSERT INTO temp1 VALUE(2, NULL, '123');#报错，说 t_name 不能为 null
INSERT INTO temp1(t_id, t_pwd) VALUE(2, '123');#该行数据确实插入，且 t_name 查询出来确实什么都没有
INSERT INTO temp1(t_id) VALUE(2);	#该行也能插入
INSERT INTO temp(s_name) VALUES('xxx');	#有警告，说 s_id 没有默认值，查询出来的 s_id 是0
INSERT INTO temp(s_name) VALUE('aaa');	#错误，说 0 重复

SELECT * FROM temp1;
SELECT * FROM temp;

#alter修改列为主键
ALTER TABLE temp MODIFY s_id INT PRIMARY KEY;  

#设置主键自增
CREATE TABLE temp3(        
m_id INT AUTO_INCREMENT PRIMARY KEY,        
m_name VARCHAR(20),        
m_pwd VARCHAR(16)
);

#auto_increment自增模式，设置自增后在插入数据的时候就不需要给该列插入值了。
SELECT * FROM temp3;
INSERT INTO temp3 VALUE(10,'xx','666');
INSERT INTO temp3 VALUES(m_id,'aaa', '123'),(m_id,'abc', '111');#不用写值，但是需要写入列名，m_id 的值会接着上一条的记录往下递增，每次加 1

4、foreign key

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。 也就是说从表的外键值必须在主表中能找到或者为空。 当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据， 然后才可以删除主表的数据。还有一种就是级联删除子表数据。
注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列，假定引用的主表列不是唯一的记录， 那么从表引用的数据就不确定记录的位置。同一个表可以有多个外键约束。

#创建外键约束：
#主表
CREATE TABLE classes(        
	c_id INT AUTO_INCREMENT PRIMARY KEY,        
	c_name VARCHAR(20)
);

#从表
CREATE TABLE student(
	s_id INT AUTO_INCREMENT,        
	s_name VARCHAR(22),        
	CONSTRAINT pk_id PRIMARY KEY(s_id),        
	s_classes_id INT REFERENCES classes(c_id)	#外键写法一  
);

#通常先建主表，然后再建从表，这样从表的参照引用的表才存在。
#表级别创建外键约束：
CREATE TABLE student(        
	s_id INT AUTO_INCREMENT PRIMARY KEY,        
	s_name VARCHAR(25),                       
	s_classes_id INT,        
	FOREIGN KEY(s_classes_id) REFERENCES classes(c_id)	#外键写法二
);

/*上面的创建外键的方法没有指定约束名称，系统会默认给外键约束分配外键约束名称，命名为student_ibfk_n，
 其中student是表名，n是当前约束从1开始的整数。  */
#指定约束名称：
CREATE TABLE student(        
	s_id INT AUTO_INCREMENT PRIMARY KEY,        
	s_name VARCHAR(25),        
	s_classes_id INT,               
	CONSTRAINT fk_classes_id FOREIGN KEY(s_classes_id) REFERENCES classes(c_id) #外键写法三
);

#多列外键组合，必须用表级别约束语法：
CREATE TABLE classes(        
	c_id INT,        
	c_name VARCHAR(20),        
	c_number INT,   
	PRIMARY KEY(c_name, c_number)	#外键要两个字段，自然应该要两个字段做主键
);

CREATE TABLE student(        
	s_id INT AUTO_INCREMENT PRIMARY KEY,        
	s_name VARCHAR(20),        
	s_classes_name VARCHAR(20),        
	s_classes_number INT,               
	FOREIGN KEY(s_classes_name, s_classes_number) REFERENCES classes(c_name, c_number)
	#只能连接主表的符合主键，主表的两个unique约束也不能被复合外键连接
);
  
#删除外键约束：(使用外键名删除外键约束)
ALTER TABLE student DROP FOREIGN KEY student_ibfk_1;
ALTER TABLE student DROP FOREIGN KEY fk_student_id;  

#增加外键约束：
ALTER TABLE student ADD FOREIGN KEY(s_classes_name, s_classes_number) REFERENCES classes(c_name, c_number); 
 
#自引用、自关联（递归表、树状表）
CREATE TABLE tree(        
	t_id INT AUTO_INCREMENT PRIMARY KEY,        
	t_name VARCHAR(50),        
	parent_id INT,        
	FOREIGN KEY(parent_id) REFERENCES tree(t_id)	#一个列通过外键连接同一表的另一列名；
);  

/*
级联删除：删除主表的数据时，关联的从表数据也删除，则需要在建立外键约束的后面增加on delete cascade 或on delete set null，
前者是级联删除，后者是将从表的关联列的值设置为null。同 Oracle
*/
CREATE TABLE student(        
	s_id INT AUTO_INCREMENT PRIMARY KEY,        
	s_name VARCHAR(20),        
	s_classes_name VARCHAR(20),        
	s_classes_number INT,         /*表级别联合外键*/        
	FOREIGN KEY(s_classes_name, s_classes_number) REFERENCES classes(c_name, c_number) ON DELETE CASCADE
);

5、check

CREATE TABLE temp(        
	s_id INT AUTO_INCREMENT,        
	s_name VARCHAR(20),        
	s_age INT,        
	PRIMARY KEY(s_id),
	CHECK(s_age > 20)
);

INSERT INTO temp VALUES(8, 'xxx', 18),(12, 'yyy', 34);#两行数据都正确插入

SELECT * FROM temp;