SQL KEY

What is the SQL KEY keyword?

KEY defines an index or participates in PRIMARY KEY and FOREIGN KEY constraints to enforce data integrity and speed up lookups.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL KEY: MySQL, MariaDB (KEY as INDEX); All major databases support PRIMARY KEY and FOREIGN KEY

SQL KEY Full Explanation

KEY is a reserved word that appears in several contexts. In MySQL and MariaDB, KEY is a synonym for INDEX in CREATE TABLE and ALTER TABLE statements, letting you create secondary indexes that improve query performance. Across all SQL dialects, KEY is also part of composite keywords such as PRIMARY KEY and FOREIGN KEY, which enforce entity-integrity and referential-integrity rules respectively. A PRIMARY KEY uniquely identifies each row, automatically creating a unique index; a FOREIGN KEY ensures that column values exist in the referenced table. When used alone (without PRIMARY or FOREIGN) KEY has no meaning outside MySQL-compatible engines, so code portability requires caution. KEY names must be unique within a table, and composite keys can include multiple columns. Dropping or altering a key may be blocked if dependent constraints exist.

SQL KEY Syntax

-- Create a secondary index (MySQL / MariaDB)
CREATE TABLE table_name (
  id INT PRIMARY KEY,
  user_id INT,
  KEY idx_user_id (user_id)
);

-- Add a key later
ALTER TABLE table_name ADD KEY idx_created_at (created_at);

-- Standard constraint forms
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

SQL KEY Parameters

Example Queries Using SQL KEY

-- 1. Composite primary key
CREATE TABLE enrollment (
  student_id INT,
  course_id  INT,
  PRIMARY KEY (student_id, course_id)
);

-- 2. Secondary index for faster search (MySQL)
ALTER TABLE users ADD KEY idx_email (email);

-- 3. Foreign key with explicit name
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customers
  FOREIGN KEY (customer_id) REFERENCES customers(id);

Expected Output Using SQL KEY

The enrollment table cannot store duplicate student/course pairs.
The MySQL engine builds a B-tree index on users.email, speeding WHERE email = ? queries.
The database blocks inserts into orders when customer_id is not present in customers.id.

Use Cases with SQL KEY

Speeding up SELECT queries with non-unique secondary indexes in MySQL.
Guaranteeing each row is unique with a PRIMARY KEY.
Enforcing parent-child relationships via FOREIGN KEY constraints.
Supporting composite keys when no single column is unique.

Common Mistakes with SQL KEY

Assuming KEY works as INDEX in PostgreSQL or SQL Server – it does not.
Forgetting to index foreign key columns, leading to slow joins.
Creating multiple nullable columns inside a PRIMARY KEY (not allowed).
Omitting a name for the key and later struggling to drop it because the system-generated name is unknown.

First Introduced In

MySQL 3.23 (KEY as synonym for INDEX). PRIMARY KEY and FOREIGN KEY appear in SQL-92.

Frequently Asked Questions

What is the difference between KEY and INDEX?

In MySQL they are synonyms inside CREATE TABLE and ALTER TABLE. Other dialects require CREATE INDEX and do not recognize KEY on its own.

Can I drop a primary key?

Yes, use ALTER TABLE ... DROP PRIMARY KEY, but first ensure no foreign keys depend on it or the statement will fail.

How many columns can a PRIMARY KEY have?

Most engines allow up to 16 columns, but the combined key length must fit within the index size limits specialized per engine.

Does adding a KEY lock the table?

In older MySQL versions adding an index could lock the table for writes. Modern versions with ALGORITHM=INPLACE or ONLINE minimize locking, but availability depends on engine and version.