如何在MySQL中创建具有N:M关系的表?

假设我有两个表:Store和Product。我希望我的商店有产品清单。我怎样才能做到这一点?

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)

);

我开始这样的事情,但是我不知道该如何完成,你们能帮我吗?

回答:

多对一(产品只能有一家商店)

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)

多对多(产品可以在许多商店中使用)

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关系的表? 的全部内容, 来源链接: utcz.com/qa/400736.html

回到顶部