mysql中外键的创建与删除
外键的创建
方法1:创建表的时候设置(外键名随机生成)
1.前提条件,必须要有一个主表,这里设为persons
2.主表中必须设置主键字段primary key,这里设为id_p
//创建数据库database(test)
create database if not exists test character set utf8;
//创建主表(persons)
create table if not exists persons(
id_p int not null,
lastName varchar(15) not null,
firstName varchar(15) not null,
address varchar(20) not null,
city varchar(15) not null,
primary key(id_p) //设置主键,这里必须要设置
);
//插入数据
![](https://images2017.cnblogs.com/blog/904182/201801/904182-20180130103847031-138759917.png)
//创建从表(orders)
create table if not exists orders(
id_o int not null,
orderNo int not null,
id_p int not null,
primary key(id_o),
foreign key(id_p) references persons(id_p) //设置外键,两个字段名id_p不需要一致,但是数据类型必须一致
);
//插入数据
![](https://images2017.cnblogs.com/blog/904182/201801/904182-20180130103859890-1668644559.png)
方法2:创建外键名的方式设置
alter table orders add constraint fk_id foreign key(id_p) references persons(id_p);
外键的删除
1.先查出它的外键
show create table orders;
![](https://images2017.cnblogs.com/blog/904182/201801/904182-20180130103219968-626749739.png)
2.删除外键
alter table orders drop foreign key order_ibfk_1;