Sqlite error 19 foreign key constraint failed

#c# #entity-framework #asp.net-core #.net-core #entity-framework-core

Вопрос:

Я просмотрел документацию и несколько других ответов, но не смог найти, в чем была моя проблема. У меня отношения «один ко многим Student » и ReportCard мне это нравится

 public class Student
{
    public long StudentId { get; set; }
    public string Name { get; set; }
    public Grade CurrentGradeLevel { get; set; }

    public ICollection<Guardian> Guardians { get; set; }
    public ICollection<Result> Results { get; set; }
    public ICollection<ReportCard> ReportCards { get; set; }
}

public class ReportCard
{
    public long ReportCardId { get; set; }
    public long StudentId { get; set; }
    public double OverallGrade { get; set;}

    public Student Student { get; set; }
    public Grade GradeLevel { get; set; }

    public ICollection<CourseGrade> Grades { get; set; }
}
 

Я настроил отношения StudentContext таким образом, чтобы:

 protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
                .HasMany(s => s.ReportCards)
                .WithOne(r => r.Student)
                .OnDelete(DeleteBehavior.Cascade);
}
 

Тем не менее , когда я пытаюсь удалить a ReportCard , происходит эта ошибка

 Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (1ms) [Parameters=[@p0='?'], CommandType='Text', CommandTimeout='30']
      DELETE FROM "ReportCards"
      WHERE "ReportCardId" = @p0;
      SELECT changes();
fail: Microsoft.EntityFrameworkCore.Update[10000]
      An exception occurred in the database while saving changes for context type 'Onero.Server.Models.ReportCardContext'.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
       ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.
         at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
         at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
       ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.
         at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
         at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
fail: Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[1]
      An unhandled exception has occurred while executing the request.
      Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
       ---> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'FOREIGN KEY constraint failed'.
         at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
         at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         --- End of inner exception stack trace ---
         at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.ExecuteAsync(IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
         at Onero.Server.Controllers.ReportCardController.DeleteReportCard(Int64 id) in /home/adrian/workspace/Onero/Server/Controllers/ReportCardController.cs:line 106
         at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(Stateamp; next, Scopeamp; scope, Objectamp; state, Booleanamp; isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(Stateamp; next, Scopeamp; scope, Objectamp; state, Booleanamp; isCompleted)
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
      --- End of stack trace from previous location ---
         at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
         at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
         at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
         at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events, IBackChannelLogoutService backChannelLogoutService)
         at IdentityServer4.Hosting.MutualTlsEndpointMiddleware.Invoke(HttpContext context, IAuthenticationSchemeProvider schemes)
         at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
         at IdentityServer4.Hosting.BaseUrlMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Builder.Extensions.MapWhenMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Builder.Extensions.MapMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware.Invoke(HttpContext context)
         at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
fail: Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseDeveloperPageExceptionFilter[7]
      An exception occurred while calculating the database error page content. Skipping display of the database error page.
      System.InvalidOperationException: StatusCode cannot be set because the response has already started.
         at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ThrowResponseAlreadyStartedException(String value)
         at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.set_StatusCode(Int32 value)
         at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.Microsoft.AspNetCore.Http.Features.IHttpResponseFeature.set_StatusCode(Int32 value)
         at Microsoft.AspNetCore.Http.DefaultHttpResponse.set_StatusCode(Int32 value)
         at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.Views.DatabaseErrorPage.ExecuteAsync()
         at Microsoft.Extensions.RazorViews.BaseView.ExecuteAsync(HttpContext context)
         at Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseDeveloperPageExceptionFilter.HandleExceptionAsync(ErrorContext errorContext, Fun
 

Почему это может быть? Я ценю любую помощь.

Редактировать

По запросу, вот CourseGrade

     public class CourseGrade
    {
        public long CourseGradeId { get; set; }
        public long CourseModelId { get; set; }
        public long StudentId { get; set; }
        public double Grade { get; set; }
    }
 

А вот мой api удаления, который я только что вызвал с почтальоном

         [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteReportCard(long id)
        {
            var reportCard = await _context.ReportCards.FindAsync(id);
            if (reportCard == null)
            {
                return NotFound();
            }

            _context.ReportCards.Remove(reportCard);
            await _context.SaveChangesAsync();

            return NoContent();
        }
 

ВТОРОЕ РЕДАКТИРОВАНИЕ: включено полное сообщение об ошибке.

Комментарии:

1. Не могли бы вы тоже опубликовать курс обучения, пожалуйста?

2. «У меня есть как StudentContext, так и ReportCardContext» в чем разница и в каком контексте вы разместили здесь?

3. Я разместил студенческий текст. ReportCardContext содержит только набор данных ReportCard.

4. CourseGrade — это просто ` длинный идентификатор { get; set;} двойной результат { get; set; }«

5. Как это связано с бесчисленными оценками<CourseGrade>? Пожалуйста, опубликуйте реальный код. В том, что вы опубликовали, нет никакого смысла.

Ответ №1:

у вас ошибка в классе CourseGrade

 public ICollection<CourseGrade> Grades { get; set; }
 

вы не можете использовать его таким образом, так как у вас нет идентификатора ReportCardId, EF core необходимо добавить идентификатор ReportCardId в качестве теневого свойства в CourseGrade. Это то, что вызывает ошибку. Сначала вы должны удалить записи с идентификатором ReportCardId из CourseGrade. Или удалите оценки свойств, если вам это не нужно, и снова выполните миграцию БД

Комментарии:

1. Да, это была ключевая часть моей проблемы. Возможно, на самом деле это была единственная ошибка компиляции, вызывающая ошибку, трудно сказать, учитывая все внесенные мной изменения; Я реструктурировал многие свои классы, о которых здесь не упоминал, в том числе убрал это свойство из курса. В итоге мне пришлось сделать ERD, чтобы я мог видеть все, что происходило сразу

Exception message: SQLite Error 19: 'FOREIGN KEY constraint failed'.
Stack trace: 
> at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC (System.Int32 rc, SQLitePCL.sqlite3 db) [0x00053] in <1f2a4e259b9a4b0480d2b09b7c367a1b>:0 
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x00220] in <1f2a4e259b9a4b0480d2b09b7c367a1b>:0 
  at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReaderAsync (System.Data.CommandBehavior behavior, System.Threading.CancellationToken cancellationToken) [0x00007] in <1f2a4e259b9a4b0480d2b09b7c367a1b>:0 
  at Microsoft.Data.Sqlite.SqliteCommand+<ExecuteDbDataReaderAsync>d__52.MoveNext () [0x00011] in <1f2a4e259b9a4b0480d2b09b7c367a1b>:0 
--- End of stack trace from previous location where exception was thrown ---
  at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/exceptionservices/exceptionservicescommon.cs:152 
  at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x00037] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:187 
  at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x00028] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:156 
  at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x00008] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:128 
  at System.Runtime.CompilerServices.TaskAwaiter`1[TResult].GetResult () [0x00000] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:357 
  at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand+<ExecuteAsync>d__17.MoveNext () [0x00371] in <24ee1e6a7a5e4034abbedc03313e8dcd>:0 
--- End of stack trace from previous location where exception was thrown ---
  at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/exceptionservices/exceptionservicescommon.cs:152 
  at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x00037] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:187 
  at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x00028] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:156 
  at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x00008] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:128 
  at System.Runtime.CompilerServices.TaskAwaiter`1[TResult].GetResult () [0x00000] in /Users/builder/jenkins/workspace/xamarin-android-d15-7/xamarin-android/external/mono/mcs/class/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:357 
  at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch+<ExecuteAsync>d__32.MoveNext () [0x000a0] in <24ee1e6a7a5e4034abbedc03313e8dcd>:0 

Sqlite error 19 foreign key constraint failed

#c# #entity-framework #asp.net-core #.net-core #entity-framework-core

Вопрос:

Я просмотрел документацию и несколько других ответов, но не смог найти, в чем была моя проблема. У меня отношения «один ко многим Student » и ReportCard мне это нравится

Я настроил отношения StudentContext таким образом, чтобы:

Тем не менее , когда я пытаюсь удалить a ReportCard , происходит эта ошибка

Почему это может быть? Я ценю любую помощь.

По запросу, вот CourseGrade

А вот мой api удаления, который я только что вызвал с почтальоном

ВТОРОЕ РЕДАКТИРОВАНИЕ: включено полное сообщение об ошибке.

Комментарии:

1. Не могли бы вы тоже опубликовать курс обучения, пожалуйста?

2. «У меня есть как StudentContext, так и ReportCardContext» в чем разница и в каком контексте вы разместили здесь?

3. Я разместил студенческий текст. ReportCardContext содержит только набор данных ReportCard.

4. CourseGrade — это просто ` длинный идентификатор < get; set;>двойной результат < get; set; >«

