-1

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

Youssef mahmoed
  • 373
  • 2
  • 9

1 Answers1

1

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.

B001ᛦ
  • 2,036
  • 6
  • 23
  • 31