mariadb 在我的案例中可以有外键重复的值吗?

os8fio9y  于 2022-11-08  发布在  其他
关注(0)|答案(1)|浏览(118)

在这种情况下,可以有外键复制值?或者最好只对列有索引?
下面是我的表结构:

CREATE TABLE customers(
id INT (10) NOT NULL,
name  VARCHAR (50) NOT NULL,
city  VARCHAR (50) NOT NULL
);

CREATE TABLE orders(
cus_id INT (10) NOT NULL ,
order_date  DATETIME NOT NULL
);

CREATE TABLE products(
id INT (5) NOT NULL,
product_name  VARCHAR(50) NOT NULL,
product_price INT(10) NOT NULL
);

但是在orderitems表中(我已经存储了订购的产品,客户可以订购多个产品,因此可以复制外键值(cus_id))

CREATE TABLE ordered_items(
id INT (10) NOT NULL,
cus_id INT (10) NOT NULL,
product_id  INT(5) NOT NULL
);
ALTER TABLE customers ADD CONSTRAINT customer_id PRIMARY KEY ( id ) ;
ALTER TABLE orders ADD CONSTRAINT customers_id_fr FOREIGN KEY ( cus_id ) REFERENCES customers ( id );
ALTER TABLE ordered_items ADD CONSTRAINT ordered_items_fr FOREIGN KEY ( cus_id ) REFERENCES customers ( id );

编辑:对不起,ordered_items表也有一个唯一的ID列。

3zwtqj6y

3zwtqj6y1#

是的!您可以在ordered_items中有多个cus_id值。但是,如果您将orders替换为order_id,则可以更好地满足您的目的。因此,您的关系听起来像这样:

a**customer**can have multiple *orders*, 
    an**order**can have multiple *order items*,
    and an**order item**can only have single *product*

您的sql将如下所示

CREATE TABLE customers(
id INT (10) NOT NULL,
name  VARCHAR (50) NOT NULL,
city  VARCHAR (50) NOT NULL
);
ALTER TABLE customers ADD CONSTRAINT customer_id PRIMARY KEY ( id ) ;

CREATE TABLE orders(
id INT (5) NOT NULL,
cus_id INT (10) NOT NULL ,
order_date  DATETIME NOT NULL
);

ALTER TABLE orders ADD CONSTRAINT order_id PRIMARY KEY ( id ) ;
ALTER TABLE orders ADD CONSTRAINT customers_id_fr FOREIGN KEY ( cus_id ) REFERENCES customers ( id );

CREATE TABLE products(
id INT (5) NOT NULL,
product_name  VARCHAR(50) NOT NULL,
product_price DOUBLE NOT NULL
);

ALTER TABLE products ADD CONSTRAINT product_id PRIMARY KEY ( id ) ;

CREATE TABLE ordered_items(
id INT (10) NOT NULL,
order_id INT (10) NOT NULL,
product_id  INT(5) NOT NULL
);

ALTER TABLE ordered_items ADD CONSTRAINT ordrItm_id PRIMARY KEY ( id ) ;
ALTER TABLE ordered_items ADD CONSTRAINT ordrItm_order_frK FOREIGN KEY ( order_id ) REFERENCES orders ( id );
ALTER TABLE ordered_items ADD CONSTRAINT ordrItm_prd_frK FOREIGN KEY ( product_id) REFERENCES products ( id );

相关问题