Doubt between using composite primary key or not in the associative table

Question

I’m making a relationship where the same is N:N...

Example:

Projeto x Funcionario

In this relationship I create an associative table called Projeto_Funcionario leading the id of the two classes.

Project(id, name)

Employee(id, name, position)

Project_funcionario(idProject, idFunc, cargaHoraria)

The question is: would you like to know if the idProjet and idFunc keys are composite primary keys or not, or Penos create them being foreign and before it create a primary.

Project_funcionario(id, idProject, idFunc, cargaHoraria)

OBS: The Employee cannot work on the same project more than once, only once.

Answer 1

That’s up to you.

Both ways are right, but I find it really boring to work with composite primary keys, because to reference them by a foreign key, you need both columns. Imagine the chaos to add a new primary column to this table? You will need to add it to all other tables that depend on the old key.

It also takes more work to pass two variables in the client code, so I prefer to do it this way:

CREATE TABLE Projeto_Funcionario (
    id INT IDENTITY (1, 1),
    idProjeto INT NOT NULL,
    idFunc INT NOT NULL,

    CONSTRAINT PK_Projeto_Funcionario PRIMARY KEY (id),
    CONSTRAINT UNIQUE_Projeto_Funcionario UNIQUE (idProjeto, idFunc),

    /* chaves estrangeiras... */
)

With a CONSTRAINT of type UNIQUE, you have the same guarantee of uniqueness that you would have with a primary key, and SQL Server even creates an index for these columns.

Now, when trying to make an INSERT in this table, linking the same employee with the same project, the DBMS will point out CONSTRAINT violation error.

-- esse primeiro insert funciona
INSERT INTO Projeto_Funcionario (idProjeto, idFunc)
VALUES (1, 1)

-- esse não
INSERT INTO Projeto_Funcionario (idProjeto, idFunc)
VALUES (1, 1)

-- erro: Violation of UNIQUE KEY constraint 'UNIQUE_Projeto_Funcionario'. Cannot insert duplicate key in object 'dbo.Projeto_Funcionario'. The duplicate key value is (1, 1).

See working on SQL Fiddle.

Answer 2

Do not generate composite key, a way to lock the insertion of duplicate items is by creating index.

The code would look something like this:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
      modelBuilder.Entity<Projeto_Funcionario>().Property(a => a.idProjeto).HasColumnAnnotation(IndexAnnotation.AnnotationName,
                           new IndexAnnotation(new IndexAttribute("IX_PROJETO_FUNCIONARIO_UNIQUE", 1) { IsUnique = true }));

      modelBuilder.Entity<Projeto_Funcionario>().Property(a => a.idFunc).HasColumnAnnotation(IndexAnnotation.AnnotationName,
                           new IndexAnnotation(new IndexAttribute("IX_PROJETO_FUNCIONARIO_UNIQUE", 2) { IsUnique = true }));
}

With this setting it will block the insertion of id duplicated project and employee and will release an exception. However, I believe that even with the addition of the index it would be better to do a validation before trying to enter the information.

EDIT

Follow how I would leave the class:

public class Projeto_Funcionario
{
    public int Id { get; set; }

    public DateTime CargaHoraria { get; set; }

    public int IdProjeto { get; set; }
    public Projeto Projeto { get; set; }

    public int IdFunc { get; set; }
    public Funcionario Funcionario { get; set; }
}