SQL KEYS

What are SQL KEYS?

SQL keys are constraints that uniquely identify rows and enforce relationships between tables.

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 KEYS: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery

SQL KEYS Full Explanation

In relational databases a key is a one-or-more-column constraint used to guarantee uniqueness or establish referential integrity. The most common types are:• Primary key – uniquely identifies each row in a table and cannot contain NULL values.• Foreign key – enforces a relationship between a column (or set of columns) in one table and the primary or unique key in another table.• Unique key – guarantees that all values in the constrained column(s) are distinct but can allow a single NULL depending on the dialect.• Composite key – any primary, unique, or foreign key that involves multiple columns.Keys improve query performance when the underlying database automatically creates an index, and they protect data integrity by rejecting duplicate or orphaned rows. When planning schemas choose stable, minimal columns for primary keys, prefer surrogate keys for large or frequently updated tables, and add explicit indexes to foreign keys if the dialect does not create them automatically. Dropping or altering keys on large tables can lock or block writes so perform such operations during maintenance windows.

SQL KEYS Syntax

-- Create primary key
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    email    VARCHAR(255) UNIQUE,
    name     TEXT
);

-- Create composite primary key
CREATE TABLE enrollment (
    student_id INTEGER,
    course_id  INTEGER,
    PRIMARY KEY (student_id, course_id)
);

-- Add foreign key
ALTER TABLE enrollment
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES students(id);

-- Drop a key
ALTER TABLE enrollment DROP CONSTRAINT fk_student;

SQL KEYS Parameters

Example Queries Using SQL KEYS

-- 1. Insert duplicate primary key (fails)
INSERT INTO users (user_id, email, name)
VALUES (1, 'alice@example.com', 'Alice');
INSERT INTO users (user_id, email, name)
VALUES (1, 'alice2@example.com', 'Alice Duplicate'); -- error

-- 2. Insert orphaned foreign key (fails)
INSERT INTO enrollment (student_id, course_id) VALUES (999, 42); -- error

-- 3. Valid inserts respecting keys
INSERT INTO students (id, name) VALUES (100, 'Bob');
INSERT INTO courses  (id, title) VALUES (42, 'SQL 101');
INSERT INTO enrollment (student_id, course_id) VALUES (100, 42);

Expected Output Using SQL KEYS

The second INSERT fails with a duplicate key error.
The orphaned foreign key insert fails with a referential integrity error.
Valid inserts succeed and new rows appear in their tables.

Use Cases with SQL KEYS

Guarantee each row is unique (primary key).
Link child tables to parents without data loss (foreign key).
Enforce business rules such as unique email addresses (unique key).
Speed lookups for join and filter operations when the dialect auto-indexes keys.

Common Mistakes with SQL KEYS

Defining a primary key on a non-unique or frequently changing column.
Forgetting to index foreign keys leading to slow joins.
Assuming UNIQUE allows multiple NULLs in all dialects.
Dropping keys without first removing dependent foreign keys.
Using large composite keys when a surrogate key plus unique constraint is simpler.

First Introduced In

ANSI SQL-86

Frequently Asked Questions

What is the difference between a primary key and a unique key?

A primary key uniquely identifies each row and cannot contain NULL values. A unique key also guarantees uniqueness but may allow a single NULL depending on the database engine.

Can I have multiple primary keys in one table?

You can have only one primary key constraint per table, but it can consist of multiple columns (composite key).

Do I need to index foreign key columns manually?

Primary and unique keys are indexed automatically in most systems. Some engines do not auto-index foreign keys, so add an index manually for better join performance.

How do composite keys affect performance?

Composite keys can slow inserts and updates because all columns must be checked for uniqueness. Consider a surrogate primary key plus a separate unique constraint if write performance is critical.