Composite Primary Key On Table Variable In Stored Procedure

sql

composite-keys

data-integrity

sql-server

byNikita Barsukov·Dec 30, 2024

DECLARE @TableVar TABLE (
    Col1 INT,
    Col2 INT,
    PRIMARY KEY (Col1, Col2)
);

INSERT INTO @TableVar VALUES (1, 2);

This script creates a table variable @TableVar with a composite primary key merging Col1 and Col2. The PRIMARY KEY constraint ensures uniqueness for each combination of Col1, Col2. The INSERT statement pops data into the table whilst guarding against duplicate entries for the (Col1, Col2) combo.

The nuts and bolts of composite keys

When handling composite keys, it's all about upholding unequivocal uniqueness and fortified data integrity. Carving a PRIMARY KEY out of multiple columns enables storing distinct combinations of these column values—a handy trick when no lone column can boast a unique identifier on its own.

Filling up the table variable

DECLARE @statistictemp TABLE (
    MajorName VARCHAR(100),
    SubName VARCHAR(100),
    DetailedName VARCHAR(100),
    UniversityID INT,
    StatisticValue DECIMAL(10, 2),
    PRIMARY KEY (MajorName, SubName, DetailedName, UniversityID)
);

Trying to sneak in duplicates to @statistictemp? Expect a swift kick from SQL Server in the form of an error message, enforcing the uniqueness constraint stringently preserved within your composite primary key:

-- This will work like a charm
INSERT INTO @statistictemp VALUES ('Science', 'Biology', 'Genetics', 1, 75.5);

-- This will fail, SQL Server is no fan of copycats
INSERT INTO @statistictemp VALUES ('Science', 'Biology', 'Genetics', 1, 80.0);

Ironically, despite SQL Server's fondness for order and meticulousness, it does not support reliving the past—in this case, revoking or redefining constraints after the table variable is declared. Hence, you should specify primary keys inline during declaration.

Testing, because data integrity is no joke!

"Sun Tzu, The Art of Coding: Know your error messages, for therein lies the solution." Embed testing of these constraints in your development workflow to avoid your application's midlife crisis, aka runtime errors. Not to mention, they offer insight into the exact constraint violation spot.

Going beyond basics: Advanced table types

Table variables are like your trusty ol' bicycle—perfect for small city rides (smaller, short-lived data sets). But SQL Server gives you a sedan and a truck too—temporary tables and permanent tables, capable of handling heavy cargo (more complex indices and constraints). Pick your ride based on scope, performance, and transaction log consumption.

The 'when' of it: Where does a composite key fit like a glove?

Many-to-many relationships, where your composite key plays peacekeeper, handling associative entities.
It's Throwback Thursday every day for historic data where time and entity identifiers are often the only unique combo.
Geographic data—latitude meets longitude to uniquely define every point on our blue planet.

The caveat: No all-you-can-eat composite key buffet

Sure, composite keys are nifty, but don't mistake SQL Server for an all-you-can-eat composite key buffet. They can be a drag on performance at scale, thanks to the extra payload of index maintenance and lookups. A single-column surrogate key is a leaner, meaner alternative consider.

explain-codes / Sql / Composite Primary Key On Table Variable In Stored Procedure

Linked

Foreign Key to non-primary key



Sql: set existing column as Primary Key in MySQL



How to Ignore "Duplicate Key" error in T-SQL (SQL Server) #



Insert ... ON DUPLICATE KEY (do nothing)



Mysql: How to copy rows, but change a few fields?