5. Как это связано с бесчисленными оценками ? Пожалуйста, опубликуйте реальный код. В том, что вы опубликовали, нет никакого смысла.

Ответ №1:

у вас ошибка в классе CourseGrade

вы не можете использовать его таким образом, так как у вас нет идентификатора ReportCardId, EF core необходимо добавить идентификатор ReportCardId в качестве теневого свойства в CourseGrade. Это то, что вызывает ошибку. Сначала вы должны удалить записи с идентификатором ReportCardId из CourseGrade. Или удалите оценки свойств, если вам это не нужно, и снова выполните миграцию БД

Комментарии:

1. Да, это была ключевая часть моей проблемы. Возможно, на самом деле это была единственная ошибка компиляции, вызывающая ошибку, трудно сказать, учитывая все внесенные мной изменения; Я реструктурировал многие свои классы, о которых здесь не упоминал, в том числе убрал это свойство из курса. В итоге мне пришлось сделать ERD, чтобы я мог видеть все, что происходило сразу

Источник

Sqlite error 19 foreign key constraint failed

Answered by:

Question

I’m learning .NET development and have built an API a basic API. I have been able integrate Microsoft Identity into it and thats all working great along with Roles. I’ve come to a point where I’m created a new table called Locations and takes foreign key from the ASPNetUsers table (Id) and uses that in the locations table to associate the location owner. I’m having issues though with mapping this correctly with AutoMapper. The locations controller and the LocationCreateDto is all done. But I’m not sure how to map the ID column from the ASPNetUsers table to the ClientID column in the Locations table. The users ID is what the key is based on will be passed along from the view when user is selected at the time of creation for the location (IE when creation the location, and admin will select the user the location will belong to).

I may be way off on this and it may just be me not knowing Postman well, and the ef migration creating the locations table does note the correct foreign key being needed when creating the new record.

Is anyone able to point me in the right direction on this? The full API is available on Github. The AutoMapper config is in the helper folder, and the dataContext (in the Data folder) file has some custom mapping configs and this is where I’m thinking my issue is — configuring the mapping here.

In Postman I keep getting an error which I know is directly related to my lack of a proper mapping between the two table for the foreign key.

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

—> Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: ‘FOREIGN KEY constraint failed’.

Is anyone able to point me in the right direction here to get this worked out? I would be greatly appreciated.

Источник

Система управления базами данных SQLite. Изучаем язык запросов SQL и реляционные базы данных на примере библиотекой SQLite3. Курс для начинающих.

Часть 11.4: Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В реляционных базах данных, вернее, в реляционных СУБД есть множество приемов, которые позволяют обеспечить целостность данных, мы уже разобрались с ограничением уровня столбца и первичным ключом, который является одновременно и индексом таблицы базы данных, позволяющим значительно ускорить выборку данных из базы данных, и правилом, которое четко выполняет SQLite. В этой записи мы поговорим про еще одно ограничение уровня таблиц: внешний ключ или FOREIGN KEY.

Внешние ключи в базах данных SQLite: FOREIGN KEY в SQLite3

