title: How do I not violate this primary key when syncing with Azure?
body: <h2>Project summary</h2>
  
  <p>We're creating a 'digital' kitchen where it's possible to add which items are in your fridge, freezer etc. Then it's possible to view these items on a webapplication, so you know groceries you have in your kitchen on the go.</p>
  
  <h2>The database</h2>
  
  <p>In total there are three tables. </p>
  
  <p><strong>Lists</strong></p>
  
  <p>This holds ListIDs and names of these Lists, like 'Fridge' or 'Freezer'. So, this is basically the 'container'.</p>
  
  <p><strong>Items</strong></p>
  
  <p>Then there's <em>Items</em> which holds itemtypes, i.e. Milk, 1 gallon.</p>
  
  <p><strong>ListItems</strong></p>
  
  <p>Then there's <em>ListItems</em> (sorry for a slightly confusing name) which holds <strong>specific</strong> items. While <em>Items</em> is just a table of items which can be added, <em>ListItems</em> are the <strong>added</strong> items. So the rows added to this table naturally have a foreign key to both a <em>List</em> on the <em>Lists</em> table and a foreign key to an <em>Item</em> on the <em>Items</em> table. This table's primary key is a superkey, made from nearly all its attributes.</p>
  
  <p>Two ListItems can refer to the same <em>Item</em> and same <em>List</em>, as long as they have different attributes. They could expire on different dates or be different unit sizes. The only thing that makes an <em>Item</em> unique is the name, like if you added a 'Ham', it would never overwrite a 'Milk'. Adding another 'Milk' would then be seen as the same <em>Item</em></p>
  
  <h2>The Problem</h2>
  
  <p>Here's an example. You want to add two seperate items. The first is 3 hams, each 200 g which expire on the 28th of May.
  The next is another ham, this one single, but 500g and expires on the 31st of May: 
  <img src="https://i.stack.imgur.com/XIk2M.png" alt="enter image description here"></p>
  
  <p>ListID 1 refers to the <em>List</em> which is called Fridge.
  ItemID 1 refers to the item which is called <em>Ham</em>.</p>
  
  <p>See the problem? The foreign keys are the same.</p>
  
  <p>It stores just fine on our local database but when syncing with an Azure database, we get the following error:</p>
  
  <pre><code>{"Violation of PRIMARY KEY constraint 'PK__#A4D1762__44A4C03D49E5E4B8'. 
  Cannot insert duplicate key in object 'dbo.@changeTable'. 
  The duplicate key value is (1, 1).\r\n
  The data for table-valued parameter \"@changeTable\" doesn't conform to the table type of the parameter. 
  SQL Server error is: 3602, state: 30\r\nThe statement has been terminated."}
  </code></pre>
  
  <p>Is this poor database design or an Azure problem?</p>
  
  <h2>Update 1</h2>
  
  <p>Here's how the DDL looks on the local database:</p>
  
  <pre><code>CREATE TABLE [dbo].[ListItems] (
      [ListId] INT NOT NULL,
      [ItemId] INT NOT NULL,
      [Amount] INT NOT NULL, 
      [Volume] INT NOT NULL, 
      [Unit] NVARCHAR(MAX) NULL, 
      [ShelfLife] DATETIME NOT NULL, 
      CONSTRAINT [pk_ListItems] PRIMARY KEY CLUSTERED ([ShelfLife], [Volume], [Amount], [ItemId], [ListId]),
      CONSTRAINT [fk_ListItems] FOREIGN KEY ([ListId]) REFERENCES [dbo].[Lists] ([ListId]) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT [fk_ListItems2] FOREIGN KEY ([ItemId]) REFERENCES [dbo].[Items] ([ItemId]) ON DELETE CASCADE ON UPDATE CASCADE
  );
  </code></pre>
  
  <p>And the Azure database DDL:</p>
  
  <pre><code>CREATE TABLE [dbo].[ListItems] (
      [ListId]    INT            NOT NULL,
      [ItemId]    INT            NOT NULL,
      [Amount]    INT            NOT NULL,
      [Volume]    INT            NOT NULL,
      [Unit]      NVARCHAR (MAX) NULL,
      [ShelfLife] DATETIME       NOT NULL,
      CONSTRAINT [PK_dbo.ListItems] PRIMARY KEY CLUSTERED ([ListId] ASC, [ItemId] ASC, [Amount] ASC, [Volume] ASC, [ShelfLife] ASC),
      CONSTRAINT [FK_dbo.ListItems_dbo.Items_ItemId] FOREIGN KEY ([ItemId]) REFERENCES [dbo].[Items] ([ItemId]) ON DELETE CASCADE,
      CONSTRAINT [FK_dbo.ListItems_dbo.Lists_ListId] FOREIGN KEY ([ListId]) REFERENCES [dbo].[Lists] ([ListId]) ON DELETE CASCADE
  );
  </code></pre>
  
  <p>The PK CONSTRAINT line is slightly different, but the attributes are the same. Could this be the problem?</p>