CODEWITHSUNDEEP
javascriptsqlpostgresqlsupabasesupabase-database
coderX
coderX

Reputation: 223

Unable to add foreign key in Supabase

I am following Build a SaaS product with Next.js, Supabase and Stripe course by Jon Meyers. I am facing issue in Add Relationships Between Tables in Supabase Using Foreign Keys part. I have created a table called profile where id and created_at were auto generated column and is_subscribed and interval are defined by me, I have to add a foreign key relationship with auth.users table with the id column of profile table which I guess is managed by Supabase under the hood. Can someone please help me fix this. Thanks

enter image description here

Upvotes: 2

Views: 3615

Answers (1)

Mansueli
Mansueli

Reputation: 6994

The main issue is that you created the profile table with ID as bigint instead of UUID. You can change this with the follow commands in SQL Editor:

-- Dropping the primary key to change it:
ALTER TABLE public.profiles 
DROP CONSTRAINT profile_pkey;
--Dropping & recreating the column as UUID:
ALTER TABLE profiles
DROP COLUMN id;
ALTER TABLE profiles
ADD COLUMN id uuid;
--Adding the primary key back:
ALTER TABLE public.profiles 
ADD PRIMARY KEY (id);
-- Setting the foreign key relationship:
ALTER TABLE public.profiles 
ADD FOREIGN KEY (id) REFERENCES auth.users(id)

Upvotes: 2

Related Questions