Defining A Composite Unique Constraint
In this lesson, we'll learn how you can enforce uniqueness between multiple columns using a composite unique constraint.
- Author
- Tom Gobich
- Published
- May 16
- Duration
- 4m 47s
Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.
Adocasts
Burlington, KY
Transcript
Defining A Composite Unique Constraint
-
(upbeat music)
-
So currently with the way that we have things,
-
there's nothing actually preventing a single user
-
from having a single movie inside of their watch list
-
more than once, which may result in unexpected behavior
-
in terms of our application.
-
To run through a quick example of this,
-
if we jump into a REPL session, so let's clear this out,
-
node ace REPL, await, load models.
-
Inside of our application, we have our user John Doe
-
with an email of [email protected].
-
And we also have this movie,
-
I'm your boogeyman with an ID of 420
-
that is currently in this user's watch list.
-
So we should have one result for that.
-
Let's go ahead and grab our user.
-
So const user equals await models.user.find, buy, or fail.
-
And we'll search by the email, [email protected].
-
Let's also go ahead and grab our movie as well.
-
So movie equals await models, movie, find, or fail,
-
the ID of 420, verify that that's the right movie.
-
There we go.
-
So currently if we were to do await user related watch list
-
query where movie ID is 420,
-
and let's also add Poyo to that
-
just to get all of the extra lucid properties
-
out of the way.
-
And we ran that.
-
This movie's only in this user's watch list once,
-
but there's nothing preventing us
-
from actually doing await models watch list,
-
create user ID of our user.ID
-
and movie ID of movie.ID.
-
Essentially adding this movie
-
into this user's watch list again.
-
So if we run this, there's all of the model properties.
-
But if we go up two times and rerun our same query,
-
we can verify that this single movie
-
is inside of the single user's watch list now twice,
-
which in terms of our application,
-
if we were to exit out of a REPL session
-
and boot that back up, jump back into our browser,
-
is gonna result in at least a little bit
-
of unexpected behavior because we're just querying
-
the first watch list and deleting it
-
whenever we're removing an item from a user's watch list.
-
So if we click on this,
-
it's gonna appear as though nothing had happened
-
because we have now that second record
-
that's showing up making this still
-
in that user's watch list.
-
If we give this a click once more,
-
now it's actually out of that user's watch list
-
and both of those records are now deleted.
-
If we give it a click once more,
-
things start to work more naturally as we expect them to.
-
So what we wanna do is go into our database structure
-
and enforce that the user ID and movie ID combination
-
should only be inside of this table a single time.
-
And we can do that using a unique constraint.
-
Now we know that we can apply a unique constraint
-
on a single property by adding in unique,
-
but we need to apply it as a combination
-
of both of these columns.
-
So adding it that way is not gonna work.
-
Instead, what we wanna do is table.
-
And there is a unique method that we can call here
-
that accepts in an array of column names.
-
So inside of this array,
-
we're gonna want to specify both of the columns
-
that we want to be combined unique,
-
which is our user ID and our movie ID.
-
And with this defined,
-
now this table will only allow one combination
-
of a single user ID and movie ID pair,
-
meaning our user with an ID of seven
-
can now only have the movie with an ID of 420
-
inside of the database once.
-
And it will fail if we attempt to add a second record in.
-
Now the argument could be made
-
that we would also want that to be true
-
for our cast movies and crew movies tables.
-
However, the inverse argument can be made as well.
-
We may want to allow multiple sinists
-
bound to a single movie
-
so that they can have multiple characters
-
that they may play in that single movie.
-
And the same with crew with their job titles.
-
So for these, there's some fluidity there.
-
And I think for that reason, we leave those as is.
-
And we just leave the unique constraint here
-
specifically for our watch list.
-
Now with our unique constraint applied,
-
what we need to do is roll back
-
to undo the single watch list migration.
-
So node, ace, migration, rollback.
-
Okay, that reverted our previous batch,
-
which just include our create watch list table migration.
-
And then we just need to node, ace, migration, run
-
to reapply that batch of migrations.
-
And now our watch list table
-
has that unique constraint applied.
-
If we jump back into our browser, refresh.
-
Oop, gotta reboot our server real quick.
-
NPM run dev.
-
There we go.
-
Now let's refresh.
-
Add this movie back into our watch list.
-
There we go.
-
And now if we jump back into our terminal,
-
let's stop our server, clear that out.
-
Node, ace, REPL.
-
Await, load models.
-
And attempt to do awaits models.watchlist.create.
-
User ID of seven, which is our John Doe user.
-
And movie ID of 420,
-
which is the movie that we just applied into our watch list.
-
If we attempt to create a second record with these same IDs,
-
we're gonna get an error
-
because that now violates our unique constraint
-
that we've added into this table.
-
Awesome, so everything there is working A-okay.
-
And we've now prevented the possibility
-
of a user having the same movie inside of their watchlist more than once.
-
Introduction
-
Fundamentals
-
2.0Routes and How To Create Them5m 24s
-
2.1Rendering a View for a Route6m 30s
-
2.2Linking Between Routes7m 52s
-
2.3Loading A Movie Using Route Parameters9m 18s
-
2.4Validating Route Parameters6m 7s
-
2.5Vite and Our Assets6m 39s
-
2.6Setting Up Tailwind CSS7m 5s
-
2.7Reading and Supporting Markdown Content4m 32s
-
2.8Listing Movies from their Markdown Files8m 51s
-
2.9Extracting Reusable Code with Services7m 4s
-
2.10Cleaning Up Routes with Controllers4m 52s
-
2.11Defining A Structure for our Movie using Models9m 38s
-
2.12Singleton Services and the Idea of Caching6m 11s
-
2.13Environment Variables and their Validation4m 16s
-
2.14Improved Caching with Redis10m 44s
-
2.15Deleting Items and Flushing our Redis Cache6m 46s
-
2.16Quick Start Apps with Custom Starter Kits6m 28s
-
2.17Easy Imports with NodeJS Subpath Imports8m 40s
-
-
Building Views with EdgeJS
-
3.0EdgeJS Templating Basics8m 49s
-
3.1HTML Attribute and Class Utilities6m 9s
-
3.2Making A Reusable Movie Card Component10m 24s
-
3.3Component Tags, State, and Props4m 53s
-
3.4Use Slots To Make A Button Component6m 56s
-
3.5Extracting A Layout Component5m 13s
-
3.6State vs Share Data Flow2m 59s
-
3.7Share vs Global Data Flow6m 8s
-
3.8Form Basics and CSRF Protection6m 13s
-
3.9HTTP Method Spoofing HTML Forms3m 3s
-
3.10Easy SVG Icons with Edge Iconify7m 57s
-
-
Database and Lucid ORM Basics
-
4.0Configuring Lucid and our Database Connection4m 3s
-
4.1Understanding our Database Schema9m 35s
-
4.2Introducing and Defining Database Migrations18m 35s
-
4.3The Flow of Migrations8m 28s
-
4.4Introducing Lucid Models5m 43s
-
4.5Defining Our Models6m 49s
-
4.6The Basics of CRUD11m 56s
-
4.7Defining Required Data with Seeders11m 11s
-
4.8Stubbing Fake Data with Model Factories13m 48s
-
4.9Querying Our Movies with the Query Builder15m 30s
-
4.10Unmapped and Computed Model Properties3m 24s
-
4.11Altering Tables with Migrations7m 6s
-
4.12Adding A Profile Model, Migration, Factory, and Controller2m 57s
-
4.13SQL Parameters and Injection Protection9m 19s
-
4.14Reusable Query Statements with Model Query Scopes8m 11s
-
4.15Tapping into Model Factory States9m 15s
-
4.16Querying Recently Released and Coming Soon Movies4m 59s
-
4.17Generating A Unique Movie Slug With Model Hooks7m 59s
-
-
Lucid ORM Relationships
-
5.0Defining One to One Relationships Within Lucid Models5m 49s
-
5.1Model Factory Relationships2m 54s
-
5.2Querying Relationships and Eager Vs Lazy Loading5m 17s
-
5.3Cascading and Deleting Model Relationships5m 16s
-
5.4Defining One to Many Relationships with Lucid Models6m 56s
-
5.5Seeding Movies with One to Many Model Factory Relationships5m 24s
-
5.6Listing A Director's Movies with Relationship Existence Queries8m 41s
-
5.7Listing and Counting a Writer's Movies8m 41s
-
5.8Using Eager and Lazy Loading to Load A Movie's Writer and Director5m 18s
-
5.9Defining Many-To-Many Relationships and Pivot Columns9m 48s
-
5.10Many-To-Many Model Factory Relationships4m 50s
-
5.11A Deep Dive Into Relationship CRUD with Models18m 5s
-
5.12How To Create Factory Relationships from a Pool of Data13m 55s
-
5.13How To Query, Sort, and Filter by Pivot Table Data9m 47s
-
-
Working With Forms
-
6.0Accepting Form Data12m 15s
-
6.1Validating Form Data with VineJS9m 29s
-
6.2Displaying Validation Errors and Validating from our Request7m 16s
-
6.3Reusing Old Form Values After A Validation Error2m 3s
-
6.4Creating An EdgeJS Form Input Component5m 28s
-
6.5Creating A Login Form and Validator5m 1s
-
6.6How To Create A Custom VineJS Validation Rule9m 7s
-
-
Authentication & Middleware
-
7.0The Flow of Middleware7m 49s
-
7.1Authenticating A Newly Registered User4m 14s
-
7.2Checking For and Populating an Authenticated User2m 10s
-
7.3Logging Out An Authenticated User2m 24s
-
7.4Logging In An Existing User6m 54s
-
7.5Remembering A User's Authenticated Session6m 55s
-
7.6Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
-
8.0Creating A Movie List Page3m 43s
-
8.1Filtering A Query By Pattern Likeness7m 9s
-
8.2Filtering Our List by Movie Status5m 47s
-
8.3How To Apply A Dynamic Sort Filter To Your Query7m 12s
-
8.4Joining SQL Tables To Order By A Related Column4m 49s
-
8.5Validating Query String Filter Values7m 24s
-
8.6How To Paginate Filtered Query Results9m 15s
-
8.7Pagination First, Last, Next, and Previous Buttons4m 3s
-
-
User Watchlist
-
9.0An Alternative Approach to Many-To-Many Relationships4m 56s
-
9.1Toggling A Movie in an Authenticated User's Watchlist9m 56s
-
9.2Listing and Filtering User Watchlist Items7m 31s
-
9.3Allowing Users To Toggle A Movie As Watched4m 44s
-
9.4Filtering By User's Watched Status6m 7s
-
9.5Defining A Composite Unique Constraint4m 47s
-
9.6Persist Filters Easily with Lucid's Query String Method3m 58s
-
-
User Profiles
-
10.0How to Create and Fix Missing User Profiles in Your Application7m 37s
-
10.1Using Dependency Injection to Update A User's Profile9m 46s
-
10.2Saving All Or Nothing with Database Transactions5m 15s
-
10.3Uploading and Displaying User Avatars15m 29s
-
10.4Displaying A User's Profile6m 1s
-
10.5Filtering, Preloading, and Sorting By Relationship7m 6s
-
-
Admin Panel
-
11.0Creating An Admin Layout7m 14s
-
11.1Counting Stats for our Admin Dashboard5m 43s
-
11.2Paginated Admin Movie Table13m 2s
-
11.3Allowing Admins to Create Movies16m 39s
-
11.4Allowing Admins to Update Movies and Clear Values13m 27s
-
11.5How To Use One Form to Create or Edit Movies5m 32s
-
Uploading Movie Cover Images in our Create or Edit Form10m 29s
-
Using A Wildcard Route Param to Download Storage Images7m 57s
-
Posting Objects, Arrays, and an Array of Objects in HTML Forms26m 26s
-
Managed Transactions and Syncing Movie Cast Members15m 55s
-
Allowing Admins to Delete Movies and their Relationships7m 42s
-
Thank You for Watching!0m 31s
-
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!