Doubt whether to use composite primary key or not in associative table
I'm making a relationship where the same is N: N...
Example:
Project X Official
In this relationship I create an associative table called Projeto_Funcionario taking the id of the two classes.
Project (id, Name)
Official (id, Name, Position)
Project_functionary (project id, idFunc , hourly load)
The doubt is: I would like to know if the keys idproject and idFunc are composite primary keys or not, or just create them being foreign and before it create a primary.
Project_functionary (id, project id, idFunc , hourly load)
Note: the employee cannot work on the same project more than once, only a single time.
2 answers
This is at your discretion.
Both ways are right, but I find it quite boring to work with composite primary keys, because to reference them by foreign key, you need both columns. Imagine the chaos of adding a new primary column to that table? You will need to add it in all other tables that depend on the old key.
Also gives more work to be passing two variables in the client code, so I prefer to do this shape:
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 uniqueness guarantee you would have with a primary key, and SQL Server still creates an index for those 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.
Do not generate composite key, one way to stop the insertion of duplicate items is by creating index.
The code would be more or less 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 id and throw an exception. However, I believe that even with the addition of index it would be better to do a validation before trying to enter the information.
EDIT
Follows 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; }
}