Can have in my case foreign key duplicated value?-CodePudding

can have in this case foreign key duplicated value? Or better just have an index on column?

Here is my table structure:

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
);

But in orderitems table (Where I have stored the ordered products, the customer can have multiple products ordered so the foreign key value (cus_id) can be duplicated)

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 );

EDIT: Sorry the ordered_items table have a unique ID column as well.

CodePudding user response：

Yes! you can have multiple value of cus_id inside ordered_items. however, your intention is better served if you replace cus_id by order_id from orders, Thus your relationship would sound like,


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

your sql would look like this


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 );