Google Groups

Re: [ServiceStack] Cannot insert duplicate key row in object


Alec Taylor Apr 29, 2012 8:33 PM
Posted in group: ServiceStack .NET Open Source REST Web Services Framework
Okay, I've asked it on stackoverflow.

Note that I used practically identical code to:
http://www.servicestack.net/docs/ormlite/ormlite-overview

On Mon, Apr 30, 2012 at 3:29 AM, Demis Bellot <demis....@gmail.com> wrote:
> You should ask your remaining questions on StackOverflow as they all seem to
> be issues with existing RDBMS concepts rather than problems with the OrmLite
> library.
>
> I recommended earlier that you should've removed all your foreign keys which
> would cause these exceptions as a result of trying to insert invalid data
> that violated you're defined foreign key constraints.
>
>
> On Sun, Apr 29, 2012 at 1:15 PM, Alec Taylor <alec.t...@gmail.com> wrote:
>>
>> Sorry about that, realised my mistake
>>
>> (I had actually fixed this in previous pastebins, not sure why my code
>> reverted)
>>
>> Final error is from Line 235 (called from line 199):
>> http://pastebin.com/nf2L5mfa
>> "The INSERT statement conflicted with the FOREIGN KEY constraint
>> "FK_Order_Customer_CustomerId". The conflict occurred in database
>> "C:\USERS\ALECTAYLOR\SOCIALBOOTSTRAPAPI\SRC\SOCIALBOOTSTRAPAPI\APP_DATA\USERAUTH.MDF",
>> table "dbo.Customer", column 'Id'. The statement has been terminated."
>>
>> On Monday, April 30, 2012 3:03:03 AM UTC+10, mythz wrote:
>>>
>>> This is a basic C# exception:
>>>
>>>  new Product { Name="Salty styled shirt", Category="Shirts",
>>> Price=40.40m, Variations={"Large", "Medium", "Small"}
>>>
>>>
>>> You're trying to add a collection of strings to Product.Variations when
>>> it is null. Errors like these are easily discoverable with debugging.
>>>
>>> I encourage you to do your own analysis as I'm quite busy atm and these
>>> are obvious errors, easily identified by looking at the StackTrace +
>>> debugger.
>>>
>>> - Demis
>>>
>>>
>>> On Sun, Apr 29, 2012 at 12:57 PM, Alec Taylor <alec.t...@gmail.com>
>>> wrote:
>>>>
>>>> Hmm, I fixed up the order of the deletes, thus making some progress but
>>>> now its reverted back to an old error:
>>>>
>>>> NullReferenceException: Object reference not set to an instance of an
>>>> object
>>>>
>>>> Stack trace:
>>>> [ShopService: 29/04/2012 4:52:41 PM]: [REQUEST:
>>>> ]System.NullReferenceException: Object reference not set to an instance of
>>>> an object. at
>>>> SocialBootstrapApi.ServiceInterface.ShopService.<Run>b__1e(IDbCommand dbCmd)
>>>> in
>>>> c:\Users\AlecTaylor\SocialBootstrapApi\src\SocialBootstrapApi\ServiceInterface\ClothingService.cs:line
>>>> 170 at
>>>> ServiceStack.OrmLite.OrmLiteConnectionFactoryExtensions.Exec[T](IDbConnectionFactory
>>>> connectionFactory, Func`2 runDbCommandsFn) at
>>>> SocialBootstrapApi.ServiceInterface.ShopService.Run(Shop request) in
>>>> c:\Users\AlecTaylor\SocialBootstrapApi\src\SocialBootstrapApi\ServiceInterface\ClothingService.cs:line
>>>> 109 at ServiceStack.ServiceInterface.ServiceBase`1.Execute(TRequest request)
>>>>
>>>> Lines 170-177: http://pastebin.com/TdtqXCVM
>>>>
>>>> What am I doing wrong?
>>>>
>>>>
>>>> Thanks for all info,
>>>>
>>>> Alec Taylor
>>>>
>>>> On Monday, April 30, 2012 2:46:22 AM UTC+10, mythz wrote:
>>>>>
>>>>> If you have foreign keys, you have to be mindful about the order for
>>>>> which you drop tables based on their dependencies.
>>>>>
>>>>>> "Cannot insert duplicate key row in object 'dbo.Shop' with unique
>>>>>> index 'uidx_shop_name'. The duplicate key value is (Johan's Clothing Shop).
>>>>>> The statement has been terminated."
>>>>>
>>>>>
>>>>> The error message is clear (and doesn't result from the source code
>>>>> pasted above) you're trying to insert a new row somewhere with a name that
>>>>> violates the Shop.Name unique index constraint I mentioned earlier.
>>>>>
>>>>> I would physically drop all db tables + indexes from your DB and start
>>>>> again, without any unique indexes or foreign keys which will also throw
>>>>> invalid data exceptions.
>>>>>
>>>>>
>>>>> On Sun, Apr 29, 2012 at 12:34 PM, Alec Taylor <alec.t...@gmail.com>
>>>>> wrote:
>>>>>>
>>>>>> Tried that, but that also didn't work.
>>>>>>
>>>>>> Something is strange though...
>>>>>>
>>>>>> under:
>>>>>>         protected override object Run(Shop request) {
>>>>>>             return DbFactory.Exec(dbCmd => {
>>>>>>
>>>>>> I added:
>>>>>>                 dbCmd.DropTable<Shop>();
>>>>>>                 dbCmd.DropTable<Order>();
>>>>>>                 dbCmd.DropTable<OrderDetail>();
>>>>>>                 dbCmd.DropTable<Customer>();
>>>>>>                 dbCmd.DropTable<Product>();
>>>>>>
>>>>>> But running the code still gave me the error: "Cannot insert duplicate
>>>>>> key row in object 'dbo.Shop' with unique index 'uidx_shop_name'. The
>>>>>> duplicate key value is (Johan's Clothing Shop). The statement has been
>>>>>> terminated."
>>>>>>
>>>>>> What is going on?
>>>>>>
>>>>>> Thanks for all info,
>>>>>>
>>>>>> Alec Taylor
>>>>>>
>>>>>>
>>>>>> On Monday, April 30, 2012 1:54:24 AM UTC+10, mythz wrote:
>>>>>>>
>>>>>>> Unique Ids have no effect on a unique column index constraint - it is
>>>>>>> column-specific.
>>>>>>> Change [Index(Unique=true)] to [Index] if you want to be able to
>>>>>>> store duplicate values in that column.
>>>>>>>
>>>>>>> On Sun, Apr 29, 2012 at 11:47 AM, Alec Taylor
>>>>>>> <alec.t...@gmail.com> wrote:
>>>>>>>>
>>>>>>>> Yes, but there are no duplicates in that field, and the overwrite is
>>>>>>>> set to true for create table.
>>>>>>>>
>>>>>>>> So what am I meant to be doing? - The overwrite should forgo the
>>>>>>>> need for dropping tables before running anything, right? - Even if it
>>>>>>>> doesn't overwrite, I should get a million of the same names and whatnot, but
>>>>>>>> with unique IDs to differentiate them.
>>>>>>>>
>>>>>>>> Thanks for all suggestions,
>>>>>>>>
>>>>>>>> Alec Taylor
>>>>>>>>
>>>>>>>> BTW: This is the Shop class:
>>>>>>>>
>>>>>>>> public class Shop {
>>>>>>>>     [AutoIncrement]
>>>>>>>>     [Alias("ShopID")]
>>>>>>>>     // [Index(Unique = true)] // Also tried moving it here
>>>>>>>>     public Int32 Id { get; set; }
>>>>>>>>     [Index(Unique = true)]
>>>>>>>>     [StringLength(40)]
>>>>>>>>     public string Name { get; set; }
>>>>>>>>     public DateTime SignedUpOn { get; set; }
>>>>>>>>     public bool Active { get; set; }
>>>>>>>>     [Alias("ShopLocation")]
>>>>>>>>     public Address Address { get; set; }
>>>>>>>> }
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> On Monday, April 30, 2012 1:39:13 AM UTC+10, mythz wrote:
>>>>>>>>>
>>>>>>>>> You're getting a unique value constraint error which would be the
>>>>>>>>> result of trying to insert duplicates on properties you've marked
>>>>>>>>> with [Index(Unique = true)]
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> On Sun, Apr 29, 2012 at 11:17 AM, Alec Taylor
>>>>>>>>> <alec.t...@gmail.com> wrote:
>>>>>>>>>>
>>>>>>>>>> I have been coding with ServiceStack for a little while now, but
>>>>>>>>>> strangely enough I've been getting this error: "Cannot insert duplicate key
>>>>>>>>>> row in object 'dbo.Shop' with unique index 'uidx_shop_name'. The duplicate
>>>>>>>>>> key value is (Johann's Clothing Shop).
>>>>>>>>>> The statement has been terminated."
>>>>>>>>>>
>>>>>>>>>> Stack trace:
>>>>>>>>>> [ShopService: 29/04/2012 3:10:23 PM]: [REQUEST:
>>>>>>>>>> ]System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate
>>>>>>>>>> key row in object 'dbo.Shop' with unique index 'uidx_shop_name'. The
>>>>>>>>>> duplicate key value is (Johann's Clothing Shop). The statement has been
>>>>>>>>>> terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException
>>>>>>>>>> exception, Boolean breakConnection, Action`1 wrapCloseInAction) at
>>>>>>>>>> System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception,
>>>>>>>>>> Boolean breakConnection, Action`1 wrapCloseInAction) at
>>>>>>>>>> System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
>>>>>>>>>> stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at
>>>>>>>>>> System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand
>>>>>>>>>> cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
>>>>>>>>>> bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at
>>>>>>>>>> System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName,
>>>>>>>>>> Boolean async, Int32 timeout) at
>>>>>>>>>> System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1
>>>>>>>>>> completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean
>>>>>>>>>> asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
>>>>>>>>>> ServiceStack.MiniProfiler.Data.ProfiledDbCommand.ExecuteNonQuery() at
>>>>>>>>>> ServiceStack.OrmLite.OrmLiteWriteExtensions.ExecuteSql(IDbCommand dbCmd,
>>>>>>>>>> String sql) at
>>>>>>>>>> ServiceStack.OrmLite.OrmLiteWriteExtensions.InsertAll[T](IDbCommand dbCmd,
>>>>>>>>>> IEnumerable`1 objs) at
>>>>>>>>>> SocialBootstrapApi.ServiceInterface.ShopService.<Run>b__20(IDbCommand dbCmd)
>>>>>>>>>> in
>>>>>>>>>> c:\Users\AlecTaylor\SocialBootstrapApi\src\SocialBootstrapApi\ServiceInterface\ClothingService.cs:line
>>>>>>>>>> 142 at
>>>>>>>>>> ServiceStack.OrmLite.OrmLiteConnectionFactoryExtensions.Exec[T](IDbConnectionFactory
>>>>>>>>>> connectionFactory, Func`2 runDbCommandsFn) at
>>>>>>>>>> SocialBootstrapApi.ServiceInterface.ShopService.Run(Shop request) in
>>>>>>>>>> c:\Users\AlecTaylor\SocialBootstrapApi\src\SocialBootstrapApi\ServiceInterface\ClothingService.cs:line
>>>>>>>>>> 109 at ServiceStack.ServiceInterface.ServiceBase`1.Execute(TRequest request)
>>>>>>>>>> ClientConnectionId:5bb10372-54f5-48ba-a0c5-b2a12d692dc3
>>>>>>>>>>
>>>>>>>>>> The code: http://pastebin.com/PS8SUupx
>>>>>>>>>>
>>>>>>>>>> I have tried to write my code as standards compliant as possible
>>>>>>>>>> (you'll find that almost all of it is taken from ServiceStack example code),
>>>>>>>>>> but I still might have some bad practices + bad code quality.
>>>>>>>>>>
>>>>>>>>>> Please point out any of these sorts of problems as well as telling
>>>>>>>>>> me how to fix the database problem.
>>>>>>>>>>
>>>>>>>>>> Thanks,
>>>>>>>>>>
>>>>>>>>>> Alec Taylor
>>>>>>>>>>
>>>>>>>>>> BTW: The only unique field I want in each class is Id
>>>>>>>>>>
>>>>>>>>>> FYI: I have also tried renaming the Shop names
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> - Demis
>>>>>>>>>
>>>>>>>>> http://twitter.com/demisbellot
>>>>>>>>> http://www.servicestack.net/mythz_blog
>>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> --
>>>>>>> - Demis
>>>>>>>
>>>>>>> http://twitter.com/demisbellot
>>>>>>> http://www.servicestack.net/mythz_blog
>>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> - Demis
>>>>>
>>>>> http://twitter.com/demisbellot
>>>>> http://www.servicestack.net/mythz_blog
>>>>>
>>>
>>>
>>>
>>> --
>>> - Demis
>>>
>>> http://twitter.com/demisbellot
>>> http://www.servicestack.net/mythz_blog
>>>
>
>
>
> --
> - Demis
>
> http://twitter.com/demisbellot
> http://www.servicestack.net/mythz_blog
>