Foreign Key Column Mapped to Multiple Primary Keys

Foreign Key column mapped to multiple primary keys

Best practice I have found is to create a Function that returns whether the passed in value exists in either of your Messages and Drafts PK columns. You can then add a constraint on the column on the History that calls this function and will only insert if it passes (i.e. it exists).

Adding non-parsed example Code:

CREATE FUNCTION is_related_there (
IN @value uniqueidentifier )
RETURNS TINYINT
BEGIN
IF (select count(DraftId) from Drafts where DraftId = @value + select count(MessageId) from Messages where MessageId = @value) > 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;

ALTER TABLE History ADD CONSTRAINT
CK_HistoryExists CHECK (is_related_there (RelatedItemId) = 1)

Hope that runs and helps lol

Entity mapping for one to many with multiple foreign keys as composite primary key

This is what the JPA spec calls a "derived identity". You should define your classes slightly differently. Here are the salient bits of code:

@Entity
Class A {

    @Id
    @Column(name="A_ID")
    private Long id;

    @OneToMany(
        cascade = CascadeType.ALL,
        orphanRemoval = true,
        mappedBy="a"
    )
    Collection<C> values;

    ...
}

@Entity
Class B {

    @Id
    @Column(name="B_ID")
    private Long id;

    ...
}

@Entity
Class C {

    @EmbeddedId
    private CId cId;

    @ManyToOne
    @JoinColumn(name="A_ID")
    @MapsId("aId") // maps aId attribute of embedded id
    private A a;

    @ManyToOne
    @JoinColumn(name="B_ID")
    @MapsId("bId") // maps bId attribute of embedded id
    private B b;

    ...
}

@Embeddable
Class CId {

    private Long aId; // corresponds to PK type of A

    private Long bId; // corresponds to PK type of B

    ...
}

Derived identities are discussed in the JPA 2.1 spec in section 2.4.1.

Composite primary key of two foreign keys

If you have multiple ids in a entity , you should use EmbeddedId. Here both of your Ids are foreign key constraints , so you need to use @JoinColumn to join them. Sample code for classificacao class and classificacaoId class.

Classificacao.java

@Entity
public class Classificacao implements Serializable {

    @Id
    ClassificacaoId id;

    String name;
    // getter and setter

}

ClassificacaoId.java

@Embeddable
public class ClassificacaoId implements Serializable {

    @OneToOne
    @JoinColumn(name = "userId", referencedColumnName = "userId")
    private Usuario usuarioIo;

    @ManyToOne
    @JoinColumn(name = "tempId", referencedColumnName = "id")
    private Temporada temporada;

    // getter and setter

}

Generated SQL in h2 db

create table classificacao (
    name varchar(255), 
    user_id varchar(255) not null, 
    temp_id bigint not null, 
    primary key (temp_id, user_id)
);
create table temporada (
    id bigint not null, 
    name varchar(255), 
    primary key (id)
);
create table usuario (
    user_id varchar(255) not null, 
    name varchar(255), 
    primary key (user_id)
);
alter table classificacao add constraint FK1najxwr5x189iul4rguqc4wyx 
foreign key (user_id) references usuario;
alter table classificacao add constraint FKlvk517howhduqt5ghb4mgx0ko 
foreign key (temp_id) references temporada;

Mapping composite foreign key to composite primary key where the foreign key is also a primary key

The main difference between your question and the one I suggested as duplicate is that your ForeignKey attributes don't refer -

• from a primitive property to a navigation property
• from a navigation property to a primitive property

In your case, the reference is from a primitive property to another primitive property, in another type. Also, little detail, VirtualMachine.Datetime should be a property, not a member. But I have to admit that the "duplicate" didn't cover your case.

So let's try to make this into a comprehensive answer how to handle this situation in Entity Framework 6. I'll use an abstracted model to explain the various options:

public class Parent
{
    public int Id1 { get; set; } // Key
    public int Id2 { get; set; } // Key   
    public string Name { get; set; }   
    public virtual List<Child> Children { get; set; }
}

public class Child
{
    public int Id1 { get; set; } // Key
    public int Id2 { get; set; } // Key
    public int Id3 { get; set; } // Key
    public string Name { get; set; }
    public virtual Parent Parent { get; set; }
} 

