1

00:00:01,470 --> 00:00:06,600

So we want to create some indexes or indices, and the first one we want to create is a constraint on



2

00:00:06,600 --> 00:00:11,010

the users table that says you can never have two rows with the same email address.



3

00:00:11,090 --> 00:00:12,090

Let's do that one first.



4

00:00:12,930 --> 00:00:19,950

And the way you do this is actually by creating a unique index on a given column for a given table.



5

00:00:20,220 --> 00:00:22,110

And we'll create our migration first.



6

00:00:22,120 --> 00:00:36,060

So I'll say so to generate Fiz create a unique index for users table that creates our migrations up



7

00:00:36,060 --> 00:00:36,590

and down.



8

00:00:36,600 --> 00:00:38,010

So let's do the up one first.



9

00:00:38,580 --> 00:00:39,960

And the way you do this is really simple.



10

00:00:39,960 --> 00:00:47,760

You just say add index and then table name users, column name email and then the type of index you



11

00:00:47,760 --> 00:00:48,090

want.



12

00:00:48,240 --> 00:00:52,250

And if you want a default index, you would just leave that empty and that would be what you did.



13

00:00:52,260 --> 00:00:54,150

But we want a particular kind of index.



14

00:00:54,600 --> 00:00:58,680

We want a unique index and we want that to be s true.



15

00:00:59,460 --> 00:01:00,720

So let's run that migration.



16

00:01:01,800 --> 00:01:04,920

So to migrate and there it does.



17

00:01:05,370 --> 00:01:07,830

So we can go look at this table over here.



18

00:01:07,830 --> 00:01:10,290

If we look at here, we see are we are users.



19

00:01:10,290 --> 00:01:12,120

Email ID is the name.



20

00:01:12,480 --> 00:01:14,450

That's the index on the user's table.



21

00:01:14,490 --> 00:01:15,780

The other ones, the primary key.



22

00:01:15,780 --> 00:01:16,770

Leave that one alone.



23

00:01:16,770 --> 00:01:22,100

That's generated as part of the when we created the ID column is type serial.



24

00:01:22,110 --> 00:01:25,020

The primary key index is created by default.



25

00:01:25,020 --> 00:01:27,780

So never delete that or things will slow to a crawl.



26

00:01:27,870 --> 00:01:29,430

But this is the one we just created.



27

00:01:29,430 --> 00:01:36,000

Users email IDEX, so we need to update our migration are down migration to drop that.



28

00:01:36,270 --> 00:01:42,150

And we do that as follows drop index table name, which is users.



29

00:01:42,660 --> 00:01:54,030

And the index name which we just saw was users, email IDEX, users email maniac's and there we should



30

00:01:54,030 --> 00:01:55,350

be able to run our down migration.



31

00:01:55,350 --> 00:01:59,400

So let's try it to migrate down and it does it.



32

00:01:59,550 --> 00:02:02,070

Let's make sure it disappeared by refreshing this right.



33

00:02:02,070 --> 00:02:06,150

Click on it and choose refresh and it's gone and if we run it again.



34

00:02:06,150 --> 00:02:07,050

So to migrate.



35

00:02:08,910 --> 00:02:10,170

And go refresh this.



36

00:02:12,660 --> 00:02:15,790

It's up, so now that was for that table.



37

00:02:16,170 --> 00:02:22,050

We also want to put some indices on the room restrictions table because that's going to get big over



38

00:02:22,050 --> 00:02:22,380

time.



39

00:02:22,380 --> 00:02:27,580

So we'll probably want to create an index on two fields together, start date and end date.



40

00:02:27,630 --> 00:02:33,190

That way we can search for very quickly restrictions that exist within a given date range.



41

00:02:33,210 --> 00:02:34,230

So let's do that first.



42

00:02:34,770 --> 00:02:38,540

Some go back to our wide open, our terminal window.



43

00:02:38,850 --> 00:02:47,910

So to generate fees, create indices on room restrictions.



44

00:02:49,140 --> 00:02:51,300

And again, that's just the name I'm choosing to make it readable.



45

00:02:51,930 --> 00:02:54,310

