Search code examples
mysqllaravellaravel-socialite

Add Unique to 3 columns at the same time

i'm having a table called social_accounts to store the data for OAuth Login 

id
user_id
provider
provider_id

I want to make user_id,provider,provider_id UNIQUE but i want all 3 columns be unique at the same time meaning a user with id x can have a provider_id b and provier google for example but this row didn't duplicate but the use x can have another row with provider faceook as an example

Solution

  • Column provider seems to be redundant here. You should have a separate table with provider_id and provider

    To set a UNIQUE index on multiple columns try the following:

    ALTER TABLE `social_accounts` ADD UNIQUE `unique_index`(`user_id`, `provider_id`);

    However if you want to stick to your approach having provider in the table so use this one:

    ALTER TABLE `social_accounts` 
ADD UNIQUE `unique_index`(`user_id`, `provider_id`, `provider`);

    Also note that unique_index is the name of your index and can be name how ever you want.