表的约束

shamusoft

表的约束
为什么需要表的约束
// 唯一约束 unique
drop table if exists a;
create table a
(
       name varchar(20) unique,
       age varchar(20)
);
insert into a values('aaa','28');
insert into a(age) values(23);
 
// 非空约束
drop table if exists b;
create table b
(
       name varchar(20) not null,
       age varchar(20)
);
 
insert into b(name,age) values('aaa','23');
 
insert into b(name) values('bbb');
 
// 为了方便对表中的数据进行查询，通过会为每个记录指定一个id
// id 不能为空  唯一
// 主键约束 primary key   相当于 非空约束加唯一约束
create table c
(
       id int primary key,
       name varchar(20)
);
 
insert into c (name) values('aaa');   // id 不能为空
insert into c (id,name) values(1,'aaa');
insert into c (id,name) values(1,'bbb');  // 主键id必须唯一
 
// 删除主键约束
alter table c drop primary key;
// 增加主键约束
alter table c add primary key(id);
// 建立联合主键
create table d
(
       firstname varchar(20),
       lastname varchar(20),
       primary key(firstname,lastname)
);
 
insert into d (firstname,lastname) values('aaa','bbb');
insert into d (firstname,lastname) values('aaa','ccc');
insert into d (firstname,lastname) values('aaa','bbb');           // 联合主键所有主键都不能为null，唯一性是针对所有主键来检查的
 
// 设置主键的自动增长 
// 缺点： 记录删除了，主键在原来的基础上继续增长 ； 不便于我们在程序中获得主键
create table e
(
       id int primary key auto_increment,
       name varchar(20)
);
 
insert into e (name) values('aaa');
 
insert into e (name) values('bbb');
 
delete from e where id=3;
 
insert into e (name) values('bbb');
 
// 手工插入id为2的记录
insert into e (id,name) values(2,'eeee');
 
// 外键约束
 
// 创建一张Person表
create table person
(
       id int primary key auto_increment,
       name varchar(20)
);
insert into person(name) values('zhangsan');
insert into person(name) values('wangwu');
 
drop table orders;
create table orders
(
       id int primary key auto_increment,
       name varchar(20)
);
 
// 增加一列   每个订单有个订单人
alter table orders add personid int;
 
insert into orders(name,personid) values('book',1);
insert into orders(name,personid) values('car',2);
 
// 问题1： 假如zhangsan离职了 这人没了
delete from person where id=1;
 
// 查看一下book这个商品是谁下的订单
// 查出商品对应的personid
select personid from orders where name='book';
// result : 1
// 拿着 personid=1 去查 person表 查出是谁
select name from person where id=1;    // 查无此人
 
// 问题2 ： 有可能输入订单的时候会误操作   填写一个不存在的订单人id
insert into orders(name,personid) values('ship',4);
 
// 要解决上述的两个问题，就需要加外键约束 // 这一列是需要参照 person 表的 id 需要加外键约束
// 创建 orders 表
drop table orders;
create table orders
(
       id int primary key auto_increment,
       name varchar(20),
       personid int,   
       constraint  personid_FK foreign key(personid) references person(id)
);
 
insert into orders(name,personid) values('book', 1);
insert into orders(name,personid) values('car',2);
 
// zhangsan离职了 删除这个人
delete from person where name='zhangsan';
删不掉的原因： 记录被另外一张表所参照
对于增删改操作而言，我们都需要考虑参照完整性的问题
 
// 删除外键约束
alter table orders drop foreign key personid_FK;
insert into orders(name,personid) values('ship',3);
 
// 增加外键约束
// 加外键之前要把参照不完整的数据去除
delete from orders where personid=3;
alter table orders add foreign key(personid) references person(id);