How do I avoid duplicates on an SQL primary key?

0
votes

Complete beginner here. I'm trying to create this simple joint table on SSMS but I'm getting this duplicate error regarding the primary key:

Msg 2627, Level 14, State 1, Line 23
Violation of PRIMARY KEY constraint 'PK__FactOffl__B14003C24ECE0589'. Cannot insert duplicate key in object 'dbo.FactOfflineSales'. The duplicate key value is (43659).

What am I doing wrong?

CREATE TABLE FactOfflineSales 
(
    SalesOrderID int NOT NULL PRIMARY KEY,
    SalesOrderNumber nvarchar(20) NOT NULL,
    SalesPersonID int NULL,
    CustomerID int NULL,
    SpecialOfferID int NOT NULL,
    TerritoryID int NOT NULL,
    ProductID int NOT NULL,
    CurrencyRateID int NULL,
    OrderQuantity smallint NULL,
    UnitPrice money NULL,
    SubTotal money NULL,
    TaxAmount money NULL,
    Freight money NULL,
    LineTotal money NULL,
    UnitPriceDiscount float NULL,
    OrderDate datetime NULL,
    ShipDate datetime NULL,
    DueDate datetime NULL,
    OnlineOrderFlag int NULL
);

INSERT INTO FactOfflineSales (
    SalesOrderID
   ,SalesOrderNumber
   ,SalesPersonID
   ,CustomerID
   ,SpecialOfferID
   ,TerritoryID
   ,ProductID
   ,CurrencyRateID
   ,OrderQuantity
   ,UnitPrice
   ,SubTotal
   ,TaxAmount
   ,Freight
   ,LineTotal
   ,UnitPriceDiscount
   ,OrderDate
   ,ShipDate
   ,DueDate
   ,OnlineOrderFlag
)

SELECT 
   SalesOrderHeader.SalesOrderID
   ,SalesOrderHeader.SalesOrderNumber
   ,SalesOrderHeader.SalesPersonID
   ,SalesOrderHeader.CustomerID
   ,SalesOrderDetail.SpecialOfferID
   ,SalesOrderHeader.TerritoryID
   ,SalesOrderDetail.ProductID
   ,SalesOrderHeader.CurrencyRateID
   ,SalesOrderDetail.OrderQty
   ,SalesOrderDetail.UnitPrice
   ,SalesOrderHeader.SubTotal
   ,SalesOrderHeader.TaxAmt
   ,SalesOrderHeader.Freight
   ,SalesOrderDetail.LineTotal
   ,SalesOrderDetail.UnitPriceDiscount 
   ,SalesOrderHeader.OrderDate
   ,SalesOrderHeader.ShipDate
   ,SalesOrderHeader.DueDate
   ,SalesOrderHeader.OnlineOrderFlag
FROM 
    AdventureWorks2019.Sales.SalesOrderHeader SalesOrderHeader
LEFT JOIN 
    AdventureWorks2019.Sales.SalesOrderDetail SalesOrderDetail ON SalesOrderHeader.SalesOrderID = SalesOrderDetail.SalesOrderID;
3
sql-serverduplicatesprimary-key
MSSQL tells you that you double records or you trying to add record with SalesOrderId=43659. Either such record already exists and you need to filter it or maybe LEFT JOIN with SalesOrderDetail generate duplicates on SalesOrderId. – Alex Yu
I assume you must first pay attention to the result of the query you make against SalesOrderHeader and SalesOrderDetail. There should be no repeated SalesOrderID values. – Fabio Mendes Soares
Your connection should determine the database context - don't use 3 part names without a very good reason. Next, give your tables short (but not cryptic) aliases. Save your fingers - you will be typing for a very long time. – SMor
And your query and your new table simply do not match logically. It is time to back up and think about (explain) what you are trying to accomplish. Without knowing that, you might get this "working" only to move down a path that will end up in a location not of your choosing. As already noted, there is a 1:m relationship between header and detail, so your attempt to use the PK of the header row as your table's primary key simply will not work. – SMor

3 Answers

0
votes

You can not insert the duplicate value in primary key column.

You table FactOfflineSales has primary key on column SalesOrderID which will hold not null and unique values. But looking at your query it seems like there is one-to-many relationship between salesorderheader and salesorderdetails table. so your select query is giving multiple records for single salesorderid, means multiple records with same salesorderid.

It is not allowed to insert duplicate values in FactOfflineSales.salesorderid. Hence, it is throwing an error.

Best practice is to use auto generated sequence for primary key column. You should add one more column in table and declare it as primary key.(something like FactOfflineSales_id)

0
votes

Your LEFT JOIN on SalesOrderDetail is mutiplying out rows from SalesOrderHeader, from which the primary key comes.

You need to add SalesOrderDetailID to the table, with that (or that and SalesOrderID together) as the primary key

0
votes

There is a lot going on here. Let me unpack.

  1. A join between tables helps when there is many to many. What does this mean? It means a header has many detail records and a detail record can belong to many headers. If this is not your case, you don't need a join table.
  2. If you really need a join table, you normally do one of two things. The first would be create a composite key on the IDs from both tables. This would only be unique more than once if you load the table with a query that has dupes (solved with DISTINCT). The second (other option) is create a derived key for the primary key (like IDENTITY) and then have the two fields.

As you are learning, neither apply, but you can play with the queries to learn.

  • See if adding a distinct in the SELECT eliminates dupes. I don't think this will work, but DISTINCT is good to learn.
  • When the above fails, add a derived key on the table and then rerun your query. You do this by creating this join table with an Id field that is IDENTITY(1,1) for the PRIMARY KEY

As already mentioned by others, your issue is you have dupes on the primary key, which is sales order Id. Why dupes? Because each header has multiple detail records. On record 2, it will fail, as you had the header's id as your primary key.