PRIMARY KEYS

Back to: Structured Query Language (SQL)

The PRIMARY KEY

The PRIMARY KEY constraint can be applied to a column where each value in that column must both be UNIQUE and NOT NULL. Typically used as a unique identifier. For example, each person in Australia with a drivers license has a unique license number. There is a high likelihood that two people in Australia have the same first name and last name, but they can not have the same drivers license. Here’s an example:

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY,
    amount DECIMAL(5, 2)
);

If you are adding a PRIMARY KEY to an existing table:

ALTER TABLE transactions
ADD CONSTRAINT
PRIMARY KEY (transaction_id);

NOTE: There can be only ONE PRIMARY KEY per table.

When we add data to this table, the transaction_id MUST be a UNIQUE number and it can not be NULL. Attempting to add data using a duplicate transaction_id or NULL value will throw an error.

INSERT INTO transactions
VALUES (1000, 6.99),
       (1001, 8.50),
       (1002, 15.00),
       (1000, 1.20);

Throws the error: Error Code: 1062. Duplicate entry ‘1000’ for key ‘transactions.PRIMARY’

If we fix the error:

INSERT INTO transactions
VALUES (1000, 6.99),
       (1001, 8.50),
       (1002, 15.00),
       (1003, 1.20);

SELECT * FROM transactions

If a customer came in wanting a refund for their purchase, you ask for their receipt and it is transaction_id: 1002 – you can easily look up that transaction number to find out how much to refund:

SELECT amount FROM transactions
WHERE transaction_id = 1002;

Returns the output:

AUTO_INCREMENT

Having to remember what your last PRIMARY KEY was so you don’t replicate it is difficult, so we can have the number automatically increment using the AUTO_INCREMENT attribute. The AUTO_INCREMENT attribute can be applied to a column that is set as a key.

Whenever we insert a new row, our primary key can be populated automatically, then each subsequent row is AUTO_INCREMENTED.

To demonstrate this we will first DROP TABLE transactions; (if it still exists) so we can re-make it slightly differently. By default the PRIMARY KEY will equal 1.

CREATE TABLE transactions (
    transaction_id INT PRIMARY KEY AUTO_INCREMENT,
    amount DECIMAL(5, 2)
);

SELECT * FROM transactions;

This has created a blank table, now we can add some data:

INSERT INTO transactions (amount)
VALUES (5.25);

SELECT * FROM transactions;

Add some more values, I could have done this in the previous block but the revision will not hurt:

INSERT INTO transactions (amount)
VALUES (6.50),
       (7.80),
       (9.90);

SELECT * FROM transactions;

We could set our PRIMARY KEY to start at a different value:

ALTER TABLE transactions
AUTO_INCREMENT = 1000;

Now the PRIMARY KEY will jump to 1000 and AUTO_INCREMENT from there.

Key Knowledge