Cannot insert duplicate key row in object

[Alec Taylor]

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 Bellot]

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)]

--
- Demis

http://twitter.com/demisbellot

http://www.servicestack.net/mythz_blog

[Alec Taylor]

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; }
}

[Demis Bellot]

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.

[Alec Taylor]

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

[Demis Bellot]

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.

[Alec Taylor]

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

[Demis Bellot]

This is a basic C# exception:

1.  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

[Alec Taylor]

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."

[Demis Bellot]

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.

[Alec Taylor]

Okay, I've asked it on stackoverflow.

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