So that will create our migrations.



46

00:02:54,330 --> 00:02:55,710

Let's do the app one first.



47

00:02:56,190 --> 00:03:03,960

We want to create an index on start date and end date so we can do it like this and index the table



48

00:03:03,960 --> 00:03:09,990

is remove restrictions and the columns because there's two of them.



49

00:03:09,990 --> 00:03:13,200

I'm going to put them in square brackets start date.



50

00:03:15,600 --> 00:03:18,720

End date and then close my square brackets.



51

00:03:19,110 --> 00:03:20,860

And what kind of index do I want?



52

00:03:20,880 --> 00:03:23,720

I want the default fast search index.



53

00:03:23,730 --> 00:03:29,490

So I'll just leave the the the curly brackets there empty and that will create the index that we want.



54

00:03:30,030 --> 00:03:32,310

Now, I'll duplicate that line and go back and see it.



55

00:03:32,360 --> 00:03:33,790

Is there anything else that I want?



56

00:03:33,810 --> 00:03:38,270

I probably want to do an index on room ID and on reservation ID as well.



57

00:03:38,520 --> 00:03:44,400

So let's do one for room ID and because there's only one column here, I just put it in single quotes.



58

00:03:45,090 --> 00:03:52,170

So room ID and reservation ID, I think that's all I want.



59

00:03:52,620 --> 00:03:55,410

Remedy reservation ID.



60

00:03:57,140 --> 00:03:57,980

Yep, that's fine.



61

00:03:58,010 --> 00:03:59,300

OK, so reservation night.



62

00:04:00,680 --> 00:04:01,860

So there we go.



63

00:04:02,240 --> 00:04:04,490

We now should be able to run this migration.



64

00:04:04,520 --> 00:04:08,320

Let's try it sota migrate and it does it.



65

00:04:09,140 --> 00:04:11,740

And now we want our down for this one as well.



66

00:04:11,750 --> 00:04:18,800

So let's open this up and let's just copy one that we used here just to make a little less typing and



67

00:04:18,800 --> 00:04:21,880

again go to our down migration right here and paste it in.



68

00:04:22,400 --> 00:04:24,880

So let's get the names of those those indices.



69

00:04:24,890 --> 00:04:30,680

So we'll go over here, open our room restrictions table, look at the indices and refresh it.



70

00:04:33,050 --> 00:04:45,800

And we have one called Room Restrictions, Reservation ID, IDEX, Room Restrictions, Reservation ID,



71

00:04:45,800 --> 00:04:49,730

IDEX, Reservation ID Index.



72

00:04:49,730 --> 00:04:54,500

And of course, it's not on the user's table, it's on the room restrictions table.



73

00:04:54,500 --> 00:04:55,640

So let's duplicate that.



74

00:04:55,650 --> 00:04:59,120

Get the second name room ID idex.



75

00:05:02,800 --> 00:05:11,950

And the third one is named Start Date, Underscore and date underscore IDEX, so room becomes start,



76

00:05:11,980 --> 00:05:18,220

underscore, date and underscore date there and there's no doubt.



77

00:05:18,880 --> 00:05:22,750

So we've now created all of the indexes that we need.



78

00:05:22,990 --> 00:05:26,920

But you might have noticed I left one thing out and that's an exercise for you.



79

00:05:27,940 --> 00:05:37,900

I never created a foreign key from reservation idy over two reservations I need and I need one there.



80

00:05:38,260 --> 00:05:39,820

So an exercise for you.



81

00:05:39,970 --> 00:05:48,580

I'd like you to try to add the up and down migration's for the link between reservation ID on Rhim restrictions



82

00:05:48,880 --> 00:05:51,310

and the ID column and the reservations table.



83

00:05:51,340 --> 00:05:52,180

So give that a try.



84

00:05:52,390 --> 00:05:53,080

It's not that hard.



85

00:05:53,080 --> 00:05:54,820

You should be able to do without much difficulty.



86

00:05:55,750 --> 00:05:57,400

And if you have problems, don't worry.



87

00:05:57,400 --> 00:05:59,530

I will do it for you in the next lecture.