1.MYISAM不支持外键约束,若需要添加外键约束,需设置engine=innodb
2.设置外键约束后,若此时在order_items添加一条关于orderid的数据,而此orderid在order中不存在,会提示can not add or update a row
[外键约束:即一个订单号不存在,那么订单里对应的物品也必然不在]
3若想支持级联删除或更新(即删除/更新订单里的所有物品 ,如delete from orders where orderid=1 ,会自动删除管理的物品):alter table order_items drop foreign key fk_1
alter table order_items add constraint fk_1 foreign key(orderid) references orders(orderid) on delete cascade on update cascade
完整示例如下:
//一对多
create table orders(
orderid int not null auto_increment,
price float(6,2),
primary key(orderid)
)engine=InnoDB;
insert into orders(price) values('100');
create table order_items(
orderid int not null,
isbn int(13) not null,
num int not null,
primary key(orderid,isbn),
constraint fk_1 foreign key (orderid) references orders(orderid) on delete cascade on update cascade
)engine=InnoDB;
insert into order_items(orderid,isbn,num) values(1,'123456',2);
//多对多
create table customers(
customerid int not null auto_increment,
address varchar(10),
primary key(customerid)
)engine=innodb;
insert into customers(address) values('shanghai');
create table order_customer(
orderid int not null,
customerid int not null,
primary key(orderid,customerid),
constraint c1 foreign key(orderid) references orders(orderid),
constraint c2 foreign key(customerid) references customers(customerid)
)engine=innodb;
补充说明:
约束种类:
1.NOT NULL
2.PRIMARY KEY
3.FOREIGN KEY
4.CHECK CHECK(price >10)
5.DEFAULT DEFAULT price ‘10’