表的约束

表的约束

为什么需要表的约束

// 唯一约束 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);