Foreign Key as Primary Key: Scenarios and Design Considerations

Introduction

In database design, the selection of primary keys and foreign keys significantly impacts system performance and maintainability. Many developers consider using foreign keys as primary keys when designing inter-table relationships. This article analyzes the rationality of this design decision from theoretical foundations and practical examples.

Basic Characteristics of Primary and Foreign Keys

A Primary Key uniquely identifies each record in a table and must satisfy uniqueness and non-null constraints. In SQL, primary keys are declared as follows:

CREATE TABLE User (
    userId INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(100) NOT NULL
);

A Foreign Key establishes relationships between tables by referencing another table's primary key, ensuring referential integrity. Typical foreign key declaration:

CREATE TABLE Profile (
    profileId INT PRIMARY KEY,
    userId INT,
    gender VARCHAR(10),
    dateOfBirth DATE,
    FOREIGN KEY (userId) REFERENCES User(userId)
);

Applicable Scenarios for Foreign Keys as Primary Keys

According to relational database design principles, foreign keys typically allow duplicate values, which is common in one-to-many relationships. For example, one user can have multiple profiles:

-- Incorrect example: Foreign key as primary key in one-to-many relationship
CREATE TABLE Profile (
    userId INT PRIMARY KEY,  -- This prevents one user from having multiple profiles
    gender VARCHAR(10),
    dateOfBirth DATE,
    FOREIGN KEY (userId) REFERENCES User(userId)
);

However, in one-to-one relationships, using a foreign key as a primary key is completely feasible. In this case, each master table record corresponds to exactly one detail table record:

-- Correct example: Foreign key as primary key in one-to-one relationship
CREATE TABLE UserProfile (
    userId INT PRIMARY KEY,
    gender VARCHAR(10),
    dateOfBirth DATE,
    FOREIGN KEY (userId) REFERENCES User(userId)
);

Case Analysis and Performance Considerations

The scenario mentioned in the reference article well illustrates the rationality of using foreign keys as primary keys in one-to-one relationships. In an image storage system, separating large images from thumbnails for performance:

-- Master table stores thumbnails and basic information
CREATE TABLE PhotoThumbnail (
    photoId SERIAL PRIMARY KEY,
    thumbnail BYTEA,
    title VARCHAR(100),
    uploadDate TIMESTAMP
);

-- Child table stores full images, using foreign key as primary key
CREATE TABLE PhotoFull (
    photoId INT PRIMARY KEY,
    fullImage BYTEA,
    FOREIGN KEY (photoId) REFERENCES PhotoThumbnail(photoId)
);

This design ensures:

• Each thumbnail record corresponds to exactly one full image record
• Avoids unnecessary loading of large data
• Maintains referential integrity and data consistency

Design Recommendations and Best Practices

Based on the above analysis, we propose the following design recommendations:

1. Relationship Type Determination
Before deciding to use a foreign key as a primary key, the relationship type between tables must be clarified. For one-to-many relationships, independent primary key fields should be used:

CREATE TABLE UserProfiles (
    profileId SERIAL PRIMARY KEY,  -- Independent primary key
    userId INT NOT NULL,
    profileType VARCHAR(20),
    settings JSONB,
    FOREIGN KEY (userId) REFERENCES User(userId)
);

2. Performance Optimization Considerations
In one-to-one relationships, using foreign keys as primary keys can reduce join operation overhead since two tables can be directly associated through the same primary key value:

-- Efficient query: Direct association through primary key
SELECT u.username, up.gender, up.dateOfBirth
FROM User u
JOIN UserProfile up ON u.userId = up.userId
WHERE u.userId = 123;

3. Data Integrity Assurance
Regardless of the design approach, data integrity must be ensured. Use database constraints to enforce business rules:

-- Ensure integrity of one-to-one relationship
ALTER TABLE UserProfile 
ADD CONSTRAINT unique_user_profile 
UNIQUE (userId);

Conclusion

The decision to use foreign keys as primary keys depends on specific business requirements and relationship models. In one-to-one relationships, this design is reasonable and efficient; in other relationship types, it may violate fundamental database design principles. Developers should deeply understand business scenarios and choose the most appropriate design to ensure system scalability and maintainability.