Primary Key

Quick reference

• 00:05 In the last video, we talked about indexing.
• 00:07 In this video, I wanna talk about the primary key.
• 00:09 And it's sorta related to indexing, but not really.
• 00:12 So if we look at at our current customers table here,
• 00:15 we have this column Customer ID.
• 00:17 And it has a unique number.
• 00:19 Now, I did this by hand, I created this Customer ID column.
• 00:22 And I put each of these numbers in here myself, I just put them in.
• 00:26 And that's not a great way to do it.
• 00:27 You need a database table to generate this stuff for you automatically.
• 00:32 So every time a new customer gets added, they get their own unique Customer ID.
• 00:36 And that's what a primary key does.
• 00:38 Now the primary key has to be unique, no two records can have the same ID.
• 00:42 So Tina and Mark cannot be Customer 2.
• 00:45 One has to be 2 and one has to be 3.
• 00:47 And another thing, it can't be blank, it can't be null.
• 00:50 So a primary key has to have some value.
• 00:54 So it's really hard to change a table and add a primary key.
• 00:57 You can do it, but you have to jump through all kinds of crazy hoops.
• 00:59 So we're not gonna do that in this video.
• 01:01 Instead, I'm just gonna create a new table real quick here and
• 01:05 create a primary key in it, just to show you how to do this.
• 01:08 So I'm just gonna really quickly do this by hand.
• 01:10 CREATE TABLE, let's call it Persons.
• 01:14 And this is how we create a table with SQL.
• 01:18 From our primary key, instead of calling it Customer ID, I'm just gonna call it ID.
• 01:22 You could call it whatever you want.
• 01:23 And we're gonna make this an integer.
• 01:25 Now we wanna do this something called auto incrementing.
• 01:28 So anytime a new record gets added,
• 01:30 it will automatically generate this ID for us.
• 01:34 And to do that, we type this IDENTITY, and then (1,1).
• 01:39 That's just how they do this.
• 01:41 So we also need to call this a primary key.
• 01:45 So what else do we want in our table?
• 01:46 Well, let's give it just a FirstName, and we'll make that a varchar 50.
• 01:52 Because we learned our lesson to not use text.
• 01:55 And LastName varchar 50.
• 01:59 We could keep going, but that's good enough.
• 02:01 So if I come down here and change this to Persons.
• 02:06 Now if we execute this, I left the comma off of there.
• 02:10 You've got to separate each of these by the commas, obviously.
• 02:12 Now when we executive this, we have this right here, ID, FirstName, and LastName.
• 02:16 And if we come up here and click on Customer database and
• 02:19 hit this Refresh button, we can see here's our new Persons table.
• 02:25 And if we click on this and look at Design,
• 02:28 you see the ID column has this little key next to it.
• 02:30 So we know it's created a primary key, and you can also see it's not null.
• 02:35 Allow Nulls, no.
• 02:36 It's unchecked, so that's really neat.
• 02:38 So we've created our table, now let's put something in it.
• 02:41 So let's just INSERT INTO Persons,
• 02:48 and give it values of John
• 02:53 and Elder.
• 02:57 Now if we run this, boom!
• 02:59 John and Elder, but it's also generated this ID.
• 03:02 So if we change this to Jason Elder, my imaginary brother, and
• 03:06 execute, it adds another one.
• 03:08 And boom, the ID is automatically incremented to 2.
• 03:11 And if we come over here and right-click, and Edit the Top 200 Rows, we see,
• 03:15 sure enough, 1 and 2 in the ID field.
• 03:18 So it's auto incrementing, it's auto generating these things for
• 03:21 us automatically, and it will forever.
• 03:23 And now that column will be unique.
• 03:26 And we'll do the same thing with every other table we create.
• 03:29 So we'll be able to cross-reference these ID fields whenever we want to
• 03:32 do our joins, left join, right join, etc, inner, outer join.
• 03:36 And that's really cool.
• 03:37 So primary keys are just very important for databases.
• 03:41 And they're sort of like indexing.
• 03:43 It's something that happens in the background to make things better,
• 03:46 I guess, but a very important concept.
• 03:49 And you've now made it to the end of the course, so congratulations.
• 03:52 You now have a great fundamental grasp of basic SQL Server.
• 03:55 You can do things like create tables and databases.
• 03:58 You know about the basic data types, which ones to use and
• 04:01 which ones to steer clear of, I hope.
• 04:02 And you know basic SQL programming, things like select statements, WHERE clauses,
• 04:07 how to insert and update data, how to join multiple tables, and a whole lot more.
• 04:10 So I hope you enjoyed this course, and thanks for watching.

Lesson notes are only available for subscribers.