Sorry if the title is unclear.

As of right now, I just have a spreadsheet of a bunch of customers and orders. For example, a line in the spreadsheet might look like:

A Customer with an ID of 1 with name Sally and address 291 North Street bought item id 2.

The actual spreadsheet looks something like this table:

My goal is to properly normalize this data so it's not as redundant. I've already separated the data into 3 tables, Items, Orders, and OrderInfo.

Items follows a structure like so:

Orders:

OrderInfo:

As you can see from the orders table, I tried to combine all redundant orders where say user Sally ordered a long sleeves shirt and a regular shirt in the same order. However, this leaves redundant data in the OrdersInfo table, where the OrderId is the same for multiple fields because the customer bought multiple items in one order.

Is this correct? I am trying to define relationships on the tables in LibreOffice Base, and I can define the correct one-to-many relationships for all of them except for OrderInfo and Orders.

Here's a screenshot of the relations and the error when I try to link the OrderID field.

Error code:

SQL Status: S0011
Error code: -170

Primary or unique constraint required on main table: "Orders" in statement [ALTER TABLE "Order_Info" ADD  FOREIGN KEY ("order_id") REFERENCES "Orders" ("order_id")]

CodePudding user response：

A foreign key must reference the primary key (or unique key) of the referenced table. You will get the error you show if no such primary/unique key is defined.

Example:

CREATE TABLE Orders (
  order_id INT NOT NULL,
  customer_id INT NOT NULL,
  PRIMARY KEY(order_id),            <-- this is probably not defined
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

CREATE TABLE Order_info (
  order_id INT NOT NULL,
  item_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (order_id, item_id),
  FOREIGN KEY (order_id) REFERENCES Orders(order_id),
  FOREIGN KEY (item_id) REFERENCES Items(item_id)
);