Из этой записи вы узнаете как работает и для чего нужны внешние ключи в базах данных SQLite3. Познакомитесь с синтаксисом FROREIGN KEY в SQLite. А так же узнаете, как реализовать связи один ко многим и многие ко многим между таблицами базы данных под управлением SQLite при помощи внешних ключей. Но, стоит заметить, что вы сможете реализовать данные связи и без использования FOREIGN KEY, но в этом случае вы никак не обеспечите целостность данных в ваших таблицах.

Работа внешних ключей в базах данных SQLite: FOREIGEN KEY и REFERENCE в SQLite3

Внешний ключ или FOREIGN KEY – это ограничение уровня таблицы в реляционных базах данных, в том числе и в базе данных SQLite3. Внешние ключи определяют правила, по которым будут связаны таблицы в базах данных SQLite. Но, кроме того, что внешние ключи определяют то, как будут связаны таблицы в базах данных SQLite3, они еще нужны для обеспечения целостности данных в базах данных.

В SQLite внешний ключ объявляется при помощи конструкции FOREIGN KEY, а таблица, на которую ссылается внешний ключ указывается после ключевого слово REFERENCE. Обратите внимание: указывается не только таблица, но и столбец, на который идет ссылка. Ниже вы можете увидеть изображение, на котором показан синтаксис использования внешнего ключа в базах данных SQLite, вернее синтаксис конструкции REFERENCE.

Синтаксис ограничения внешнего ключа в базах данных SQLite3

Правила использования внешнего ключа не очень сложные, но давайте разберемся с тем, как реализован внешний ключ в SQLite3 и его конструкции: FOREIGEN KEY и REFEERENCE. Обратите внимание: когда вы связываете таблицы при помощи внешнего ключа одна таблица является родительской, а вторая таблица является дочерней. Внешний ключ всегда ссылается на родительскую таблиц, другими словами конструкция FOREIGN KEY и REFERENCE указывается в дочерней таблице.

Подведем итог: внешний ключ в базах данных SQLite необходим для реализации связей между таблицами. FOREIGEN KEY позволяет реализовывать связи между таблицами в базах данных. Конструкция REFERENCE используется для указания ссылки на родительскую таблицу. Внешний ключ обеспечивает целостность данных между двумя таблицами и необходим для нормализации базы данных. Вторая нормальная форма и третья нормальная форма не могут быть реализованы без внешнего ключа. Вернее будет сказать: мы можем организовать связи между таблицами без внешнего ключа, но проверка правил при этом выполняться не будет.

Давайте рассмотрим несколько практических примеров реализации внешнего ключа в базах данных SQLite. Мы реализуем два типа связи: связь один ко многим и связь многие ко многим.

Реализация связи один ко многим в базах данных SQLite. Пример связи один ко многим и FOREIGEN KEY в SQLite

Давайте реализуем связь один ко многим при помощи внешнего ключа, для этого воспользуемся конструкциями FORIGEN KEY и REFERENCE. Мы будем связывать при помощи внешнего ключа две таблицы: таблицу авторов и таблицу книг, поэтому давайте договоримся о допущение, что один автор может написать много книг, но у книги может быть только один автор.

Чтобы реализовать связь один ко многим, нам нужно воспользоваться конструкцией FOREIGEN KEY и REFERENCE при создании таблицы при помощи команды CREATE.

Источник

1. Introduction to Foreign Key Constraints

SQL foreign key constraints are used to enforce «exists» relationships between tables. For example, consider a database schema created using the following SQL commands:

The applications using this database are entitled to assume that for each row in the track table there exists a corresponding row in the artist table. After all, the comment in the declaration says so. Unfortunately, if a user edits the database using an external tool or if there is a bug in an application, rows might be inserted into the track table that do not correspond to any row in the artist table. Or rows might be deleted from the artist table, leaving orphaned rows in the track table that do not correspond to any of the remaining rows in artist. This might cause the application or applications to malfunction later on, or at least make coding the application more difficult.

One solution is to add an SQL foreign key constraint to the database schema to enforce the relationship between the artist and track table. To do so, a foreign key definition may be added by modifying the declaration of the track table to the following:

This way, the constraint is enforced by SQLite. Attempting to insert a row into the track table that does not correspond to any row in the artist table will fail, as will attempting to delete a row from the artist table when there exist dependent rows in the track table There is one exception: if the foreign key column in the track table is NULL, then no corresponding entry in the artist table is required. Expressed in SQL, this means that for every row in the track table, the following expression evaluates to true:

Tip: If the application requires a stricter relationship between artist and track, where NULL values are not permitted in the trackartist column, simply add the appropriate «NOT NULL» constraint to the schema.

There are several other ways to add an equivalent foreign key declaration to a CREATE TABLE statement. Refer to the CREATE TABLE documentation for details.

The following SQLite command-line session illustrates the effect of the foreign key constraint added to the track table:

As you would expect, it is not possible to manipulate the database to a state that violates the foreign key constraint by deleting or updating rows in the artist table either:

SQLite uses the following terminology:

The parent table is the table that a foreign key constraint refers to. The parent table in the example in this section is the artist table. Some books and articles refer to this as the referenced table, which is arguably more correct, but tends to lead to confusion.

The child table is the table that a foreign key constraint is applied to and the table that contains the REFERENCES clause. The example in this section uses the track table as the child table. Other books and articles refer to this as the referencing table.

The parent key is the column or set of columns in the parent table that the foreign key constraint refers to. This is normally, but not always, the primary key of the parent table. The parent key must be a named column or columns in the parent table, not the rowid.

The child key is the column or set of columns in the child table that are constrained by the foreign key constraint and which hold the REFERENCES clause.

The foreign key constraint is satisfied if for each row in the child table either one or more of the child key columns are NULL, or there exists a row in the parent table for which each parent key column contains a value equal to the value in its associated child key column.

In the above paragraph, the term «equal» means equal when values are compared using the rules specified here. The following clarifications apply:

When comparing text values, the collating sequence associated with the parent key column is always used.

