no unique constraint matching

Hey guys. Trying to stablish a foreign key relationship between two tables (Plans.quantity < Subscription.quantity), but getting the

"Failed to update column "quantity": Failed to run sql query: there is no unique constraint matching given keys for referenced table "subscriptions" error message.

Here is a snapshot of my db schema. I just want the quantity (or seats) of this tiny SaaS to be updated via the Stripe's Webhook. :supabase:

garyaustin•9/4/22, 2:28 PM

Can you clarify what you are trying to do. Foreign keys normally point to the primary key of the other table (or at least a unique column). You have subscription_id which I assume is a fk to the subscriptions table which would establish a relationship. Quantity does not seem to be a candidate for a foreign key unless it means something different than the usual definition as it would not be unique.

amorenvaOP•9/4/22, 3:03 PM

Right, I want the Plans table to show how many seats were bought by the organization. Do you think the logic is incorrect? should it be only shown in the Subscriptions table? (managed by the Stripe's webhook). I highlighted in the red rectangle the bare minimum schema I want for this app:

users belong to one or more organizations, organizations buy plans, plans contain the features organizations can access to.

Thank you so much for your time Gary! :supabase:

garyaustin•9/4/22, 3:05 PM

If you don't want to update quantity in both tables, then you use a join to get the data out of subscriptions you need to finish up plans. You would not have the number in plans. Or do it the opposite way which ever makes sense.

no unique constraint matching

Similar Threads

Similar Threads

Similar Threads