如何在MySQL中创建具有N:M关系的表? [英] How to create tables with N:M relationship in MySQL?
本文介绍了如何在MySQL中创建具有N:M关系的表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
比方说,我有两个表:商店和产品.我希望我的商店有产品清单.我该怎么办?
Let's say I have this two tables: Store and Product. I want my store to have a list of products. How can I do that?
create table store(
id int unsigned not null auto_increment,
store_name varchar(30) not null,
product_list_FK int unsigned not null,
primary key(id)
);
create table product(
id int unsigned not null auto_increment,
product_name varchar(30) not null,
price float not null,
primary key(id)
);
我开始了类似的事情,但是我不知道该如何完成,你们能帮我吗?
I started something like that, but I don't know how to finish, can you guys help me?
推荐答案
多对一(产品只能有一家商店)
Many-to-one (products can only have one store)
create table store(
id int unsigned not null auto_increment,
store_name varchar(30) not null,
primary key(id)
);
Query OK, 0 rows affected (0.02 sec)
create table product(
id int unsigned not null auto_increment,
store_id int unsigned not null,
product_name varchar(30) not null,
price float not null,
primary key(id),
constraint product_store foreign key (store_id) references store(id)
);
Query OK, 0 rows affected (0.02 sec)
多对多(产品可以在许多商店中购买)
Many-to-many (products can be in many stores)
create table store(
id int unsigned not null auto_increment,
store_name varchar(30) not null,
primary key(id)
);
Query OK, 0 rows affected (0.04 sec)
create table product(
id int unsigned not null auto_increment,
store_id int unsigned not null,
product_name varchar(30) not null,
price float not null,
primary key(id)
);
Query OK, 0 rows affected (0.01 sec)
create table product_store (
product_id int unsigned not null,
store_id int unsigned not null,
CONSTRAINT product_store_store foreign key (store_id) references store(id),
CONSTRAINT product_store_product foreign key (product_id) references product(id),
CONSTRAINT product_store_unique UNIQUE (product_id, store_id)
)
Query OK, 0 rows affected (0.02 sec)
这篇关于如何在MySQL中创建具有N:M关系的表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文