When comparing values, if the parent key column has an affinity, then that affinity is applied to the child key value before the comparison is performed.

2. Enabling Foreign Key Support

In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY nor SQLITE_OMIT_TRIGGER defined. If SQLITE_OMIT_TRIGGER is defined but SQLITE_OMIT_FOREIGN_KEY is not, then SQLite behaves as it did prior to version 3.6.19 (2009-10-14) — foreign key definitions are parsed and may be queried using PRAGMA foreign_key_list, but foreign key constraints are not enforced. The PRAGMA foreign_keys command is a no-op in this configuration. If OMIT_FOREIGN_KEY is defined, then foreign key definitions cannot even be parsed (attempting to specify a foreign key definition is a syntax error).

Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.) The application can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled. The following command-line session demonstrates this:

Tip: If the command «PRAGMA foreign_keys» returns no data instead of a single row containing «0» or «1», then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).

It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.

3. Required and Suggested Database Indexes

Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index. If the parent key columns have a UNIQUE index, then that index must use the collation sequences that are specified in the CREATE TABLE statement for the parent table. For example,

The foreign key constraints created as part of tables child1, child2 and child3 are all fine. The foreign key declared as part of table child4 is an error because even though the parent key column is indexed, the index is not UNIQUE. The foreign key for table child5 is an error because even though the parent key column has a unique index, the index uses a different collating sequence. Tables child6 and child7 are incorrect because while both have UNIQUE indices on their parent keys, the keys are not an exact match to the columns of a single UNIQUE index.

If the database schema contains foreign key errors that require looking at more than one table definition to identify, then those errors are not detected when the tables are created. Instead, such errors prevent the application from preparing SQL statements that modify the content of the child or parent tables in ways that use the foreign keys. Errors reported when content is changed are «DML errors» and errors reported when the schema is changed are «DDL errors». So, in other words, misconfigured foreign key constraints that require looking at both the child and parent are DML errors. The English language error message for foreign key DML errors is usually «foreign key mismatch» but can also be «no such table» if the parent table does not exist. Foreign key DML errors are reported if:

• The parent table does not exist, or
• The parent key columns named in the foreign key constraint do not exist, or
• The parent key columns named in the foreign key constraint are not the primary key of the parent table and are not subject to a unique constraint using collating sequence specified in the CREATE TABLE, or
• The child table references the primary key of the parent without specifying the primary key columns and the number of primary key columns in the parent do not match the number of child key columns.

The last bullet above is illustrated by the following:

By contrast, if foreign key errors can be recognized simply by looking at the definition of the child table and without having to consult the parent table definition, then the CREATE TABLE statement for the child table fails. Because the error occurs during a schema change, this is a DDL error. Foreign key DDL errors are reported regardless of whether or not foreign key constraints are enabled when the table is created.

Indices are not required for child key columns but they are almost always beneficial. Returning to the example in section 1, each time an application deletes a row from the artist table (the parent table), it performs the equivalent of the following SELECT statement to search for referencing rows in the track table (the child table).

where ? in the above is replaced with the value of the artistid column of the record being deleted from the artist table (recall that the trackartist column is the child key and the artistid column is the parent key). Or, more generally:

If this SELECT returns any rows at all, then SQLite concludes that deleting the row from the parent table would violate the foreign key constraint and returns an error. Similar queries may be run if the content of the parent key is modified or a new row is inserted into the parent table. If these queries cannot use an index, they are forced to do a linear scan of the entire child table. In a non-trivial database, this may be prohibitively expensive.

So, in most real systems, an index should be created on the child key columns of each foreign key constraint. The child key index does not have to be (and usually will not be) a UNIQUE index. Returning again to the example in section 1, the complete database schema for efficient implementation of the foreign key constraint might be:

The block above uses a shorthand form to create the foreign key constraint. Attaching a «REFERENCES

. Refer to the CREATE TABLE documentation for further details.

4. Advanced Foreign Key Constraint Features

4.1. Composite Foreign Key Constraints

A composite foreign key constraint is one where the child and parent keys are both composite keys. For example, consider the following database schema:

In this system, each entry in the song table is required to map to an entry in the album table with the same combination of artist and album.

Parent and child keys must have the same cardinality. In SQLite, if any of the child key columns (in this case songartist and songalbum) are NULL, then there is no requirement for a corresponding row in the parent table.

4.2. Deferred Foreign Key Constraints

Each foreign key constraint in SQLite is classified as either immediate or deferred. Foreign key constraints are immediate by default. All the foreign key examples presented so far have been of immediate foreign key constraints.

If a statement modifies the contents of the database so that an immediate foreign key constraint is in violation at the conclusion the statement, an exception is thrown and the effects of the statement are reverted. By contrast, if a statement modifies the contents of the database such that a deferred foreign key constraint is violated, the violation is not reported immediately. Deferred foreign key constraints are not checked until the transaction tries to COMMIT. For as long as the user has an open transaction, the database is allowed to exist in a state that violates any number of deferred foreign key constraints. However, COMMIT will fail as long as foreign key constraints remain in violation.

If the current statement is not inside an explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit transaction is committed as soon as the statement has finished executing. In this case deferred constraints behave the same as immediate constraints.

To mark a foreign key constraint as deferred, its declaration must include the following clause:

The full syntax for specifying foreign key constraints is available as part of the CREATE TABLE documentation. Replacing the phrase above with any of the following creates an immediate foreign key constraint.

The defer_foreign_keys pragma can be used to temporarily change all foreign key constraints to deferred regardless of how they are declared.

The following example illustrates the effect of using a deferred foreign key constraint.

A nested savepoint transaction may be RELEASEd while the database is in a state that does not satisfy a deferred foreign key constraint. A transaction savepoint (a non-nested savepoint that was opened while there was not currently an open transaction), on the other hand, is subject to the same restrictions as a COMMIT — attempting to RELEASE it while the database is in such a state will fail.

If a COMMIT statement (or the RELEASE of a transaction SAVEPOINT) fails because the database is currently in a state that violates a deferred foreign key constraint and there are currently nested savepoints, the nested savepoints remain open.

