[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 uniqueUsersVALUES (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 WORKINSERT 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)