Mysql 外键约束

MySQL创建关联表可以理解为是两个表之间有个外键关系，但这两个表必须满足三个条件
1.两个表必须是InnoDB数据引擎
2.使用在外键关系的域必须为索引型(Index)
3.使用在外键关系的域必须与数据类型相似

create table s_user(
       u_id int auto_increment primary key,
       u_name varchar(15),
       u_pwd varchar(15),
       u_truename varchar(20),
        u_role varchar(6),
       u_email varchar(30)
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

insert into s_user values
       (1,"wangc","aaaaaa","wangchao","buyer","wang@163.com"),
      (2,"huangfp","bbbbbb","huangfp","seller","huang@126.com"),
      (3,"zhang3","cccccc","zhangsan","buyer","zhang@163.com"),
      (4,"li4","dddddd","lisi","seller","li@1256.com");

create table s_orderform(

          o_id int auto_increment primary key,
         o_buyer_id int,
         o_seller_id int,
         o_totalprices double,
         o_state varchar(50),
         o_information varchar(200),
         foreign key(o_buyer_id) references s_user(u_id),      #外链到s_user表的u_id字段
         foreign key(o_seller_id) references s_user(u_id)      #外链到s_user表的u_id字段
) ENGINE=INNODB DEFAULT CHARSET=utf8 ;

尝试加入外键不在主表中：

添加数据

insert into s_orderform values (4,1,1,12.3,"pending","1234567");
insert into s_orderform values (2,3,2,12.3,"pending","1234567");
insert into s_orderform values (3,4,1,12.3,"pending","1234567");

现在尝试删除主表s_user中数据

 

转载于:https://www.cnblogs.com/xiaoliwang/p/8863105.html