4.3. ON DELETE and ON UPDATE Actions

Foreign key ON DELETE and ON UPDATE clauses are used to configure actions that take place when deleting rows from the parent table (ON DELETE), or modifying the parent key values of existing rows (ON UPDATE). A single foreign key constraint may have different actions configured for ON DELETE and ON UPDATE. Foreign key actions are similar to triggers in many ways.

The ON DELETE and ON UPDATE action associated with each foreign key in an SQLite database is one of «NO ACTION», «RESTRICT», «SET NULL», «SET DEFAULT» or «CASCADE». If an action is not explicitly specified, it defaults to «NO ACTION».

NO ACTION: Configuring «NO ACTION» means just that: when a parent key is modified or deleted from the database, no special action is taken.

RESTRICT: The «RESTRICT» action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it. The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated — not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.

SET NULL: If the configured action is «SET NULL», then when a parent key is deleted (for ON DELETE SET NULL) or modified (for ON UPDATE SET NULL), the child key columns of all rows in the child table that mapped to the parent key are set to contain SQL NULL values.

SET DEFAULT: The «SET DEFAULT» actions are similar to «SET NULL», except that each of the child key columns is set to contain the column’s default value instead of NULL. Refer to the CREATE TABLE documentation for details on how default values are assigned to table columns.

CASCADE: A «CASCADE» action propagates the delete or update operation on the parent key to each dependent child key. For an «ON DELETE CASCADE» action, this means that each row in the child table that was associated with the deleted parent row is also deleted. For an «ON UPDATE CASCADE» action, it means that the values stored in each dependent child key are modified to match the new parent key values.

For example, adding an «ON UPDATE CASCADE» clause to the foreign key as shown below enhances the example schema from section 1 to allow the user to update the artistid (the parent key of the foreign key constraint) column without breaking referential integrity:

Configuring an ON UPDATE or ON DELETE action does not mean that the foreign key constraint does not need to be satisfied. For example, if an «ON DELETE SET DEFAULT» action is configured, but there is no row in the parent table that corresponds to the default values of the child key columns, deleting a parent key while dependent child keys exist still causes a foreign key violation. For example:

Those familiar with SQLite triggers will have noticed that the «ON DELETE SET DEFAULT» action demonstrated in the example above is similar in effect to the following AFTER DELETE trigger:

Whenever a row in the parent table of a foreign key constraint is deleted, or when the values stored in the parent key column or columns are modified, the logical sequence of events is:

1. Execute applicable BEFORE trigger programs,
2. Check local (non foreign key) constraints,
3. Update or delete the row in the parent table,
4. Perform any required foreign key actions,
5. Execute applicable AFTER trigger programs.

There is one important difference between ON UPDATE foreign key actions and SQL triggers. An ON UPDATE action is only taken if the values of the parent key are modified so that the new parent key values are not equal to the old. For example:

5. CREATE, ALTER and DROP TABLE commands

This section describes the way the CREATE TABLE, ALTER TABLE, and DROP TABLE commands interact with SQLite’s foreign keys.

A CREATE TABLE command operates the same whether or not foreign key constraints are enabled. The parent key definitions of foreign key constraints are not checked when a table is created. There is nothing stopping the user from creating a foreign key definition that refers to a parent table that does not exist, or to parent key columns that do not exist or are not collectively bound by a PRIMARY KEY or UNIQUE constraint.

The ALTER TABLE command works differently in two respects when foreign key constraints are enabled:

It is not possible to use the «ALTER TABLE . ADD COLUMN» syntax to add a column that includes a REFERENCES clause, unless the default value of the new column is NULL. Attempting to do so returns an error.

If an «ALTER TABLE . RENAME TO» command is used to rename a table that is the parent table of one or more foreign key constraints, the definitions of the foreign key constraints are modified to refer to the parent table by its new name. The text of the child CREATE TABLE statement or statements stored in the sqlite_schema table are modified to reflect the new parent table name.

If foreign key constraints are enabled when it is prepared, the DROP TABLE command performs an implicit DELETE to remove all rows from the table before dropping it. The implicit DELETE does not cause any SQL triggers to fire, but may invoke foreign key actions or constraint violations. If an immediate foreign key constraint is violated, the DROP TABLE statement fails and the table is not dropped. If a deferred foreign key constraint is violated, then an error is reported when the user attempts to commit the transaction if the foreign key constraint violations still exist at that point. Any «foreign key mismatch» errors encountered as part of an implicit DELETE are ignored.

The intent of these enhancements to the ALTER TABLE and DROP TABLE commands is to ensure that they cannot be used to create a database that contains foreign key violations, at least while foreign key constraints are enabled. There is one exception to this rule though. If a parent key is not subject to a PRIMARY KEY or UNIQUE constraint created as part of the parent table definition, but is subject to a UNIQUE constraint by virtue of an index created using the CREATE INDEX command, then the child table may be populated without causing a «foreign key mismatch» error. If the UNIQUE index is dropped from the database schema, then the parent table itself is dropped, no error will be reported. However the database may be left in a state where the child table of the foreign key constraint contains rows that do not refer to any parent table row. This case can be avoided if all parent keys in the database schema are constrained by PRIMARY KEY or UNIQUE constraints added as part of the parent table definition, not by external UNIQUE indexes.

The properties of the DROP TABLE and ALTER TABLE commands described above only apply if foreign keys are enabled. If the user considers them undesirable, then the workaround is to use PRAGMA foreign_keys to disable foreign key constraints before executing the DROP or ALTER TABLE command. Of course, while foreign key constraints are disabled, there is nothing to stop the user from violating foreign key constraints and thus creating an internally inconsistent database.

6. Limits and Unsupported Features

This section lists a few limitations and omitted features that are not mentioned elsewhere.

