Primary Keys in SQL

We want all our data entries in a table to be uniquely identifiable. A Primary key is a unique identifier on a row. A good example of a primary key is your twitter handle. No 2 twitter handles are the same, and your twitter handle cannot be NULL. You can change your twitter handle, but it always has to be unique (and twitter will not allow the change unless it is unique value).

CREATE TABLE unique_students (student_id INT NOT NULL,
name VARCHAR(100),
age INT,
PRIMARY KEY(student_id)
);

The below image will shows us what the above code creates in mysql:

Here we can see that the primary key is the student_id. However, it would be less efficient in this database to manually create our primary key. We can do this automatically with the auto_increment keyword.

CREATE TABLE unique_students (
student_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
age INT,
PRIMARY KEY(student_id)
);

The auto_increment id will add a 1 each time, so we no longer have to specify it when creating a new entry
In the highlighted section of the graphic below, we can see that we have 2 identical entries except they have different student_id's because they've been auto_incremented. They are actually 2 separate students who happen to have the same first name and age (which is quite common in a school setting).

The auto_increment now lies in the "Extra" field (use the DESC keyword to show the table's description.

That's all for this blog, folks. Thanks for checking it out :)

Comments

[Alvaro Montoro]

Nice introduction to primary keys.

As a side note: I would update the example at the beginning. The fact that you can change your twitter handle (even if it has to be unique) is a sign that it is a bad candidate for a primary key. Imagine that you use it as a primary key, and then the user changes it. It would require a major update all over the database. It would be better to have a different field as primary key, and have the twitter handle with an unique constraint.