Each record must have a unique identifier that sets it apart from all other records. This unique identifier is know as the Primary Key (or sometimes just the key).
Often, a field simply called “id” is used with an incrementing integer. This value is also usually set with the AUTO_INCREMENT parameter
CREATE TABLE IF NOT EXISTS Students ( id int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, …, final varchar(20) );
The primary key:
- Only one per table
- Cannot be null
- Must never change
- Must be unique
If DELETE is used to empty a table, the primary key’s position will not be removed and therefore any new data will continue from that point.
e.g. if there 20 records in the table, and DELETE was used to empty the table, any new data would start from the 21st position.
To overcome this the TRUNCATE keyword is used to empty the table and reset the primary key’s position so new data starts from 1