MariaDB Tutorial / Identify Rows with PRIMARY KEY, AUTO_INCREMENT, and UNIQUE

PRIMARY KEY uniquely identifies each row, while UNIQUE restricts other values or combinations that must not repeat. AUTO_INCREMENT generates integer identifiers; it does not replace a key declaration or guarantee a gap-free sequence. Use these features together according to the data’s identity and business rules.

Separate a row identifier from a business uniqueness rule

Two different customers can have the same name, making a name a poor row identifier. This example uses a stable generated customer number as the primary key and treats email uniqueness as a separate rule. The table-structure guide shows where these definitions belong. Combine these keys with the presence/range rules in Define Input Rules with NULL, DEFAULT, NOT NULL, and CHECK, and analyze which facts a business identifier determines as in Normalize an Order Database Without Losing Historical Facts.

One primary key does not necessarily mean one column: a composite primary key is one definition involving multiple columns. AUTO_INCREMENT does not have to be the primary key in every possible design, but a single integer primary key is a straightforward starting point here.

Run the complete customer and key examples

Connect as course_user to the practice server prepared in the installation guides, with the shopcourse database available. The command-line client and SQL-file guide explains the connection and SOURCE command. Run these checks inside that connection, not in the operating-system shell:

SELECT VERSION() AS server_version, DATABASE() AS selected_database;
USE shopcourse;
SELECT DATABASE() AS selected_database;

The selected database should be shopcourse. Save a complete normal example as a UTF-8 .sql file and run it with SOURCE and its actual local path. Keep the intentionally failing statements separate: an error can stop a source file. Use a learning instance, not a connection containing production data.

customers_key_lab is a separate practice table for learning identifiers before modeling customer–order relationships. The example permits an unknown email by making that column nullable. It declares the primary-key column NOT NULL explicitly, then omits it from the customer inserts:

USE shopcourse;
CREATE TABLE customers_key_lab (
  customer_id INT NOT NULL AUTO_INCREMENT,
  customer_name VARCHAR(100) NOT NULL,
  email VARCHAR(254) NULL,
  PRIMARY KEY (customer_id),
  CONSTRAINT uq_customers_key_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO customers_key_lab (customer_name, email) VALUES
  ('Alex Chen', 'alex@example.com'),
  ('Sam Patel', NULL),
  ('Jordan Lee', NULL);
SELECT customer_id, customer_name, email FROM customers_key_lab ORDER BY customer_id;
CREATE TABLE product_codes_key_lab (
  supplier_id INT NOT NULL,
  supplier_code VARCHAR(30) NOT NULL,
  label VARCHAR(100) NOT NULL,
  PRIMARY KEY (supplier_id, supplier_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO product_codes_key_lab VALUES
  (1, 'N001', 'Notebook'),
  (2, 'N001', 'Notebook from supplier two');
SELECT supplier_id, supplier_code, label FROM product_codes_key_lab ORDER BY supplier_id;
CREATE TABLE auto_gap_lab (
  item_id INT PRIMARY KEY AUTO_INCREMENT,
  label VARCHAR(30) NOT NULL
) ENGINE=InnoDB;
START TRANSACTION;
INSERT INTO auto_gap_lab (label) VALUES ('Rolled-back row');
ROLLBACK;
INSERT INTO auto_gap_lab (label) VALUES ('Saved row');
SELECT item_id, label FROM auto_gap_lab;

On the fresh table, the customer identifiers are 1, 2, and 3. UNIQUE(email) permits the two NULL emails; it does not require an email to be supplied. Add a suitable NOT NULL policy if an email is mandatory, and review existing NULL rows before imposing that condition on an existing table. The NULL and input-constraint guide explains that distinction.

Consult the AUTO_INCREMENT reference and PRIMARY KEY and UNIQUE rules for the definitions. The example.com addresses here are sample values, not addresses for sending mail.

Test duplicate email and duplicate primary-key values

After the normal file, execute these two intentional failures separately. The first repeats an existing email; the second explicitly repeats customer identifier 1:

INSERT INTO customers_key_lab (customer_name, email) VALUES ('Another Alex', 'alex@example.com');

INSERT INTO customers_key_lab (customer_id, customer_name) VALUES (1, 'Duplicate ID');

Compare the constraint names in the errors. After either failure, SELECT COUNT(*) FROM customers_key_lab; should remain three. Do not immediately switch to INSERT IGNORE or REPLACE to hide the issue. REPLACE can replace an existing conflicting row, making it more than a harmless way to bypass a duplicate.

The email column’s collation affects duplicate comparisons. With utf8mb4_unicode_ci, changing the case can still produce a duplicate; ALEX@example.com is not a reliably distinct value from alex@example.com under that rule. Choose normalization and comparison policies deliberately rather than assuming different stored spellings always pass UNIQUE. The string and collation discussion provides the context.

Understand a primary key made from multiple columns

Suppose two suppliers each use the product code N001. A key on the code alone would reject the second supplier’s otherwise valid product. The complete example defines the pair (supplier_id, supplier_code) as the primary key:

INSERT INTO product_codes_key_lab VALUES (1, 'N001', 'Duplicate code');

The composite key makes the combination unique, not every component independently unique. One supplier can have several codes, and different suppliers can share a code. Queries and foreign-key references may need the complete combination. If a business key can change, consider a stable generated identifier with a separate UNIQUE combination instead of expanding a primary key only for convenience.

Expect gaps in generated identifiers

The auto_gap_lab section inserts a row inside a transaction, rolls it back, and then saves a different row. In this fresh InnoDB example, the persisted row has identifier 2 and label Saved row. The first identifier reserved by the rolled-back insert is not returned to the sequence.

Deletes, failed inserts, and concurrent work can also leave gaps. AUTO_INCREMENT is therefore not a count of rows or a guarantee of gap-free invoice numbering. Count rows with COUNT(*); MAX(id) only reports the highest visible identifier.

When you need the generated value, call LAST_INSERT_ID() in the same connection immediately after a successful relevant insert. Do not combine it with another connection’s work or interpret a value after a failed insert as proof of success. Calculating MAX(id)+1 yourself lets concurrent clients choose the same next number and is not a replacement for safe identifier generation.

Inspect the keys and reset the isolated tables

SHOW CREATE TABLE customers_key_lab;
SHOW INDEX FROM customers_key_lab;
SHOW CREATE TABLE product_codes_key_lab;

Check that PRIMARY and uq_customers_key_email are separate keys and that the composite primary key includes both intended columns. DESCRIBE’s Extra field and SHOW INDEX answer different questions: generated values and uniqueness are different features.

Before repeating the exercise, use SELECT DATABASE(); and SHOW CREATE TABLE table_name;, substituting each actual table name, to confirm that the targets are the disposable tables created here. Back up any data you need to keep. Only after verifying those targets, remove them with:

DROP TABLE shopcourse.customers_key_lab, shopcourse.product_codes_key_lab, shopcourse.auto_gap_lab;

This removes both the named tables and their rows. A data-definition statement such as DROP TABLE is not undone by an ordinary ROLLBACK. You can then rerun the complete normal example. The existing products table from the first product-table exercise is deliberately outside this reset.