Create Tables with Composite Primary Keys and One-to-Many Relationships in SQL Server

When designing relational databases in Microsoft SQL Server (MSSQL), there are many cases where a composite primary key is needed. A composite primary key is a primary key that consists of two or more columns. In this article, we’ll show how to create such a table and then define a one-to-many relationship between it and another table using foreign keys that reference the composite key.

This guide includes:

• Practical SQL examples
• Use of PRIMARY KEY and FOREIGN KEY constraints
• Best practices for composite key relationships

📌 Why Use Composite Primary Keys?

Composite primary keys are essential when a single column is not sufficient to uniquely identify a record. This often happens in junction tables, log tables, or in normalized schemas where the combination of two columns provides a unique identity.

✅ Step 1: Create a Table with a Composite Primary Key

Let’s create a table called ExampleTable with a composite primary key composed of two columns: PrimaryKeyColumn1 and PrimaryKeyColumn2.

CREATE TABLE ExampleTable (
    PrimaryKeyColumn1 INT NOT NULL,
    PrimaryKeyColumn2 INT NOT NULL,
    SomeDataColumn NVARCHAR(255),
    CONSTRAINT PK_ExampleTable PRIMARY KEY (PrimaryKeyColumn1, PrimaryKeyColumn2)
);

Explanation:

• PrimaryKeyColumn1 and PrimaryKeyColumn2 together form the composite key.
• SomeDataColumn is just a placeholder for any additional data you want to store.
• The PRIMARY KEY constraint ensures that no two rows will have the same combination of PrimaryKeyColumn1 and PrimaryKeyColumn2.

✅ Step 2: Create a Related Table with a One-to-Many Relationship

Next, let’s create a table called RelatedTable that references the composite key of ExampleTable.

CREATE TABLE RelatedTable (
    RelatedTableID INT IDENTITY(1,1) PRIMARY KEY,
    ForeignKeyColumn1 INT NOT NULL,
    ForeignKeyColumn2 INT NOT NULL,
    AdditionalData NVARCHAR(255),
    CONSTRAINT FK_RelatedToExampleTable FOREIGN KEY (ForeignKeyColumn1, ForeignKeyColumn2)
        REFERENCES ExampleTable (PrimaryKeyColumn1, PrimaryKeyColumn2)
);

Explanation:

• RelatedTableID is the primary key for RelatedTable.
• ForeignKeyColumn1 and ForeignKeyColumn2 are foreign keys pointing to the composite primary key in ExampleTable.
• This creates a one-to-many relationship: one row in ExampleTable can be referenced by many rows in RelatedTable.

🔁 One-to-Many Relationship Explained

In the above setup:

• ExampleTable is the parent (or referenced) table.
• RelatedTable is the child (or referencing) table.
• The FOREIGN KEY constraint guarantees referential integrity — RelatedTable rows must match a valid primary key from ExampleTable.

💡 Best Practices

• Always ensure the data types and nullability of foreign key columns match those of the referenced primary key columns.
• Use explicit constraint names (like PK_ExampleTable, FK_RelatedToExampleTable) for better debugging and maintainability.
• Avoid unnecessary composite keys — prefer single-column keys unless a combination is truly required for uniqueness.

🔍 Frequently Asked Questions (FAQs)

❓ Can I create a foreign key to just one column of a composite primary key?

Technically, no — foreign keys must match all columns of a composite primary key. If you need to reference only part of it, you must either:

• Refactor your schema, or
• Create a surrogate key (e.g., a unique ID) in the parent table.

❓ Can a composite primary key include more than two columns?

Yes! Composite primary keys can have two or more columns — but keep them concise. Too many columns can affect performance and design clarity.

🚀 Conclusion

Using composite primary keys and foreign keys in SQL Server is a powerful technique for maintaining data integrity in normalized databases. By properly defining your relationships, you ensure that your application logic is backed by a strong, consistent schema.

Need help designing your SQL Server database schema? Our team at [Your Company Name] specializes in relational database design, performance tuning, and scalable backend development. [Contact us here] to start your project!

🔧 Sample Use Case

Imagine a School Database where:

• ExampleTable stores courses with CourseID and DepartmentID as a composite key.
• RelatedTable stores enrolled students in those courses.

This real-world pattern ensures that students are only enrolled in valid courses under the correct department.