Getting 'Cannot insert explicit value for identity column in table 'OrderPromo' when IDENTITY_INSERT is set to OFF.' on table with NO Identity

Question

I have this table created like so:

USE [OrderMore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[OrderPromo](
    [OrderId] [int] NOT NULL,
    [PromoId] [int] NOT NULL,
    [Created] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.OrderPromo] PRIMARY KEY CLUSTERED 
(
    [OrderId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[OrderPromo]  WITH CHECK ADD  CONSTRAINT [FK_dbo.OrderPromo_dbo.Order_OrderId] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Order] ([OrderID])
GO
ALTER TABLE [dbo].[OrderPromo] CHECK CONSTRAINT [FK_dbo.OrderPromo_dbo.Order_OrderId]

The interesting entities look like this:

[Table("OrderPromo")]
public class OrderPromo : EntityBase
{
    /// <summary>
    /// 
    /// </summary>
    [Key, ForeignKey("Order")]
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int OrderId { get; set; }

    /// <summary>
    /// 
    /// </summary>
    public int PromoId { get; set; }

    /// <summary>
    /// 
    /// </summary>
    public virtual Order Order { get; set; }

}

and:

[Table("Order")]
    public partial class Order : EntityBase
    {
        /// <summary>
        /// 
        /// </summary>
        [Key]
        public int OrderID { get; set; }

        /// <summary>
        /// 
        /// </summary>
        public virtual Transaction Transaction { get; set; }

        /// <summary>
        /// If there is an Promo used on this Order, it will be recorded in the OrderPromo table.
        /// </summary>
        public virtual OrderPromo OrderPromo { get; set; }
    }

The issue comes when I attempt to insert the data into the database. I receive the Cannot insert explicit value for identity column in table 'OrderPromo' when IDENTITY_INSERT is set to OFF.

However, there is no Identity column in the OrderPromo table. I have even tried to set IDENTITY_INSERT to ON just to be 100% sure, and SQL gives the error indicating there is no Identity column on the table.

Thank you.

EDIT--

Table definition for Order:

USE [OrderMore]
GO
/****** Object:  Table [dbo].[Order]    Script Date: 01/05/2017 10:57:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Order](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderFormID] [int] NOT NULL,
    [Notes] [nvarchar](250) NULL,
    [Created] [datetime] NOT NULL,
    [TransactionID] [int] NOT NULL,
    [OrderSequenceContext] [int] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_dbo.Order] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Order_dbo.Transaction_TransactionID] FOREIGN KEY([TransactionID])
REFERENCES [dbo].[Transaction] ([TransactionID])
GO
ALTER TABLE [dbo].[Order] CHECK CONSTRAINT [FK_dbo.Order_dbo.Transaction_TransactionID]

When you say you try to add add data. Did you added a order first and then tried to add an orderpromo and failed? — Christos, Jan 05 '17 at 19:00
The `Order` table does have an `IDENTITY` column, so, that seems to be your issue — Lamak, Jan 05 '17 at 19:01
@Lamak I do not understand why the 'IDENTITY' column in Order is interfering with inserting into OrderPromo? — OCDDev, Jan 05 '17 at 19:03
Are you pointing to the right database/server?, this doesn't seem possible — Lamak, Jan 05 '17 at 19:08
@Lamak I am about 99.9% positive I am pointed at he correct server. I am glad that you find this situation in the impossible realm as well. :) — OCDDev, Jan 05 '17 at 19:11
@Christos If I have everything set up correctly, an Order should be inserted (Principal end) before an OrderPromo is. However, there are no Orders being inserted before the exception is thrown. — OCDDev, Jan 05 '17 at 19:12
@H.G.Sandhagen Why not? It's perfectly valid for a one to one or zero relationship. — juharr, Jan 05 '17 at 19:14
I'd suggest finding out the exact sql that EF is genereating to determine what exactly is going on. Also have to tried running insert commands on `OrderPromo` in SSMS? — juharr, Jan 05 '17 at 19:19
@juharr I will look into the code that EF is generating. I have inserted directly from SSMS with no issue, however. — OCDDev, Jan 05 '17 at 19:26
@OCDDev Yeah, I just tried it myself and it worked fine. I'm guessing EF is doing something weird. Can you share the EF code you're calling? — juharr, Jan 05 '17 at 19:28
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132436/discussion-between-ocddev-and-juharr). — OCDDev, Jan 05 '17 at 19:44

score 0 · Answer 1 · answered Jan 05 '17 at 19:03

0

You can try turn the IDENTITY_INSERT On like

SET IDENTITY_INSERT Order ON
--Insert script here
SET IDENTITY_INSERT Order OFF

answered Jan 05 '17 at 19:03

Tony Dong

3,213
1
29
32

The error is stating the issue is with OrderPromo, not Order itself. I am unable to set `IDENTITY_INSERT` on OrderPromo because there is no `IDENTITY`. Why/how would turning it Order make a difference? – OCDDev Jan 05 '17 at 19:06
Could you post OrderPromo definition? Does not make sense to affect OrderPromo if it don't have IDENTITY – Tony Dong Jan 05 '17 at 19:09
Why you have PRIMARY KEY CLUSTERED in OrderPromo table for OrderID? Is this one to one relationship with Order table? How about using OrderId combine with PromoId as PRIMARY KEY? – Tony Dong Jan 05 '17 at 19:16
@TonyDong That is how EF migrations set it up. OrderPromo is a one to one with Order. – OCDDev Jan 05 '17 at 19:22
Could you add OrderPromoID in OrderPromo table and make it as primary key and make OrderID as Foreign Key? – Tony Dong Jan 05 '17 at 19:26
@TonyDong That would change the relationship from one to one or zero to one to many, which I assume is incorrect. – juharr Jan 05 '17 at 19:27
Yes, but you can make the OrderID in OrderPromo talbe unique index that will it works for one to one or zero – Tony Dong Jan 05 '17 at 19:33
Here is a discuss about how to create a real one to one relationship http://stackoverflow.com/questions/10292355/how-do-i-create-a-real-one-to-one-relationship-in-sql-server – Tony Dong Jan 05 '17 at 19:35

score 0 · Answer 2 · answered Jan 05 '17 at 19:14

0

You are declaring OrderId to be the primary key in table OrderPromo although it seems to be a foreign key. It should be the primary key of table Order.

Remove the primary key on OrderId from OrderPromo and it should work.

I believe what's happening is that the insert is trying to force the value of the referenced Order into the OrderId field of OrderPromo, causing it to fail.

answered Jan 05 '17 at 19:14

JuanR

7,405
1
19
30

That would change the relationship from one to one or zero to one to many. Also EF does not play well with tables that do not have primary keys. – juharr Jan 05 '17 at 19:16
OrderID is the PK of the Order table as well. "I believe what's happening is that the insert is trying to force the value of the referenced Order into the OrderId field of OrderPromo"..that is exactly what I think should be happening. – OCDDev Jan 05 '17 at 19:18
You should rethink your design then. I imagine an order Id uniquely identifies an order, not an order promo. If your relationship is one to one, you might as well move the fields to the Order table, no? You can solve the issue with EF needing a PK easily by adding your own PK field, which is the recommended way to go anyways. – JuanR Jan 05 '17 at 20:22

Getting 'Cannot insert explicit value for identity column in table 'OrderPromo' when IDENTITY_INSERT is set to OFF.' on table with NO Identity

2 Answers2