How a foreign key refer multiple primary keys

Question

I am working on a system in Laravel in which user can post, answer and vote on post or answer.

One way to do that is making separate vote tables for that but i wanted to do that with one table as votes.

I want that content_id in votes table should refer to two primary keys as posts.id and post-answers.id

If that solution is not possible then suggest an alternate solution for that. Thanks in advance.

I tried to make this migration but to no avail the table is created successfully but foreign key just pointing only one primary key.

public function up()
{
    Schema::create('contentvotes',function(Blueprint $table){
        $table->increments('id');
        $table->enum('content_type',['post','post_answer']);
        $table->integer('user_id')->unsigned();
        $table->foreign('user_id')->references('id')->on('users');
        $table->integer('content_id')->unsigned();
        $table->foreign('content_id')->references('id')->on('posts');
        $table->foreign('content_id')->references('id')->on('postanswers');
        $table->boolean('status');
        $table->timestamps();
    });
}

The laravel way would be to use [Polymorphic Relations](https://laravel.com/docs/5.6/eloquent-relationships#polymorphic-relations). But personally I don't like them, since data integrity is not maintained by the DB. If you want a DB solution - search for "*table inheritance*". Or just use one table for posts and answers. — Paul Spiegel, Aug 11 '18 at 12:04
I mean **one** table for both: `posts(id, title, descr, content_type)`. While `content_type` is either `'post'` or `'answer'`. — Paul Spiegel, Aug 11 '18 at 12:17
MySQL doesn't support polymorphic foreign keys: https://stackoverflow.com/q/441001/4848587 — Jonas Staudenmeir, Aug 11 '18 at 15:41
Taking a step back for a second, how is a post answer associated to a post? Seems like post_answers needs a post_id so that you can have a one to many relationship between posts and post_answers. — Nate, Aug 12 '18 at 05:58

score 0 · Answer 1 · answered Aug 11 '18 at 16:31

for me, i will do like this. not the best practices but it is the possible way

public class Model {
   ...
   ...

   protected $appends = ['post', 'post_answer']   

   public class getPostAttribute()
   {
        return Post::find($this->attribute('content_id'))
   }

   public class getPostAnswerAttribute()
   {
        return PostAnswer::find($this->attribute('content_id'))
   }


}

score 0 · Answer 2 · answered Aug 12 '18 at 05:38

One foreign key cannot reference to primary keys from multiple tables.

For example, you may have Post A and Post Answer B with same ID, how the database knows which ID it is for.

I noticed that you have a content_type column and I believe you have realized this issue. You content_type and content_id is actually a composite foreign key. Again it can reference to one table.

Solution: you need introduce polymorphism. You can merge the post and postanswer to one table and add a type column.

To minimize the effort for data migration, your primary key can be Id and type. Otherwise, ID is a better primary key.

How a foreign key refer multiple primary keys

2 Answers2