No support for the MATCH clause. According to SQL92, a MATCH clause may be attached to a composite foreign key definition to modify the way NULL values that occur in child keys are handled. If «MATCH SIMPLE» is specified, then a child key is not required to correspond to any row of the parent table if one or more of the child key values are NULL. If «MATCH FULL» is specified, then if any of the child key values is NULL, no corresponding row in the parent table is required, but all child key values must be NULL. Finally, if the foreign key constraint is declared as «MATCH PARTIAL» and one of the child key values is NULL, there must exist at least one row in the parent table for which the non-NULL child key values match the parent key values.

SQLite parses MATCH clauses (i.e. does not report a syntax error if you specify one), but does not enforce them. All foreign key constraints in SQLite are handled as if MATCH SIMPLE were specified.

No support for switching constraints between deferred and immediate mode. Many systems allow the user to toggle individual foreign key constraints between deferred and immediate mode at runtime (for example using the Oracle «SET CONSTRAINT» command). SQLite does not support this. In SQLite, a foreign key constraint is permanently marked as deferred or immediate when it is created.

Recursion limit on foreign key actions. The SQLITE_MAX_TRIGGER_DEPTH and SQLITE_LIMIT_TRIGGER_DEPTH settings determine the maximum allowable depth of trigger program recursion. For the purposes of these limits, foreign key actions are considered trigger programs. The PRAGMA recursive_triggers setting does not affect the operation of foreign key actions. It is not possible to disable recursive foreign key actions.

Источник

I have two entities Entity1 and Entity2. When I’m running test case to create Entity1, It’s giving me the below error.

sqlite error 19 ‘foreign key constraint failed’

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Abp.Domain.Entities.Auditing;
using MyCompany.MyProject.Entity1s;
using MyCompany.MyProject.Business.Model.Entity2s;

namespace MyCompany.MyProject.Business.Model.Entity1s
{
    [Table("Entity1")]
    public class Entity1 : FullAuditedEntity
    {
        [ForeignKey("Entity2Id")]
        public virtual Entity2 Entity2 { get; set; }
        public virtual int Entity2Id { get; set; }
    }
}

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

using Abp.Application.Services.Dto;
using MyCompany.MyProject.Business.Dto.Entity1s;
using MyCompany.MyProject.Business.Dto.Entity2s;
using MyCompany.MyProject.Business.Services.Entity1s;
using MyCompany.MyProject.Business.Services.Entity2s;
using MyCompany.MyProject.Tests.AppTestBases;
using Shouldly;
using Xunit;

namespace MyCompany.MyProject.Tests.Entity1s
{
    public class Entity1AppService_Test : Entity1TestBase
    {
        public readonly IEntity2AppService _entity2AppService;
        public readonly IEntity1AppService entity1AppService;
		
        public Entity1AppService_Test()
        {
            entity1AppService = Resolve<IEntity1AppService>();
            _entity2AppService = Resolve<IEntity2AppService>();
        }

        #region Entity1 Tests
        [Fact]
        public async Task Should_Create_Entity1_With_Valid_Arguments()
        {
            var Entity1 = await CreateNewEntity1(K_TESTSKU1);
           Entity1.Id =  await _Entity1AppService.CreateEntity1(Entity1);

            UsingDbContext(context =>
            {
                context.Entity1.FirstOrDefault(
                    u => u.Entity1Name == Entity1.Entity1Name
                ).ShouldNotBeNull();
                context.Entity1PLU.FirstOrDefault(
                    u => u.Entity1Id == Entity1.Id).ShouldNotBeNull();
                context.Entity1Supplier.FirstOrDefault(
                    u => u.Entity1Id == Entity1.Id).ShouldNotBeNull();
            });
        }
		
        #endregion

        #region Private Methods

        private async Task<FetchEntity1> CreateNewEntity1(string sku)
        {
            FetchEntity2 entity2 = await entity2Base.AddEntity2ToDBAsync(entity2Base.CreateEntity2Entity(
                Entity2TestBase.K_TESTENTITYCODE1,
                Entity2TestBase.K_TESTDESC1
                ));

            return await AddEntity1ToDBAsync(CreateEntity1Entity(sku, entity2.Id));
		}
        #endregion
    }
}

namespace MyCompany.MyProject.Tests.AppTestBases
{
    public class Entity2TestBase : AppTestBase
    {
		public async Task<FetchEntity2> AddEntity2ToDBAsync(Entity2Input entity2Dto)
		{
			await _entity2AppService.CreateEntity2(entity2Dto);
			await CleanQueue();

			var entity2s = _entity2AppService.GetSearchEntity2(
				new FetchEntity2Input() { Entity2Code = entity2Dto.Entity2Code, LanguageCode = AppConsts.DefaultLanguageCode }
				);

			return entity2s.Items.First();
		}
	}
}

But When I replace method CreateNewEntity1 with the below method it works fine.

private async Task<Entity1DetailsDto> CreateNewEntity1(string sku)
{
	var entity2 = entity2Base.CreateEntity2Entity(
		Entity2TestBase.K_TESTENTITY2CODE1,
		Entity2TestBase.K_TESTDESC1
		);

	await _entity2AppService.CreateEntity2(entity2);

	var entity2List = _entity2AppService.GetSearchEntity2(new FetchEntity2Input()
	{ 
		Entity2Code = entity2.Entity2Code
	});
	
	return CreateEntity1Entity(sku, entity2List.Items.First().Id);

}

So why is it failing in the first case? Even if I’m passing the inserting the correct value of Entity2Id in the Entity1 table.
And why is it working if I ‘m calling _entity2AppService.CreateEntity2(entity2) inside Entity1AppService_Test class.
Is it related to Context? or this record is deleted for Entity2 when I’m returning from AddEntity2ToDBAsync method?

How can I verify the SQLite table records? Is there any way to check tables once test case is finished or during test case execution?

