My sql 的约束

1. not null 非空约束
// name 不能为空，如果数据插入的时候name为空会报错
create table t_user(
    id int(10),
    name varchar(32) not null,  // 列级约束
    email varchar(128)
    // not null(name) 加到这里是表级约束
);
insert into t_user(id,name,email) values (1,'张三','123@jd.com');
+------+--------+------------+
| id   | name   | email      |
+------+--------+------------+
|    1 | 张三   | 123@jd.com |
+------+--------+------------+
// name设置不能为空，如果插入的是时候name为空会报这种错误
insert into t_user(1,email)values(1,'135@jd.com');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1,email)values(1,'135@jd.com')' at line 1

2. unique 唯一约束
create table t_user(
    id int(10),
    name varchar(32) not null,   // 列级约束
    email varchar(128) unique  // 列级约束
   // unique(email) 表级约束
);
// 给邮箱加上unique 表示在插入数据的时候邮箱不能重复
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | YES  |     | NULL    |       |
| name  | varchar(32)  | NO   |     | NULL    |       |
| email | varchar(128) | YES  | UNI | NULL    |       |
+-------+--------------+------+-----+---------+-------+

3. 主建约束 primary key 简称pk
        主建涉及的术语
                主建约束
                主建字段
                主建值
给字段添加主建约束后,不能为空,并且不能重复
一张表中应该有主建,如果没有主建表示这张表是无效的
主建值：是当前行数据的唯一标示

/// 添加主建
drop table if exists t_user;
create table t_user(
    id int(10) primary key,
    name varchar(32) ,
    email varchar(128)
);
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   | PRI | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| email | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
mysql 中提供了一个自增数字，主建的值是自动增长，不需要用户维护
auto_increment  ， 自动增长的值只能使用一次

drop table if exists t_user;
create table t_user(
    id int(10) primary key auto_increment,
    name varchar(32) ,
    email varchar(128)
);
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(10)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32)  | YES  |     | NULL    |                |
| email | varchar(128) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

4. foreign key 外建约束 简称fk
      主建涉及的术语
                外建约束
                外建字段
                外建值
 foreign key 可以把两张表联系起来
两张表设置外建
drop table if exists t_student;
drop table if exists t_calss;
create table t_calss(
    cno int(3) primary key,
    cname varchar(120) not null unique
);
create table t_student(
    sno int(3) primary key,
    sname varchar(32) not null,
    calssno int(3),
    foreign key(calssno) references t_calss(cno)
);
insert into t_calss (cno,cname) values(100,'1班');
insert into t_calss (cno,cname) values(200,'2班');
insert into t_calss (cno,cname) values(300,'3班');
insert into t_student(sno,sname,calssno) values(1,'李四',100);
insert into t_student(sno,sname,calssno) values(2,'王五',200);
insert into t_student(sno,sname,calssno) values(3,'赵六',300);
select * from t_student;
+-----+--------+---------+
| sno | sname  | calssno |
+-----+--------+---------+
|   1 | 李四   |     100 |
|   2 | 王五   |     200 |
|   3 | 赵六   |     300 |
+-----+--------+---------+
select * from t_calss;
+-----+-------+
| cno | cname |
+-----+-------+
| 100 | 1班   |
| 200 | 2班   |
| 300 | 3班   |
+-----+-------+
// 下面这行添加失败t_calss cno 里面 500 不存在
insert into t_student(sno,sname,calssno) values(6,'李四',500);