1

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

coderX
  • 183
  • 1
  • 14

1 Answers1

0

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)
Mansueli
  • 6,223
  • 8
  • 33
  • 57
  • Hi @Mansueli, Thanks for the reply. I tried to execute these SQL queries. The first 2 lines were executed successfully. But I am getting error for next 2 lines ```ALTER TABLE profile ALTER COLUMN id TYPE uuid;``` Failed to run sql query: identity column type must be smallint, integer, or bigint – coderX Oct 22 '22 at 06:37
  • 1
    You'll need to drop the column and recreate it as UUID. I will update the answer. – Mansueli Oct 24 '22 at 21:34