Привет, посетитель сайта ZametkiNaPolyah.ru! Продолжаем изучать базы данных и наше знакомство с библиотекой SQLite3. В реляционных базах данных, вернее, в реляционных СУБД есть множество приемов, которые позволяют обеспечить целостность данных, мы уже разобрались с ограничением уровня столбца и первичным ключом, который является одновременно и индексом таблицы базы данных, позволяющим значительно ускорить выборку данных из базы данных, и правилом, которое четко выполняет SQLite. В этой записи мы поговорим про еще одно ограничение уровня таблиц: внешний ключ или FOREIGN KEY.

Из этой записи вы узнаете как работает и для чего нужны внешние ключи в базах данных SQLite3. Познакомитесь с синтаксисом FROREIGN KEY в SQLite. А так же узнаете, как реализовать связи один ко многим и многие ко многим между таблицами базы данных под управлением SQLite при помощи внешних ключей. Но, стоит заметить, что вы сможете реализовать данные связи и без использования FOREIGN KEY, но в этом случае вы никак не обеспечите целостность данных в ваших таблицах.

Работа внешних ключей в базах данных SQLite: FOREIGEN KEY и REFERENCE в SQLite3

Внешний ключ или FOREIGN KEY – это ограничение уровня таблицы в реляционных базах данных, в том числе и в базе данных SQLite3. Внешние ключи определяют правила, по которым будут связаны таблицы в базах данных SQLite. Но, кроме того, что внешние ключи определяют то, как будут связаны таблицы в базах данных SQLite3, они еще нужны для обеспечения целостности данных в базах данных.

В SQLite внешний ключ объявляется при помощи конструкции FOREIGN KEY, а таблица, на которую ссылается внешний ключ указывается после ключевого слово REFERENCE. Обратите внимание: указывается не только таблица, но и столбец, на который идет ссылка. Ниже вы можете увидеть изображение, на котором показан синтаксис использования внешнего ключа в базах данных SQLite, вернее синтаксис конструкции REFERENCE.

Правила использования внешнего ключа не очень сложные, но давайте разберемся с тем, как реализован внешний ключ в SQLite3 и его конструкции: FOREIGEN KEY и REFEERENCE. Обратите внимание: когда вы связываете таблицы при помощи внешнего ключа одна таблица является родительской, а вторая таблица является дочерней. Внешний ключ всегда ссылается на родительскую таблиц, другими словами конструкция FOREIGN KEY и REFERENCE указывается в дочерней таблице.

Подведем итог: внешний ключ в базах данных SQLite необходим для реализации связей между таблицами. FOREIGEN KEY позволяет реализовывать связи между таблицами в базах данных. Конструкция REFERENCE используется для указания ссылки на родительскую таблицу. Внешний ключ обеспечивает целостность данных между двумя таблицами и необходим для нормализации базы данных. Вторая нормальная форма и третья нормальная форма не могут быть реализованы без внешнего ключа. Вернее будет сказать: мы можем организовать связи между таблицами без внешнего ключа, но проверка правил при этом выполняться не будет.

Давайте рассмотрим несколько практических примеров реализации внешнего ключа в базах данных SQLite. Мы реализуем два типа связи: связь один ко многим и связь многие ко многим.

Реализация связи один ко многим в базах данных SQLite. Пример связи один ко многим и FOREIGEN KEY в SQLite

Давайте реализуем связь один ко многим при помощи внешнего ключа, для этого воспользуемся конструкциями FORIGEN KEY и REFERENCE. Мы будем связывать при помощи внешнего ключа две таблицы: таблицу авторов и таблицу книг, поэтому давайте договоримся о допущение, что один автор может написать много книг, но у книги может быть только один автор.

Чтобы реализовать связь один ко многим, нам нужно воспользоваться конструкцией FOREIGEN KEY и REFERENCE при создании таблицы при помощи команды CREATE.

Здесь нам нужно дать пояснение к том, как мы создали внешний ключ для базы данных. Во-первых, в SQLite3 по умолчанию отключена поддержка внешних ключей, команда PRAGMA позволяет включить внешние ключи в базах данных SQLite. Во-вторых, помимо внешнего ключа наши таблицы имеют ограничения уровня столбца. Третье, столбец который ссылается и столбец, на который ссылается, должны иметь одинаковый тип данных, это закон реляционных баз данных.

Если столбцы будут иметь разные типы данных внешний ключ не будет работать. Скорее всего, другие СУБД вам даже не дадут возможность создать такой внешний ключ, но SQLite имеет динамическую типизацию данных, поэтому внешний ключ будет создан.

Далее: конструкция FOREIGEN KEY объявляет о том, что столбец auth_id является ссылкой, а конструкция REFERENCE указывает, что столбец auth_id является ссылкой на столбец id таблицы auth. Таким нехитрым образом мы реализовали связь один ко многим в базе данных SQLite при помощи внешнего ключа.

Давайте теперь добавим строки в таблицу auth нашей базы данных, это можно сделать командой INSERT:

Добавлять данные при создании внешнего ключа и использовании FOREIGN KEY в нашем случае удобнее сперва в ту таблицу, на которую идет ссылка. Теперь добавим строки в таблицу books и укажем значения для нашего внешнего ключа.

Поскольку связь один ко многим, по нашей договоренности: один автор может написать много книг, добавим еще одно произведение Джека Лондона:

А теперь давайте посмотрим, что нам дает связь один ко многим, реализованная благодаря внешнему ключу, и как действует ограничение FOREIGEN KEY.

Этот SQL запрос INSERT не будет выполнен в SQLite3, поскольку действует ограничение внешнего ключа, мы получим ошибку: Error: FOREIGN KEY constraint failed. В таблице справочнике с авторами нет значения id = 3, SQLite это проверил и сказал нам, что мы не правы, что у  него есть FOREIGN KEY, который ничего не знает про автора с id = 3. Так обеспечивается целостность данных и так мы избавляемся от аномалии добавления данных в базах данных.

В нашем случае, если мы попробуем передать внешнему ключу значение NULL, то строка в таблицу не добавится, так как действует ограничение уровня столбца NOT NULL, но если бы этого ограничения не было, SQLite спокойно и честно добавил значение NULL, как ссылку в таблицу books, это может нарушить целостность ваших данных, поэтому будьте аккуратны, но это нельзя считать ошибкой, например, есть книги, автор которых неизвестен.

