https://supabase.com/ logo
Join Discord
Powered by
Getting mixed results when creating foreign keys
# help
j
Getting mixed results when creating foreign keys
Admittedly new to supabase and postgres. Maybe I just need to go back to mongo. In the web table editor, I go to link a table column to another table's 
id
column, click save twice, it says it successfully updated. But when I go into edit the table again, the work is gone. However, I have gotten another fk to work - to a non 
id
column (but still primary). Any experience with this? I searched this server, but couldn't find anything similar. Maybe I didn't look hard enough. Reviewed some postgres docs, and it didn't seem to have any constraints I am bumping up against.
d
I actually don't use the Supabase UI and I'm not that comfortable with sql either. What helped me was using something like postico or datagrip
j
@User so I installed postico. When I try to add/save the foreign key, it says 
must be owner of table components
. Which is weird, because I'm using the 
postgres
user login from supabase. I assume that IS the owner. You ever run into this?
I didn't think it would be this difficult to mess with foreign keys lol
d
What schema is components in? And I think in postico on the bottom right you'll see something like SQL preview before you click save changes. Could you post that here?
j
public schema
Copy code
ALTER TABLE "public"."components" ADD FOREIGN KEY ("subscribers") REFERENCES "public"."subscribers"("id") ON DELETE CASCADE;
d
Lmao that is very weird. Okay, this might be a pain, but what if you delete the table via Supabase UI, and remake it via postico?
Idk if you have data in their already
You can also do something like ...
j
no data. I'll try
d
Altar table public.componenets OWNER TO postgres My understanding is that when you're using the Supabase UI it's not using the postgres owner. But I could be wrong
select tablename, tableowner from pg_catalog.pg_tables where schemaname = ‘public’ ; That'll tell you the owners of the tables
j
supabase_admin psh
thanks
As it turns out, I must be trying to use foreign keys wrong anyway - based on an error I got when creating the table via portico. In this case, a 'component' can have many 'subscribers'. So in the components table, the 'subscribers' column would be an array of ids from the subscribers table.
I guess an fk must be a one to one relationship??
d
If a component can have subscribers, that wording makes it seem like there should be a component_id column in subscribers
And that component_id is a fk to the component table
The fk goes on the child, or the "many" table
j
gotcha. but a subscriber can subscribe to many components as well. guess I'll just make it an array and "push" the IDs in. No foreign key.
PreviousNext
Powered by