sql server - How do I avoid duplicates on an SQL primary key?

Question

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;

Answer 1

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)