Setup a Foreign Key relationship between PostgreSQL tables

Jon Meyers
InstructorJon Meyers
Share this video with your friends

Social Share Links

Send Tweet
Published a year ago
Updated a year ago

Supabase uses the auth.users table to store information about our users and their sessions. In this lesson, we add a column to the tweets table to associate each tweet with one of our users.

Additionally, we set up cascading deletes, so when a user is deleted from the auth.users table, their tweets are also deleted.

Lastly, because our database schema has changed, our TypeScript definitions are now incorrect. Therefore, we use the Supabase CLI to introspect our PostgreSQL schema and create a new database.types.ts file.

Code Snippets

Add foreign key relationship to auth.users

alter table public.tweets
add user_id uuid references auth.users on delete cascade not null;

Generate TypeScript definitions

npx supabase gen types typescript --project-id your-project-id > lib/database.types.ts

Resources

Instructor: Now that our users can sign in, let's associate each one of these tweets with their author. Back over in the Supabase dashboard, if we go to the Table Editor, we can see this tweets table that we created. If we go over to Authentication and Users, we can see there's a list of users being stored somewhere, but they're not listed here next to our tweets table in the Table Editor.

That's because by default, any tables that we create from the Supabase dashboard are in the public schema. If we open this dropdown list, we can see a list of these protected schemas which are associated with supabase features like graphql, realtime or things that might be needed by other extensions in our project.

We're looking for a list of users. This would probably come under auth. Now we can see a list of tables that are associated with auth. At the bottom, we have our users table. There's our user's ID, and then all the information that supabase uses to manage our user's session. Let's associate each of our tweets with the user that created them.

If we change this back to the public schema, and then go to our tweets table, we can click here to add a new column. The column_name is going to be user_id. Then we want to click this Add foreign key relation button. This lets us set up a relationship between two tables. We can say the user_id column of the tweets table is a link to the auth schema.

Then the users table. The column we want to reference from the auth.users table is the id column. Then here we can set up an action for, if the user is removed from this auth.users table, what do we want to do with their tweets? We can either have No action, which will raise an error when trying to delete that user if that user has any tweets.

Our other options are Cascade, Restrict, Set default or Set Null. In this case, we want Cascade, which means, deleting a record from the auth.users table will also delete any records that reference it in this table. Essentially, we delete the user, Cascade will automatically delete their tweets. Let's click Save. That's set up the relationship between these two columns.

Then if we scroll down, we can see Allow Nullable is enabled. This means tweets can be created without an author or a user ID. Now, that's not what we want. We want all of our tweets to have an associated user. If we try and save this, we're going to get an error when creating the column user_id because our current tweets contain null values.

We could either delete our existing tweets here, or a more realistic example would be to migrate these tweets to be correct. Let's leave Allow Nullable enabled for now so that we can create our column. We can get rid of these errors here. Now we need to set a value for the user_id for each of our tweets.

We can do that by double-clicking, which is going to look up all the rows in the auth.users table. We can click this one here and the same for these other two tweets. Now that all of the rows in our tweets column have a user_id, we can edit this column, scroll down to Allow Nullable, disable this one so that every tweet needs to have a user associated with it, and click Save to apply those changes.

Now, if we try to insert a new row for the tweets table and set the title to be fourth tweet, if we leave the user_id column blank and try to save, we're going to get this error because a null value in the column user_id of relation tweets violates not-null constraint, so this one cannot be null.

We can click here to select a record, choose our only user, and click Save to successfully insert that fourth tweet. Now, if we go back to our application and refresh, we'll see we're getting back our four tweets as well as this new user_id column because back over in our code, we're selecting all of the columns.

If we have a look at the type for our tweets, we only have created_at, id, and title. This is because any time we change the structure of our database, we need to go and generate a new version of our lib/database.types.ts file to contain the correct TypeScript definitions.

We can do that by running the same command as earlier, so npx supabase generate types for typescript, passing the project-id of our supabase project and telling it to write that result to lib/database.types.ts.

Let's run this to add our user_id column. We can see our file has been updated with the user_id and our types are correctly flowing through to our tweets, keeping our Next.js application in sync with our database schema.

egghead
egghead
~ just now

Member comments are a way for members to communicate, interact, and ask questions about a lesson.

The instructor or someone from the community might respond to your question Here are a few basic guidelines to commenting on egghead.io

Be on-Topic

Comments are for discussing a lesson. If you're having a general issue with the website functionality, please contact us at support@egghead.io.

Avoid meta-discussion

  • This was great!
  • This was horrible!
  • I didn't like this because it didn't match my skill level.
  • +1 It will likely be deleted as spam.

Code Problems?

Should be accompanied by code! Codesandbox or Stackblitz provide a way to share code and discuss it in context

Details and Context

Vague question? Vague answer. Any details and context you can provide will lure more interesting answers!

Markdown supported.
Become a member to join the discussionEnroll Today