Primary Keys

John Purcell
A free video tutorial from John Purcell
Software Development Trainer
4.4 instructor rating • 17 courses • 2,253,531 students

Learn more from the full course

MySQL, SQL and Stored Procedures from Beginner to Advanced

A beginners' guide to MySQL queries, database design, transactions and stored procedures.

18:16:46 of on-demand video • Updated May 2016

  • Create, query and update databases
  • Use triggers, views and stored procedures
  • Understand transactions and database isolation levels.
  • Understand database users and security.
English Hello this is John from K4 programming dot com. In this tutorial we're going to take a lot of primary keys. In my sequel so I'm going to say use tutorial one as usual and I'm going to say show tables at the moment I've just got one table in here called uses. So I'm going to do drop table users because I want to recreate this table now. I say create table users and let's get this a idea of time in it and we'll give it and let's give it a name field or just call it names that of use and ANCs Schalter type text and also lots of email type text. Now before I create this table it's good practice and sequence in general to make sure that every table has a thing called a primary key. What this is is this one of your columns which in that column the values in a column are going to uniquely identify each row. So for every row in your table the primary key will have a different unique value and it can't be no . It's usual to make the primary key a integer type. You can make it another type. So for example if you had a table with the added column which was like a user name column and so you had a table of users and each user had a unique username important and unique you could choose to make using the primary key. You can't do that with typed text on a counter text being a variable length field but left strong where you will see fixed length text fields text types that you could use in your primary key. But the bottom line is that every table ideally should have a primary key and that's Ideally that should be an integer type because that will give you the fastest results with your queries. But it is possible to use text types as we'll see later on. So I'm going to make this ID column here which is which is an integer type. My primary key. So that's going to contain the following You can be unique for every row in a database and let's say primary key after it. So I'll run this now and then if we do desk users we can look at the type for this table so we see what the ID field can be. No. And that's because primary keys can't be no. In general if you want you can say primary key not no but that's implied by that. Just the fact that you made the primary key and we see here that it is the primary key. You see that the type is in brackets see Lavern the 11 doesn't actually refer to the number of digits you can store in or anything is purely a relatively unimportant display feature which means that if you have a integer in that it's smaller than 11 days it's my cynical in some circumstances depending on how you do the query when you do a query with Will left part the integer with spaces. I think you really left it plus not us not really important. So this brackets 11 is not so important. It just somehow sometimes under some circumstances little fat word or small integers you know get displayed in a field that has 11 spaces in it or not but it will truncate or integers or anything like that doesn't affect how many digits you can store in. I think for an instant any case a normal and you can get 10 digits in book if used. If you google for my secret numeric types you'll find more information on that. OK so let's see what we can do with a primary key and I say insert into the users Biotene name and email the values and we will put zero in for the ID and we'll have a name that say darb. And that e-mail Bob out of stipes nonsense and I don't know whether this site actually exists but we'll add that in. So go ahead and execute it. It's executed fine. Now we could happily insert the stuff in. Let's see some other stuff. I will give this a primary key value of one. So that's worked as well. We can see those values of course in the normal way select stuff from users. There we go. But now let's try to do some things that would work. So if we go and we bring back this query if for example we try to win some data with a primary key that already exists and that works if we execute this it won't execute because you can't have duplicate values in a primary key column. The point of it is to uniquely identify every row in the table. So similarly you can have no primary key is automatically not no. So this query this statement won't execute either. So the primary key must be unique. Most often you make it an integer and sometimes I see users doing stuff on beginners. With my sequel I see them doing things like can take the following or another key said how to use the name Bob and then I'll append something to that. Use a name. She's already in the database to make you need like an on the score and some digits. This is very bad practice. You shouldn't duplicate data in your database because imagine if you had a user with a user name Bob and they had a primary key 0 this is fine there's no data duplication really. If you had the primary keys of text you remember you can use the text type for primary cable there are text types that you can use which will see later. So if it was a text type you might think OK I'll do this. Bob underscore by this that's not a good idea because now you know duplicating some Tator it takes up unnecessary space. And what if you want to be named Bob. Well if Bob changes his name then you've got to rename it both here and here which is even more difficult . So I try to avoid having any duplicate data in your database you need to get all the duplicate data around that. Of course if you have two separate users and they both happen to have the same name that's not considered duplicate data it's that you need mainframes use or it's just that they happen to have the same name . But there again if they do have the same name you can't use that name as a primary key because the primary key really must have duplicate values in it. The point is just that you don't want to repeat data in your database so you don't want to have the name of one particularly use or repeated in two places. But if two people had the same name that's fine as long as you're not trying to put the same name of two different users in a field where you can't duplicate it's like a primary key. So anyway the way to get to grips with this is really just to play about it. So do try and create a table with some columns in it give it a integer type primary key in certain data and then check that you can insert duplicate values for the primary key check and you can't insert no advice for the primary key because even if this does seem complicated to you at first just by typing this stuff out it will start to remain in your head and it will start to become understandable even if you get to a point either now or later with this course while you're struggling typing this out and actually seeing what is really the key to understanding that. OK so until next time happy coding