SQLite Composite Primary Keys: Syntax and Practical Guide for Multi-Column Primary Keys

Basic Syntax of Composite Primary Keys

In SQLite, creating tables with multi-column primary keys requires using table-level constraint syntax. According to official documentation specifications, the correct syntax format is as follows:

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  column3 data_type,
  PRIMARY KEY (column1, column2)
);

This syntax defines the PRIMARY KEY constraint as a table-level constraint, with the column names that constitute the primary key specified within parentheses. Unlike single-column primary keys, composite primary keys require that the combination of values from all specified columns must be unique within the table.

Column Constraints vs Table Constraints

In SQLite, primary key constraints can be defined in two ways: column-level constraints and table-level constraints. Single-column primary keys typically use column-level constraints:

CREATE TABLE single_pk (
  id INTEGER PRIMARY KEY,
  name TEXT
);

Composite primary keys must use table-level constraints because a single column definition cannot specify multiple columns. Table-level constraint syntax is more flexible and can include combinations of multiple columns.

NOT NULL Constraint Requirements

In standard SQLite tables, primary key columns automatically imply NOT NULL constraints. However, when using tables with the WITHOUT ROWID option, primary key columns must be explicitly declared with NOT NULL constraints:

CREATE TABLE without_rowid_table (
  col1 INTEGER NOT NULL,
  col2 TEXT NOT NULL,
  col3 REAL,
  PRIMARY KEY (col1, col2)
) WITHOUT ROWID;

This requirement ensures the integrity and consistency of primary keys, particularly in scenarios involving optimized storage structures.

Foreign Key Relationships with Composite Primary Keys

Composite primary keys are often used in conjunction with foreign key constraints to establish relationships between tables. When defining foreign keys, it's essential to ensure that the referencing columns match the primary key columns in both number and data type:

CREATE TABLE parent_table (
  key1 INTEGER,
  key2 TEXT,
  data TEXT,
  PRIMARY KEY (key1, key2)
);

CREATE TABLE child_table (
  id INTEGER PRIMARY KEY,
  parent_key1 INTEGER,
  parent_key2 TEXT,
  child_data TEXT,
  FOREIGN KEY (parent_key1, parent_key2) 
    REFERENCES parent_table(key1, key2)
);

This design pattern is very common in complex data models, especially in scenarios requiring many-to-many relationships or hierarchical structures.

Performance Optimization Considerations

The order of columns in a composite primary key significantly impacts performance. In scenarios with frequent data insertion, columns with lower change frequency should be placed before columns with higher change frequency:

-- Suboptimal design (may cause frequent index rebalancing)
CREATE TABLE suboptimal_order (
  frequently_changing_col TEXT,
  stable_col INTEGER,
  data TEXT,
  PRIMARY KEY (frequently_changing_col, stable_col)
);

-- Optimized design (better insertion performance)
CREATE TABLE optimal_order (
  stable_col INTEGER,
  frequently_changing_col TEXT,
  data TEXT,
  PRIMARY KEY (stable_col, frequently_changing_col)
);

This ordering strategy reduces B-tree index rebalancing operations and improves insertion performance. For bulk-loaded data, column order should be optimized based on query patterns.

Practical Application Example

Consider a weather data collection system that needs to record various parameter measurements from different stations at different time points:

CREATE TABLE WeatherData (
  wxsite_id INTEGER NOT NULL REFERENCES WeatherSites(wxsite_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  sampdate TEXT NOT NULL,
  samptime TEXT NOT NULL,
  sampby TEXT,
  wxparam_name TEXT NOT NULL REFERENCES WeatherParams(wxparam_name)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  wxquant REAL NOT NULL,
  notes TEXT,
  PRIMARY KEY (wxsite_id, sampdate, samptime, wxparam_name)
);

In this design, the composite primary key ensures uniqueness of records for the same station, same time point, and same parameter. Foreign key constraints maintain referential integrity with station and parameter tables.

Constraint Naming and Syntax Details

If constraint naming is required, the CONSTRAINT keyword can be used:

CREATE TABLE named_constraint (
  col1 INTEGER,
  col2 TEXT,
  col3 REAL,
  CONSTRAINT pk_main PRIMARY KEY (col1, col2)
);

It's important to note that syntax like CONSTRAINT primary PRIMARY KEY is incorrect because primary is a reserved keyword. The correct approach is to use other meaningful constraint names.

Error Handling and Data Integrity

When handling composite primary key conflicts in applications, SQLite throws UNIQUE constraint failed errors. Developers should implement appropriate error handling mechanisms at the application level:

import sqlite3

try:
    cursor.execute("INSERT INTO table_name (col1, col2, col3) VALUES (?, ?, ?)", 
                   (value1, value2, value3))
    connection.commit()
except sqlite3.IntegrityError as e:
    if "UNIQUE constraint failed" in str(e):
        print("Duplicate primary key values, please check data")
    else:
        print(f"Other integrity error: {e}")

Best Practices Summary

When designing composite primary keys, follow these best practices: carefully select the column combinations that constitute the primary key to ensure business logic uniqueness; properly order primary key columns for performance optimization; explicitly add NOT NULL constraints to all primary key columns; ensure column count and type matching in foreign key references; implement comprehensive error handling mechanisms at the application level. These practices help build robust and efficient database applications.