[SQL]: Primary Key

SQL

09/22/2019


Consider this situation..

TEXT
+----------+----------+
| name | age |
+----------+----------+
| sameName | 23 |
| sameName | 23 |
| sameName | 23 |
| sameName | 23 |
+----------+----------+
  • We want our data to be uniquely identifiable
  • For instance, we can add userID
TEXT
+----------+----------+----------+
| name | age | userID |
+----------+----------+----------+
| sameName | 23 | 1 |
| sameName | 23 | 2 |
| sameName | 23 | 3 |
| sameName | 23 | 4 |
+----------+----------+----------+

Primary Key: A Unique Identifier

Assigning a new primary key:

SQL
CREATE TABLE uniqueUsers(
userID INT NOT NULL PRIMARY KEY,
name VARCHAR(20),
age INT
);
--OR
CREATE TABLE uniqueUsers(
userID INT NOT NULL,
name VARCHAR(20),
age INT,
PRIMARY KEY (userID)
);

Check the columns

TEXT
mysql> DESC uniqueUsers;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| userID | int(11) | NO | PRI | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • Note that the Key now has PRI

Add data

SQL
INSERT INTO uniqueUsers
VALUES (1, 'John', 23);
  • When entering the same primary key, it will throw an error
TEXT
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

AUTO_INCREMENT

When you assign a primary key with AUTO_INCREMENT you don't have to put primary keys in parameter every time

SQL
CREATE TABLE uniqueUsers2(
userID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
age INT
);
  • Order of extra parts, NOT NULL, AUTO_INCREMENT, and PRIMARY KEY don't matter
  • Table:
TEXT
mysql> DESC uniqueUsers2;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| userID | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
  • Now with primary keys the user data can be uniquely identifiable

Then INSERT data with..

SQL
--INSERT INTO uniqueUsers2 VALUES('Elllis', 22); -- DOESN'T WORK
INSERT INTO uniqueUsers2 VALUES(3, 'Ellis', 22);
INSERT INTO uniqueUsers2(name, age) VALUES('paul', 33);
INSERT INTO uniqueUsers2(name, age) VALUES('paul', 33);
INSERT INTO uniqueUsers2(name, age) VALUES('paul', 33);
  • Since AUTO_INCREMENT was used, userID parameter can be skipped
  • Output:
TEXT
mysql> SELECT * from uniqueUsers2;
+--------+-------+------+
| userID | name | age |
+--------+-------+------+
| 3 | Ellis | 22 |
| 4 | paul | 33 |
| 5 | paul | 33 |
| 6 | paul | 33 |
+--------+-------+------+

Side note:

  • Other example of primary keys: usernames, customerNumber



Practice

Create a worker table with following columns

  • id: number that automatically inccrements. Required. Primary key
  • lastName: text, required
  • firstName: text, required
  • midName: text, not required
  • age: number, required
  • curStatus: text, required, defaults to 'single'
  • Scroll down for solution





















Soln

SQL
CREATE TABLE workers (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
lastName VARCHAR(20) NOT NULL,
firstName VARCHAR(20) NOT NULL,
midName VARCHAR(20),
age INT NOT NULL,
curStatus VARCHAR(50) NOT NULL DEFAULT 'single'
);
  • Table:
TEXT
mysql> DESC workers;
+-----------+-------------+------+-----+----------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+----------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| lastName | varchar(20) | NO | | NULL | |
| firstName | varchar(20) | NO | | NULL | |
| midName | varchar(20) | YES | | NULL | |
| age | int(11) | NO | | NULL | |
| curStatus | varchar(50) | NO | | single | |
+-----------+-------------+------+-----+----------+----------------+
6 rows in set (0.01 sec)

Test insert

SQL
INSERT INTO workers(firstName, lastName, age)
VALUES ('Smith', 'Min', 39);
SELECT * FROM workers;
  • Tests midName that's not required and default curStatus
  • Result:
TEXT
mysql> SELECT * FROM workers;
+----+----------+-----------+---------+-----+-----------+
| id | lastName | firstName | midName | age | curStatus |
+----+----------+-----------+---------+-----+-----------+
| 1 | Min | Smith | NULL | 39 | single |
+----+----------+-----------+---------+-----+-----------+
1 row in set (0.00 sec)

WRITTEN BY

Keeping a record