So we want to create some indexes or indices, and the first one we want to create is a constraint on
the users table that says you can never have two rows with the same email address.
Let's do that one first.
And the way you do this is actually by creating a unique index on a given column for a given table.
And we'll create our migration first.
So I'll say so to generate Fiz create a unique index for users table that creates our migrations up
and down.
So let's do the up one first.
And the way you do this is really simple.
You just say add index and then table name users, column name email and then the type of index you
want.
And if you want a default index, you would just leave that empty and that would be what you did.
But we want a particular kind of index.
We want a unique index and we want that to be s true.
So let's run that migration.
So to migrate and there it does.
So we can go look at this table over here.
If we look at here, we see are we are users.
Email ID is the name.
That's the index on the user's table.
The other ones, the primary key.
Leave that one alone.
That's generated as part of the when we created the ID column is type serial.
The primary key index is created by default.
So never delete that or things will slow to a crawl.
But this is the one we just created.
Users email IDEX, so we need to update our migration are down migration to drop that.
And we do that as follows drop index table name, which is users.
And the index name which we just saw was users, email IDEX, users email maniac's and there we should
be able to run our down migration.
So let's try it to migrate down and it does it.
Let's make sure it disappeared by refreshing this right.
Click on it and choose refresh and it's gone and if we run it again.
So to migrate.
And go refresh this.
It's up, so now that was for that table.
We also want to put some indices on the room restrictions table because that's going to get big over
time.
So we'll probably want to create an index on two fields together, start date and end date.
That way we can search for very quickly restrictions that exist within a given date range.
So let's do that first.
Some go back to our wide open, our terminal window.
So to generate fees, create indices on room restrictions.
And again, that's just the name I'm choosing to make it readable.
So that will create our migrations.
Let's do the app one first.
We want to create an index on start date and end date so we can do it like this and index the table
is remove restrictions and the columns because there's two of them.
I'm going to put them in square brackets start date.
End date and then close my square brackets.
And what kind of index do I want?
I want the default fast search index.
So I'll just leave the the the curly brackets there empty and that will create the index that we want.
Now, I'll duplicate that line and go back and see it.
Is there anything else that I want?
I probably want to do an index on room ID and on reservation ID as well.
So let's do one for room ID and because there's only one column here, I just put it in single quotes.
So room ID and reservation ID, I think that's all I want.
Remedy reservation ID.
Yep, that's fine.
OK, so reservation night.
So there we go.
We now should be able to run this migration.
Let's try it sota migrate and it does it.
And now we want our down for this one as well.
So let's open this up and let's just copy one that we used here just to make a little less typing and
again go to our down migration right here and paste it in.
So let's get the names of those those indices.
So we'll go over here, open our room restrictions table, look at the indices and refresh it.
And we have one called Room Restrictions, Reservation ID, IDEX, Room Restrictions, Reservation ID,
IDEX, Reservation ID Index.
And of course, it's not on the user's table, it's on the room restrictions table.
So let's duplicate that.
Get the second name room ID idex.
And the third one is named Start Date, Underscore and date underscore IDEX, so room becomes start,
underscore, date and underscore date there and there's no doubt.
So we've now created all of the indexes that we need.
But you might have noticed I left one thing out and that's an exercise for you.
I never created a foreign key from reservation idy over two reservations I need and I need one there.
So an exercise for you.
I'd like you to try to add the up and down migration's for the link between reservation ID on Rhim restrictions
and the ID column and the reservations table.
So give that a try.
It's not that hard.
You should be able to do without much difficulty.
And if you have problems, don't worry.
I will do it for you in the next lecture.