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

Question

I have a system already in production of which the client requested a modification.

The modification is the creation of an EMPLOYEES table that will relate to the MOVEMENT table, creating a 1:N relationship (One Employee, several MOVEMENTS).

I know how to do the relationship when the table is empty. But in this case, as 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, giving the UPDATE-DATABASE, SQL returns the error the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY Constraint "Fk_dbo.Tb_mover_dbo.Tb_funcio_id_funcionario". The Conflict occurred in database "Fieldservice_desenv", table "dbo.TB_FUNCIONARIO", column 'Id'.

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

Answer 1

It’s three steps.

1. Create relationship allowing null and void

The entity Movimentacao 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, thus:

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 Functionalities

Right after:

Update-Database

Upload the change to your database.

2. Updates

Create employees and right after create SQL sentences that update FuncionarioId in Movimentacoes. Make sure no one FuncionarioId is void.

3. Amend FuncionarioId

Alter Movimentacao making FuncionarioId nonzero:

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 one more Migration:

Add-Migration Movementworkspace

Right after:

Update-Database