How to create a foreign key in a table where data already exists, in the EntityFramework with Code-First?

4

I have a system already in production from which the customer has requested a modification.

Modification is the creation of an EMPLOYEE table that will relate to the DRIVE table, creating a 1: N relationship (One Employee, multiple DRIVES).

I know how to do the relationship when the table is empty. But in this case, since it is an EVOLUTIVE, the MOVEMENT table already has records. In theory, when creating the foreign key, this field could be null for the old data. However, the give the UPDATE-DATABASE, SQL returns the error the following error:

  

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.TB_MOVIMENTACAO_dbo.TB_FUNCIONARIO_Id_Funcionario". The   conflict occurred in database "FieldService_Desenv", table   "dbo.TB_FUNCIONARIO", column 'Id'.

How to outline this problem? What do you do in this case?

    
asked by anonymous 10.04.2015 / 22:37

1 answer

6

There are three steps.

1. Create relationship by allowing null

The Movimentacao entity would look like this:

public class Movimentacao
{
    public int MovimentacaoId { get; set; }
    public int? FuncionarioId { get; set; }

    // Coloque aqui os outros campos da sua movimentação

    public virtual Funcionario Funcionario { get; set; }
}

Funcionario , like this:

public class Funcionario 
{
    public int FuncionarioId { get; set; }

    // Coloque aqui os outros campos de seus funcionários

    public virtual ICollection<Movimentacao> Movimentacoes { get; set; }
}

Create a Migration through the Package Manager Console :

  

Add-Migration OfficialsMovements

Soon after:

  

Update-Database

Upload the change to your database.

2. Updates

Create the employees and right after you create the SQL statements that update FuncionarioId to Movimentacoes . Make sure no FuncionarioId is null.

3. Modify FuncionarioId

Change Movimentacao making FuncionarioId non-null:

public class Movimentacao
{
    public int MovimentacaoId { get; set; }
    public int FuncionarioId { get; set; } // Deixou de ser int?

    // Coloque aqui os outros campos da sua movimentação

    public virtual Funcionario Funcionario { get; set; }
}

Create another Migration :

  

Add-Migration MovementsFuntionaryIdNaoNulo

Soon after:

  

Update-Database

    
10.04.2015 / 23:44