Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
An ID column, is a column that's used as a unique identifier for each row in a table. It's commonly used in relational databases for connecting one table to another. For this lesson, we're going to use the scratch database, and again I'm going to copy from the exercise files. This is the, chapter 3 exercise file starting around line 100. Starting with this drop table if exists, because again, we're going to be creating this table over and over again with different options all the way down here through show indexes from tests on line 113.
So, I'm going to to copy that and paste that to our. SQL box here. And you'll notice I'm creating a table with three columns, INTEGER and two VARCHARs. And I'm inserting some rows into it and then selecting from it. I'm going to describe it, show the table status, the create table. And show the indexes. And so when I press go, there's our table, with the three rows that I inserted. They have values of one, two, and three for the ID.
And there's our describe, so that's the describe right there. The show table status. And the show create table. And you notice I also have a Show Indexes, but that's not getting us any results yet. So, I'm going to change this CREATE TABLE here, as we move along. In MySQL an ID column requires that a column is defined with not null and unique index constraints. And so, in MySQL, a primary key constraint, and this is actually true of many.
Database systems. Primary key provides both not and all, and unique. And it provides an index named primary. The effect is that there is only one primary key allowed per table. And so I'm going to go ahead and execute this. And you'll notice that down here in our show create table, it shows our create table as having a non-null constraint and a separate primary key index. And down here in our show indexes, we have a key name of primary.
And that's all just with this primary key here. We didn't say not null, we didn't say unique. So, you'll notice that nulls are not allowed and our index says, non_unique is zero, which means that it's constrained to unique keys. So if, I try to insert a duplicate row, I'm just going to. Copy this row here. So, we have two of those with a three in here twice. You notice we get a constraint violation. Duplicate entry three for key primary. So that is in fact not allowed.
So, primary key gives us those requirement for an id field. Current versions of MySQL do not require that the id column uses the primary key constraint. Earlier versions of mysql did require that. In most cases, an id column fits the definition of a primary key. Because, the id column requires that every value is unique, mysql provides a feature that will automatically generate sequential values for the column. So if, I come back up here to my definition,.
Where I have Primary Key, I can say Auto Increment. And it's a very common construct here. Auto Increment Primary Key. And when I press Go, you notice in the describe it now says auto-increment in that Extras column. And there's an Auto Increment here in the Show Table Status. And if, I come back here now and I take these values out, and I have to take ID out of the list there as well. So now I'm not inserting anything into that ID column at all, and I pressure go, and I still get those sequential values.
Those are now automatically generated. So, that's AUTO_INCREMENT. MySQL provides an alias for all of this called SERIAL. I can take all of this INTEGER_AUTO_INCREMENT_PRIMARY_KEY and I can just type SERIAL. And press Go. And I get exactly the same result. The difference here is, is that the type here, is bigint(20) unsigned. And if I say, INTEGER. Auto increment, primary key.
You notice it's just an inta 11. So, if you have a lot of data and if you're expecting to have a very large table that big int unsigned is going to give you the maximum amount of space. For those values. They'll allow you the maximum size of numbers for that column, so it's not such a bad idea. The serial short cut is an easy short cut, but you need to take your data storage requirements into consideration. In most cases, it's fine and allows for very large tables.
In practice, in an application where you might be concerned with the extra storage you'll probably need the maximum size of a ___ anyway. According to the MySQL manual serial is just an alias for bigint, unsigned, not null, auto_increment, unique. Serial was added in MySQL 4.1, so it's not available in all the older versions. So, most people still use integer primary key. When you're using an automatically generated ID, sometimes. You'll need to know the ID column for the last inserted row.
This is available from a special function. And I'm going to come down here right after the select from test. And the special function is called last insert ID. And you notice that, I'm not specifying that it comes from a particular table, this is a database wide last inserted ID, so if you're using this in code and you want to get, what's the ID of this row that I just inserted, so that you can use that ID. For another purpose. You want to be right after your inserts.
So, when I press go here, you see the last insert ID is 3, and you see that that is the ID of the last inserted row in the table. Just one more quick thing I wanted to show you with this ID serial. You'll notice that our create table shows the ID as 20, unsigned, not null, auto increment. And with the unique key ID, and here the key is named ID is not named primary because we're not using the primary key constraint. So, just so you know, that that's what it does.
My scale provides comprehensive support for ID columns with the auto increment constraint and the special serial alias. This is a very useful feature, we'll be using it a lot in the rest of the course. I'm going to go ahead and drop this table now. Bring our database back to its original state for the rest of the course.
Get unlimited access to all courses for just $25/month.Become a member
61 Video lessons · 99094 Viewers
56 Video lessons · 112376 Viewers
71 Video lessons · 81203 Viewers
131 Video lessons · 39003 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.