Can't add records from query in LibreOffice Base - Violation of unique index

Question

I am attempting to add records to a table which is linked to other tables that I don't want to modify. When I attempt to add a record to the table in question, an error message appears:

Error inserting the new record.
Violation of the unique index "<unique index>":
duplicate values for columns "xxx", "yyyy" in statement [INSERT INTO "table"("xxx","yyyy")VALUES(?,?)].

The error message is correct in that the table does have a unique index composed of two fields. However in the first instance I don't want records added to that table as it is only used as a lookup table for the main table which I am adding or modifying records.

The erroneous table does in fact have three keys. One primary, which is auto incremented, a second, unique key comprising two fields, and a third, foreign lookup key which is not unique.

I'm not sure what's going on under the hood here but I think it is to do with the index comprising two fields.

SELECT DISTINCT "Futures_Orders"."Date",
"Futures_Orders"."Symbol","Futures_Orders"."Contract_Mth",
"Futures_Orders"."Send_To_Broker",
"Futures_Orders"."Order-Id", 
"Futures_Orders"."Broker","Futures_Orders"."Order_Action",
"Futures_Orders"."No_Contracts",
"Futures_Orders"."Order_Price", 
"Futures_Orders"."Trade_Type",
CASE "Futures_Orders"."Order_Action" 
   WHEN 'b' THEN 's' 
ELSE 'b' 
END "If_Done_Action",
"Futures_Orders"."If_Done_Price", 
"Futures_Orders"."No_Contracts" * ( 
CASE "Contract_Details"."USIntRates" 
   WHEN 'n' THEN ABS( "Futures_Orders"."Order_Price"
      - "Futures_Orders"."If_Done_Price" ) / 
      "Contract_Details"."MinTick"
   ELSE ABS(ABS(TRUNCATE("Futures_Orders"."Order_Price",
       0)-TRUNCATE("Futures_Orders"."If_Done_Price",
          0))*"Contract_Details"."MinTick" -
       CASE 
          WHEN "Contract_Details"."Symbol" 
                   IN ( 'FV', 'TU' ) THEN 400 
          ELSE 200 
       END * 
       ABS("Futures_Orders"."Order_Price" -
         TRUNCATE("Futures_Orders"."Order_Price", 0)-
         "Futures_Orders"."If_Done_Price" + TRUNCATE(
         "Futures_Orders"."If_Done_Price", 0 )))
END 
* "Contract_Details"."MinTickVal" / "FX_Rates"."Rate" +
CASE
   WHEN "Broker_Commissions"."Commission_AUD" +
      "Broker_Commissions"."Commission" = 0
      THEN "Broker"."Commission"
   ELSE "Broker_Commissions"."Commission_AUD" +
      "Broker_Commissions"."Commission" / "FX_Rates"."Rate"
END
* 2) "Risk",
"Contract_Details"."Symbol",  "Contract_Details"."Currency",
"FX_Rates"."Code", "Broker"."Broker",
"Broker_Commissions"."Symbol", "Broker_Commissions"."Broker",
"Broker_Commissions"."ID"
FROM "Futures_Orders", "Contract_Details", "FX_Rates", "Broker",
"Broker_Commissions" WHERE "Futures_Orders"."Symbol" =
"Contract_Details"."Symbol" AND "Contract_Details"."Currency" =
"FX_Rates"."Code" AND "Futures_Orders"."Broker" = "Broker"."Broker"
AND "Futures_Orders"."Broker" = "Broker_Commissions"."Broker" AND
"Futures_Orders"."Symbol" = "Broker_Commissions"."Symbol"

How are you attempting to add records - through a form, directly into the table GUI, with an SQL statement? If you're using the GUI would you add more step-by-step what you are doing and if you're using an SQL statement would you share your exact INSERT statement? Also, by "linked to" what do you mean - what were the exact steps (SQL or in the Relationships GUI) you took to link the tables? — Lyrl, Oct 21 '15 at 19:22
The query adds records to Futures_Orders. However when I included the table Broker_Commissions it is attempting to add records to it as well. Broker_Commissions has a unique index comprising the fields Broker and Symbol. This is the index which prevents the record being added and produces the error message. — user1897830, Oct 21 '15 at 22:00

score 1 · Answer 1 · answered Oct 22 '15 at 05:18

The problem was in my keys and my select statement. I made a small change to the relationships and changed the last part of the select statement as follows and all is good. Thanks for your help. I just got a feeling that was the problem as I was reading something else.

FROM "Futures_Orders", "Contract_Details", "FX_Rates", "Broker",
"Broker_Commissions" WHERE "Futures_Orders"."Symbol" =
"Contract_Details"."Symbol" AND "Contract_Details"."Currency" =
"FX_Rates"."Code" AND "Futures_Orders"."Broker" = "Broker"."Broker"
AND "Broker"."Broker" = "Broker_Commissions"."Broker" AND
"Contract_Details"."Symbol" = "Broker_Commissions"."Symbol"

Can't add records from query in LibreOffice Base - Violation of unique index

1 Answers1