Making two columns unique in combination (or a composite primary key).

I have the following Models:

Opportunity Vote User

A User can only have one Vote on an Opportunity.

I just went to change my auto-incrementing Vote Id field to a composite key made up of the User Id and the Opportunity Id like so: $table->primary('user_id', 'opportunity_id');

Having done this my seeder fails like so: SQLSTATE[HY000]: General error: 1 foreign key mismatch - "comments" referencing "votes" (Connection: sqlite, SQL: insert into "comments" ("vote_id", "comment", "updated_at", "created_at") values (3, Occaecati dolor et nihil saepe. Quae dolores odio libero eos et consequuntur occaecati qui. Velit et recusandae sapiente id., 2024-09-21 16:10:08, 2024-09-21 16:10:08))

My question is how to I best constrain the Vote to only have a single vote on each opportunity per user?

I could start to handle the primary ID fully but I'm starting to consider that editing with the structure may be a pain to deal with the composite key (trying to keep it simple) and seeing mixed reviews on the usefulness of composite keys.

What are the thoughts here? Ideally I would use updateOrCreate and it would know that this updated vote (when someone changes their score) would update the previous vote on the same opportunity.

Thanks in advance.

LaryAI

1 year ago

Best Answer

Level 58

To ensure that a user can only have one vote per opportunity, you can use a unique constraint on the combination of user_id and opportunity_id instead of making them a composite primary key. This approach simplifies the database schema and avoids the issues you're encountering with foreign key constraints.

Here's how you can achieve this in your migration file:

Create the Votes Table with a Unique Constraint:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateVotesTable extends Migration
{
    public function up()
    {
        Schema::create('votes', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('opportunity_id');
            $table->integer('score'); // Assuming you have a score field
            $table->timestamps();

            // Add foreign key constraints
            $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
            $table->foreign('opportunity_id')->references('id')->on('opportunities')->onDelete('cascade');

            // Add unique constraint on user_id and opportunity_id
            $table->unique(['user_id', 'opportunity_id']);
        });
    }

    public function down()
    {
        Schema::dropIfExists('votes');
    }
}

Update or Create Vote:

You can use the updateOrCreate method in your model to ensure that a vote is either updated or created if it doesn't exist:

use App\Models\Vote;

public function castVote($userId, $opportunityId, $score)
{
    Vote::updateOrCreate(
        ['user_id' => $userId, 'opportunity_id' => $opportunityId],
        ['score' => $score]
    );
}

Handling Foreign Key in Comments Table:

If you have a comments table that references the votes table, ensure that the vote_id column is properly set up as a foreign key:

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class CreateCommentsTable extends Migration
{
    public function up()
    {
        Schema::create('comments', function (Blueprint $table) {
            $table->id();
            $table->unsignedBigInteger('vote_id');
            $table->text('comment');
            $table->timestamps();

            // Add foreign key constraint
            $table->foreign('vote_id')->references('id')->on('votes')->onDelete('cascade');
        });
    }

    public function down()
    {
        Schema::dropIfExists('comments');
    }
}

By using a unique constraint on the combination of user_id and opportunity_id, you ensure that each user can only vote once per opportunity. This approach avoids the complexity of composite primary keys and makes it easier to manage foreign key relationships.