There are three options to setup the mappings.

Option 1

Data annotations, ForeignKey attribute:

public class Parent
{
    [Key]
    [Column(Order = 1)]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id2 { get; set; }
    
    public string Name { get; set; }

    public virtual List<Child> Children { get; set; }
}

public class Child
{
    [Key]
    [Column(Order = 0)]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 1)]
    public int Id2 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id3 { get; set; }

    public string Name { get; set; }

    [ForeignKey("Id1,Id2")]
    public virtual Parent Parent { get; set; }
}

As you see, here the ForeignKey attribute refers from a navigation property to primitive properties. Also, the absolute numbers in the column order don't matter, only their sequence.

Option 2

Data annotations, InverseProperty attribute:

public class Parent
{
    [Key]
    [Column(Order = 1)]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id2 { get; set; }
    
    public string Name { get; set; }

    public virtual List<Child> Children { get; set; }
}

public class Child
{
    [Key]
    [Column(Order = 0)]
    [InverseProperty("Children")]
    public int Id1 { get; set; }
    [Key]
    [Column(Order = 1)]
    [InverseProperty("Children")]
    public int Id2 { get; set; }
    [Key]
    [Column(Order = 2)]
    public int Id3 { get; set; }

    public string Name { get; set; }

    public virtual Parent Parent { get; set; }
}

InverseProperty points from one or more properties in a type at one end of a relationship to a navigation property in the type on the other end of the relationship. Another way to achieve the same mapping is to apply [InverseProperty("Parent")] on both key properties of Parent.

Option 3

Fluent mapping:

modelBuilder.Entity<Parent>().HasKey(p => new { p.Id1, p.Id2 });
modelBuilder.Entity<Child>().HasKey(p => new { p.Id1, p.Id2, p.Id3 });
modelBuilder.Entity<Parent>()
    .HasMany(p => p.Children)
    .WithRequired(c => c.Parent)
    .HasForeignKey(c => new { c.Id1, c.Id2 });

As said in the comments, fluent mapping is less error-prone than data annotations. Data annotations offer too many options to configure mappings and it's not always easy to see which parts are connected. That's why fluent mapping is my favorite.

Entity Framework Core

In EF-core (current version 3.1.6) composite primary keys can't be modeled by data annotations. It throws a run-time exception:

Entity type 'Parent' has composite primary key defined with data annotations. To set composite primary key, use fluent API.

So for EF-core only option 3 is feasible. The mapping is almost identical:

modelBuilder.Entity<Parent>().HasKey(p => new { p.Id1, p.Id2 });
modelBuilder.Entity<Child>().HasKey(p => new { p.Id1, p.Id2, p.Id3 });
modelBuilder.Entity<Parent>()
    .HasMany(p => p.Children)
    .WithOne(c => c.Parent) // Different here
    .HasForeignKey(c => new { c.Id1, c.Id2 });

SQLAlchemy multiple foreign keys in one mapped class to the same primary key

Tried removing quotes from the foreign_keys and making them a list. From official documentation on Relationship Configuration: Handling Multiple Join Paths

Changed in version 0.8: relationship() can resolve ambiguity between
foreign key targets on the basis of the foreign_keys argument alone;
the primaryjoin argument is no longer needed in this situation.

---

Self-contained code below works with sqlalchemy>=0.9:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(u'sqlite:///:memory:', echo=True)
session = scoped_session(sessionmaker(bind=engine))
Base = declarative_base()

#The business case here is that a company can be a stakeholder in another company.
class Company(Base):
    __tablename__ = 'company'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)

class Stakeholder(Base):
    __tablename__ = 'stakeholder'
    id = Column(Integer, primary_key=True)
    company_id = Column(Integer, ForeignKey('company.id'), nullable=False)
    stakeholder_id = Column(Integer, ForeignKey('company.id'), nullable=False)
    company = relationship("Company", foreign_keys=[company_id])
    stakeholder = relationship("Company", foreign_keys=[stakeholder_id])

Base.metadata.create_all(engine)

# simple query test
q1 = session.query(Company).all()
q2 = session.query(Stakeholder).all()

---

Related Topics