Foreign Key issue preventing me from updating table in azure sql database (x-post from r/sql)

I'm working on a webapp (C#, MVC 5) that uses an Azure SQL database. I'm also implementing the built in ASP.NET Identity 2.0 system (and I'm sure that's where my issue is).

Probably because of a setting i chose, the credential system insisted on using a local db, rather than my cloud based db (which is fairly useless once my laptop is turned off).

I've migrated all the tables to my azure sql db, set up a new connection string and pointed the IdentityModel at the new connection, rather than the default (and yes, it is a different connection than the regular azure connection which uses entity framework rather than SqlClient)

When I try to register against the local database, everything works perfectly. When I change it to reference the azure database, I get this error:

 SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AspNetUsers_ToTable". The conflict occurred in database "PhoenixMachineTracking", table "dbo.Distributor", column 'Dist_ID'. The statement has been terminated. 

I've changed a few things with little success (though this is the newest error; i'm making progress lol) and i'm not sure at this point if the issue is within my connection string (i don't think so), my c# code (I can't find anything that looks wrong) or my tables (it looks like all of my foreign keys are present and correct)

What am I doing wrong? Below are the table creation scripts, as well as my "classes" (not sure if that's what they're called when VS builds them for you from a database)

Distributor Table (users that will be logging in)

 CREATE TABLE [dbo].[Distributor] ( [Dist_ID] NVARCHAR (128) NOT NULL, [Dist_Name] NVARCHAR (256) NULL, [Dist_Address] VARCHAR (50) NULL, [Dist_City] VARCHAR (50) NULL, [Dist_State] VARCHAR (50) NULL, [Dist_Zip] VARCHAR (50) NULL, [Dist_Phone] VARCHAR (50) NULL, [Dist_Fax] VARCHAR (50) NULL, [Dist_Contact] VARCHAR (50) NULL, [Dist_Email] VARCHAR (50) NULL, CONSTRAINT [PK_dbo.Distributor] PRIMARY KEY CLUSTERED ([Dist_ID] ASC)); 

ASP.NET Table generated by visual studio as part of ASP Identity

 CREATE TABLE [dbo].[AspNetUsers] ( [Id] NVARCHAR (128) NOT NULL, [Email] NVARCHAR (256) NULL, [EmailConfirmed] BIT NOT NULL, [PasswordHash] NVARCHAR (MAX) NULL, [SecurityStamp] NVARCHAR (MAX) NULL, [PhoneNumber] NVARCHAR (MAX) NULL, [PhoneNumberConfirmed] BIT NOT NULL, [TwoFactorEnabled] BIT NOT NULL, [LockoutEndDateUtc] DATETIME NULL, [LockoutEnabled] BIT NOT NULL, [AccessFailedCount] INT NOT NULL, [UserName] NVARCHAR (256) NOT NULL, CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC), CONSTRAINT [FK_AspNetUsers_ToTable] FOREIGN KEY ([Id]) REFERENCES [dbo].[Distributor] ([Dist_ID])); GO CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex] ON [dbo].[AspNetUsers]([UserName] ASC); 

ASP.NET Users Class – generated from database

namespace PhoenixProductTracking.db { using System; using System.Collections.Generic; public partial class AspNetUsers { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public AspNetUsers() { this.AspNetUserClaims = new HashSet<AspNetUserClaims>(); this.AspNetUserLogins = new HashSet<AspNetUserLogins>(); this.AspNetRoles = new HashSet<AspNetRoles>(); } public string Id { get; set; } public string Email { get; set; } public bool EmailConfirmed { get; set; } public string PasswordHash { get; set; } public string SecurityStamp { get; set; } public string PhoneNumber { get; set; } public bool PhoneNumberConfirmed { get; set; } public bool TwoFactorEnabled { get; set; } public Nullable<System.DateTime> LockoutEndDateUtc { get; set; } public bool LockoutEnabled { get; set; } public int AccessFailedCount { get; set; } public string UserName { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<AspNetUserClaims> AspNetUserClaims { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<AspNetUserLogins> AspNetUserLogins { get; set; } public virtual Distributor Distributor { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<AspNetRoles> AspNetRoles { get; set; } }} 

Distributor class – generated from database

 namespace PhoenixProductTracking.db { using System; using System.Collections.Generic; public partial class Distributor { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Distributor() { this.Machine = new HashSet<Machine>(); } public string Dist_ID { get; set; } public string Dist_Name { get; set; } public string Dist_Address { get; set; } public string Dist_City { get; set; } public string Dist_State { get; set; } public string Dist_Zip { get; set; } public string Dist_Phone { get; set; } public string Dist_Fax { get; set; } public string Dist_Contact { get; set; } public string Dist_Email { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<Machine> Machine { get; set; } public virtual AspNetUsers AspNetUsers { get; set; } }} 

Identity Model Class (where the default connection is changed)

 namespace PhoenixProductTracking.Models { // You can add profile data for the user by adding more properties to your ApplicationUser class, please visit http://ift.tt/1fDWPnv to learn more. public class ApplicationUser : IdentityUser { public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager) { // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie); // Add custom user claims here return userIdentity; } } public class ApplicationDbContext : IdentityDbContext<ApplicationUser> { public ApplicationDbContext() : base("PhoenixLogInConnection", throwIfV1Schema: false) { **THIS IS WHERE THE CONNECTION WAS CHANGED FROM "DEFAULTCONNECTION"** } public static ApplicationDbContext Create() { return new ApplicationDbContext(); } }} 

Web.Config – where my connection strings are defined

Default (local) connection

 <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-PhoenixProductTracking-20160118123425.mdf;Initial Catalog=aspnet-PhoenixProductTracking-20160118123425;Integrated Security=True" providerName="System.Data.SqlClient" /> 

Azure Connection for Entities

<add name="PhoenixMachineTrackingEntities" connectionString="metadata=http://res*/db.PhoenixAzureDb.csdl|res*/db.PhoenixAzureDb.ssdl|res*/db.PhoenixAzureDb.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=*****.database.windows.net;initial catalog=PhoenixMachineTracking;persist security info=True;user id=******4;password=*****;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" /> 

Azure connection for Log-In

<add name="PhoenixLogInConnection" connectionString="Server=******.database.windows.net,1433;Database=PhoenixMachineTracking;User ID=*****;Password=******;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient"/> 

by Pidgey_OP via /r/csharp

Leave a Reply