DEV Community

Cover image for Primary Key vs. Foreign Key in SQL: Explained with Examples
DbVisualizer
DbVisualizer

Posted on

Primary Key vs. Foreign Key in SQL: Explained with Examples

#sql #mysql #sqlserver

ver confused about when to use a primary key vs. a foreign key in your SQL tables? You’re not alone. These two types of constraints serve different but complementary purposes in relational database design.

What is a Primary Key?

A primary key is used to uniquely identify each record in a table. It typically uses an auto-incrementing integer and does not allow null or duplicate values.

Common use:

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100)
);
Enter fullscreen mode Exit fullscreen mode

What is a Foreign Key?

A foreign key links a column in one table to a primary key in another. It enforces relationships between tables and ensures data consistency.

Common use:

CREATE TABLE sales (
  id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT,
  FOREIGN KEY (product_id) REFERENCES products(id)
);
Enter fullscreen mode Exit fullscreen mode

Key Differences

  • Primary key: Uniquely identifies rows.
  • Foreign key: Links rows between tables.
  • A table has one primary key but can have many foreign keys.
  • Foreign keys require matching values in the referenced table.

Keys and SQL Clients

To explore how keys interact with your data visually, tools like DbVisualizer can help. With features like ERD visualization, DDL browsing, and automatic query generation, DbVisualizer makes it easier to manage and inspect your key relationships.

FAQ

Can I have multiple foreign keys?

Yes, one table can reference several others.

Can a foreign key be null?

Yes, unless restricted by a NOT NULL constraint.

Are foreign keys always integers?

Not necessarily. They just need to match the data type of the referenced column.

Can a column be both a primary and a foreign key?

Yes. It's rare but valid—often in junction tables.

Are keys automatically indexed?

Yes. Both primary and foreign keys benefit from index structures.

Conclusion

Primary keys and foreign keys are central to organizing and securing data in SQL. They serve different roles—one guarantees uniqueness, the other enforces relationships.

To work with these keys more efficiently, tools like DbVisualizer give you a visual overview and full control over your schema design. Try it out to see your keys in action.

Read the Primary key vs. Foreign key: A complete comparison article for more insights.

Top comments (0)