Introduction

In SQL, KEY usually refers to either a constraint or an index, depending on the exact phrase and the database system. The word matters most in definitions such as PRIMARY KEY, FOREIGN KEY, UNIQUE KEY, and in some systems plain KEY as a synonym for an index.

PRIMARY KEY

A primary key identifies each row in a table uniquely. It is the main row identity for that table and is normally used by other tables when they need to reference it.

1CREATE TABLE customers (
2    id BIGINT PRIMARY KEY,
3    email VARCHAR(255) NOT NULL
4);

In this example, id is the primary key. No two rows can share the same value, and the column cannot be NULL.

You can also define a composite primary key across multiple columns:

1CREATE TABLE order_items (
2    order_id BIGINT NOT NULL,
3    product_id BIGINT NOT NULL,
4    quantity INT NOT NULL,
5    PRIMARY KEY (order_id, product_id)
6);

That means the pair of values must be unique even if each column alone is not.

FOREIGN KEY

A foreign key defines a relationship from one table to another. It says that a value in the child table must match a valid primary key or unique key in the parent table.

1CREATE TABLE orders (
2    id BIGINT PRIMARY KEY,
3    customer_id BIGINT NOT NULL,
4    CONSTRAINT fk_orders_customer
5        FOREIGN KEY (customer_id) REFERENCES customers(id)
6);

Here customer_id is not the primary key of orders. It is a foreign key that points to customers.id.

This is how relational databases enforce referential integrity. You cannot create an order for a customer that does not exist, unless the constraint is absent or temporarily disabled.

UNIQUE KEY

A unique key enforces uniqueness without making the column the table’s primary identifier.

1CREATE TABLE users (
2    id BIGINT PRIMARY KEY,
3    username VARCHAR(50) NOT NULL,
4    email VARCHAR(255) NOT NULL,
5    UNIQUE KEY uq_users_username (username),
6    UNIQUE KEY uq_users_email (email)
7);

This is useful when a table needs one primary key for identity but still must guarantee that other values, such as usernames or emails, never repeat.

Plain KEY Often Means Index

In MySQL and MySQL-compatible systems, plain KEY is commonly just another way to say INDEX.

1CREATE TABLE products (
2    id BIGINT PRIMARY KEY,
3    sku VARCHAR(50) NOT NULL,
4    category_id BIGINT NOT NULL,
5    KEY idx_products_category_id (category_id)
6);

That KEY does not enforce uniqueness by itself. It mainly improves lookup performance for queries that filter or join on category_id.

This is an important distinction:

• 'PRIMARY KEY identifies rows'
• 'FOREIGN KEY enforces relationships'
• 'UNIQUE KEY enforces uniqueness'
• plain KEY often means non-unique index

Database Differences Matter

The broad idea of keys is common across relational systems, but plain KEY syntax is not interpreted identically everywhere.

In standard SQL discussions, people usually mean PRIMARY KEY or FOREIGN KEY when they say “key.” In MySQL DDL, plain KEY is very often shorthand for an index definition. PostgreSQL users are more likely to write CREATE INDEX explicitly rather than use KEY in that loose MySQL sense.

So when someone asks what KEY means, the right answer depends on context:

• table constraint definition
• index definition
• relational data modeling discussion

A Full Example

This table uses several kinds of keys together:

1CREATE TABLE invoices (
2    id BIGINT PRIMARY KEY,
3    customer_id BIGINT NOT NULL,
4    invoice_number VARCHAR(40) NOT NULL,
5    total_cents INT NOT NULL,
6    UNIQUE KEY uq_invoices_invoice_number (invoice_number),
7    KEY idx_invoices_customer_id (customer_id),
8    CONSTRAINT fk_invoices_customer
9        FOREIGN KEY (customer_id) REFERENCES customers(id)
10);

Each clause serves a different purpose:

• 'PRIMARY KEY makes id the row identifier'
• 'UNIQUE KEY guarantees invoice numbers do not repeat'
• 'KEY speeds up lookups by customer_id'
• 'FOREIGN KEY guarantees the referenced customer exists'

Common Pitfalls

The biggest pitfall is assuming every KEY means uniqueness. Plain KEY in MySQL is often only an index, and duplicate values are allowed unless you declare UNIQUE.

Another common mistake is confusing primary keys with foreign keys. A foreign key references another table’s key, while a primary key identifies rows in its own table.

Developers also sometimes create indexes and expect them to enforce business rules. An ordinary index helps performance, but it does not stop duplicate rows or invalid references.

Finally, be careful when switching between database systems. Syntax that works in MySQL may need to be rewritten in PostgreSQL or SQL Server, especially for nonstandard shorthand forms.

Summary

• 'KEY in SQL usually refers to row identity, relationships, uniqueness, or indexing depending on context.'
• 'PRIMARY KEY uniquely identifies rows.'
• 'FOREIGN KEY enforces a relationship to another table.'
• 'UNIQUE KEY enforces unique values.'
• In MySQL, plain KEY often means a non-unique index.