java_sql约束

创建表加入约束(constraint)

/*约束在创建表的时候 可以给表的字段添加响应的约束 添加约束的目的是为了保证表中的数据的合法性 有效性 完整性
常见的约束有哪些呢？
非空约束（not null）约束的字段不能为NULL
唯一约束 （unique）约束的字段不能重复
主键约束 （ primary key）约束的字段既不能为NULL也不能重复
外键约束 （foreign key）
检查约束 （check）
注意oracal数据有check约束 目前mysql不支持
*/

1. not null(只有列级约束)

mysql> drop if exists t_mem;
mysql> create table t_mem(
id int,
memname varchar(255) not null,
password varchar(255)
);
mysql> desc t_mem;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int          | YES  |     | NULL    |       |
| memname  | varchar(255) | NO   |     | NULL    |       |
| password | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

//非空字段少值
mysql> insert into t_mem (id,password) values (1,'123');
结果打印：
ERROR 1364 (HY000): Field 'memname' doesn't have a default value

mysql> insert into t_mem (id,memname,password) values (1,'lisi','123');
mysql> select *from t_mem;
+------+---------+----------+
| id   | memname | password |
+------+---------+----------+
|    1 | lisi    | 123      |
+------+---------+----------+

2.唯一性约束(unique)(列级 表级约束)
唯一性约束修饰的字段具有唯一性 不能重复 但可以为NULL

//给一个字段添加unique
drop table if exists t_member;
create table t_member(
id int,
username varchar(255) unique
);
//插入数据
insert into t_member values  (1,'zhangsan');

//插入相同的username
insert into t_member values  (1,'zhangsan'),(2,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 't_member.username'

//可以同时为NULL
insert into t_member (id) values (2);
insert into t_member (id) values (3);
mysql> select *from t_member;
+------+----------+
| id   | username |
+------+----------+
|    2 | NULL     |
|    3 | NULL     |
|    1 | zhangsan |
+------+----------+

//给多个字段联合添加unique
//usercode,username联合(拼接)起来不能重复
create table t_member(
id int unique,
usercode varchar(255),
username varchar(255),
unique(usercode,username),
);
//给单独字段添加unique
// 单独字段内容不能重复
create table t_member(
id int unique,
usercode varchar(255) unique,
username varchar(255) unique,
);

3.主键约束

根据主键字段分类
1.单一主键
2.复合主键(多个字段联合起来添加主键约束)（复合主键不建议使用 因为复合主键违背了三范式）
3.业务主键 主键和系统的业务挂钩 例如:拿着银行卡的卡号做主键 拿着身份证号作为主键.(不推荐使用)
4.自然主键：主键值最好就是一个和业务没有关系的自然数(推荐)

//使用符合主键 不需要掌握
drop table if exists t_mem;
create table t_mem(
id int primary key,
username varchar(255),
email varchar(255),
primary key(id,username)
);

//使用表级约束定义主键
drop table if exists t_mem;
create table t_mem(
id int primary key,
username varchar(255),
email varchar(255),
primary key(id)
);
//使用列级约束定义主键
drop table if exists t_mem;
create table t_mem(
id int primary key,
username varchar(255),
email varchar(255)
);

insert into t_mem (id,username,email) values (1,'zs','zs@123');
insert into t_mem (id,username,email) values (2,'ls','ls@123');
insert into t_mem (id,username,email) values (3,'ww','ww@123');
mysql> select *from t_mem;
+----+----------+--------+
| id | username | email  |
+----+----------+--------+
|  1 | zs       | zs@123 |
|  2 | ls       | ls@123 |
|  3 | ww       | ww@123 |
+----+----------+--------+

//id为主键不能重复
mysql> insert into t_mem (id,username,email) values (1,'md','md@123');
ERROR 1062 (23000): Duplicate entry '1' for key 't_mem.PRIMARY'
//id为主键 不能为NULL
mysql> insert into t_mem (username,email) values ('md','md@123');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

//mysql提供主键值自增 (auto_increment)
drop table if exists t_mem;
create table t_mem(
id int primary key auto_increment,
username varchar(255)
);
insert into t_mem (username) values ('a');
insert into t_mem (username) values ('b');
insert into t_mem (username) values ('c');
insert into t_mem (username) values ('d');
mysql> select *from t_mem;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | c        |
|  4 | d        |
+----+----------+
4 rows in set (0.00 sec)

4.外键约束 foreign key

//班级表和学生表
t_class 班级表（父表）
cno(pk)          cname
---------------------------------------------
101              北京宜黄二中高三1班
102              北京宜黄二中高三2班  

t_student 学生表（子表）
sno(pk)      sname          classno(该字段添加外键约束fk)
----------------------------------------------------------
1             zs1                 101
2             zs2                 102
3             zs3                 101
4             zs4                 102

//将以上表的建表语句写出来
//foreign key(字段) references 父表(字段),
drop table if exists t_student;
create table t_class(
cno int primary key,
cname varchar(255)
);
create table t_student(
sno int primary key,
sname varchar(255),
classno int,
foreign key(classno) references t_class(cno)
);

insert into t_class values (101,'xxxxxxxxxxxx');
insert into t_class values (102,'yyyyyyyyyyyyy');

insert into t_student values(1,'zs',101);
insert into t_student values(2,'ff',102);
insert into t_student values(3,'ww',102);
insert into t_student values(4,'cc',101);

mysql> select *from t_class;
+-----+---------------+
| cno | cname         |
+-----+---------------+
| 101 | xxxxxxxxxxxx  |
| 102 | yyyyyyyyyyyyy |
+-----+---------------+
2 rows in set (0.00 sec)

mysql> select *from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | zs    |     101 |
|   2 | ff    |     102 |
|   3 | ww    |     102 |
|   4 | cc    |     101 |
+-----+-------+---------+
4 rows in set (0.00 sec)

insert into t_student values(5,'cc',105);
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`))