Попробуем изменить данные в таблицах, связанных при помощи внешнего ключа. Для этого воспользуемся командой UPDATE:

SQLite не дала нам изменить значение auth_id на 4, так как в таблице auth нет авторов с id = 4 и это благодаря наличию внешнего ключа. Ошибка в SQLite будет содержать следующий текст: Error: FOREIGN KEY constraint failed. В дальнейшем мы рассмотрим каскадное обновление данных при помощи команды ON UPDATE.

Нам осталось использовать последнюю команду из группы манипуляции данными, чтобы убедиться, что внешние ключи в SQLite действительно обеспечивают целостность данных в базе данных. Попробуем удалить данные из таблицы, для этого есть команда DELETE.

Сделать у нас это не получится, потому что сработает ограничение внешнего ключа, мы не можем удалить данные из таблицы справочника авторов, пока на них ссылаются значения из таблицы книг. Ошибка будет следующей: Error: FOREIGN KEY constraint failed.

Чтобы удалить данные из справочника, мы сперва должны удалить данные из таблицы которая ссылается на справочник, давайте это реализуем.

Теперь мы удалили упоминание об авторе в таблице книг, а затем смогли удалить данные об авторе из таблицы auth, по-другому удалить данные из справочника мы не сможем: сперва удаляем данные из ссылающейся таблицы, а потом удаляем данные из таблицы, на которую ссылаемся – это правило внешнего ключа, которое обеспечивает целостность данных и защищает нас от аномалии удаления данных.

Про команду SELECT и то, как она работает с таблицами, которые используют внешние ключи, мы поговорим отдельно, так как это большая и требующая некоторых дополнительных усилий тема.

Сейчас нам важно понимать, что конструкция FOREIGEN KEY REFERENCE организует связь между таблицами базы данных, а также то, что внешний ключ очень тесно связан с понятием нормализации данных в базе данных. FOREIGEN KEY – это ограничения, которые заставляют SQLite проверять выполнение набора определенных правил, которые позволяют избежать аномалии модификации, удаления и обновления данных в таблицах базах данных.

Реализация связи многие ко многим в базах данных SQLite3

В принципе, мы уже сказали всё, что можно о внешних ключах в базах данных SQLite и разобрали особенности работы FOREIGEN KEY и REFERENCE в SQLite3. Раньше мы намеренно упрощали примеры, когда говорили про третью нормальную форму и когда разговаривали про особенности внешних ключей.

Давайте теперь исправим это упущение и будем считать, что одну книгу может написать много авторов и один автор может написать много книг. Реализуем связь многие ко многим в базах данных SQLite3 при помощи внешнего ключа.

Вы, наверное, помните, что для реализации связи многие ко многим нам необходимо создавать третью таблицу, ее можно назвать результирующей, а можно назвать и промежуточной. Мы назовем эту таблицу auth_book.

Никаких особых хитростей при реализации связи в базах данных SQLite, да и любых других реляционных базах данных нет. Мы создали третью таблицу, в которой есть два столбца и оба этих столбца являются внешними ключами. Столбец auth_id является ссылкой на таблицу авторов, столбец books_id – это ссылка на таблицу книг. Добавляем данные в таблицы командой INSERT.

Мы подготовили таблицы, которые, на первый взгляд никак не связаны между собой, теперь давайте свяжем их связью многие ко многим, добавив нужные значения в столбцы FOREIGEN KEY таблицы auth_book.

Хочу обратить ваше внимание на то, что SQLite и слышать не слышала про Льва Толстого, Джека Лондона и Ильфа с Петровым, она лишь проверяет работоспособность ссылок, ответственность за наполнение таблиц лежит на операторе. При таком подходе вы легко можете сказать, что Джек Лондон написал Войну и мир:

С точки логики SQLite никакой ошибки нет, с точки зрения человека – ошибка есть. Внешние ключи никак не могут проверить смысл, который заложен в данных, они лишь проверяют и ограничивают логику работы базы данных. Но не все так страшно, когда мы начнем работать с триггерами, мы посмотрим, как при помощи триггера сделать так, чтобы результирующая таблица при организации связи многие ко многим с помощью FOREIGN KEY заполнялась автоматически, это будет дополнительным уровнем обеспечения целостности данных в базах данных.

Немного о логике внешних ключей в SQLite

Когда мы рассматривали третью и вторую нормальные формы, мы ничего не упоминали про внешние ключи и про то, что они реализуют связи между таблицами. Тогда мы говорили, что справочник является дочерней таблицей, а вот таблица со ссылками (со столбцом FOREIGEN KEY) является родительской. С одной стороны, мы правильно говорим, до тех пор, пока не сталкиваемся со связью многие ко многим, ведь нельзя же результирующую таблицу называть родительской…

Поэтому в базах данных SQLite разработчики четко обозначили терминологию:

1. Родительская таблица — это таблица, на которую ссылается внешний ключ (FOREIGN KEY).
2. Дочерняя таблица — это таблица, к которой применяется ограничение внешнего ключа.
3. Родительский ключ — это один или несколько столбцов родительской таблицы, на которые ссылается внешний ключ (FOREIGN KEY).
4. Дочерний ключ — это один или несколько столбцов дочерней таблицы, значения которых ограничиваются внешним ключом и которые перечисляются после ключевой фразы REFERENCES.

На самом деле, вам нужно выбрать ту терминологию, которую вам легче воспринимать, мне легче воспринимать так: таблица справочник – дочерняя таблица, таблица, которая ссылается на справочник – родительская таблица.

Важно, чтобы другие вас понимали, благо, язык SQL универсальный и все понимают, что FOREIGN KEY – это внешний ключ, который обеспечивает целостность данных. FOREIGN KEY, как и первичный ключ или PRIMARY KEY, имеет синтаксис, который позволяет записывать данное ограничение, как ограничение уровня столбца.