Doubt between using compound primary key or not in associative table

2

I'm making a relationship where it's N: N ...

Example:

  

Project x Employee

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

  

Project (id, name)

     

Official (id, name, title)

     

Functional_Project ( idProject , idFunc , loadHoraria)

The question is: I would like to know if the idProject and idFunc keys are composite primary keys or not, or just create them as foreign keys and create a primary one beforehand.

  

idFunc , idFunc , loadTime)

OBS: The Employee can not work on the same project more than once, just once.     

asked by anonymous 19.02.2018 / 17:24

2 answers

3

This is at your discretion.

Both ways are fine, but I find it annoying to work with composite primary keys, because to refer to them as 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's also harder to get two variables in the client code, so I'd rather do it like this:

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 UNIQUE CONSTRAINT, you have the same uniqueness assurance you would have with a primary key, and SQL Server still creates an index for these columns.

Now, when trying to do an INSERT on this table, linking the same employee with the same project, the DBMS will point to the violation error of CONSTRAINT.

-- 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 in SQL SQL Fiddle .

    
19.02.2018 / 17:46
1

Do not generate composite key, one way to lock 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 duplicate project% and employee% and will throw an exception. However, I believe that even with the addition of id it would be better to do a validation before attempting to enter the information.

EDIT

Here's how I'd 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; }
}
    
19.02.2018 / 17:40