Clash Royale CLAN TAG#URR8PPP

How a foreign key refer multiple primary keys

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();
);



posts(id, title, descr, content_type)

content_type

'post'

'answer'

2 Answers
2

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'))






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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.