Setup a Foreign Key relationship between PostgreSQL tables

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.