dbCreatePK: Creates a unique index on a table in a SQLite database

In RSQLite.toolkit: Load Data in SQLite from Tabular Files

Creates a unique index on a table in a SQLite database

Description

The dbCreatePK() function creates a ⁠UNIQUE INDEX⁠ named ⁠<table_name>_PK⁠ on the table specified by table_name in the database connected by dbcon. The index is created on the fields specified in the pk_fields argument.

Usage

dbCreatePK(dbcon, table_name, pk_fields, drop_index = FALSE)

Arguments

dbcon	database connection, as created by the dbConnect function.
table_name	character, the name of the table where the index will be created.
pk_fields	character vector, the list of the fields' names that define the ⁠UNIQUE INDEX⁠.
drop_index	logical, if TRUE the index named ⁠<table_name>_PK⁠ will be dropped (if exists) before recreating it. If FALSE, it will check if an index with that name exists and eventually stops. Default to FALSE.

Value

nothing

Examples

# Create a database and table, then add a primary key
library(RSQLite.toolkit)

# Set up database connection
dbcon <- dbConnect(RSQLite::SQLite(), file.path(tempdir(), "example.sqlite"))

# Load sample data
data_path <- system.file("extdata", package = "RSQLite.toolkit")

dbTableFromFeather(
  input_file = file.path(data_path, "penguins.feather"),
  dbcon = dbcon, table_name = "PENGUINS",
  drop_table = TRUE
)

dbGetQuery(dbcon, "select species, sex, body_mass_g,
                   culmen_length_mm, culmen_depth_mm 
                   from PENGUINS 
                   group by species, sex, body_mass_g,
                   culmen_length_mm, culmen_depth_mm 
                   having count(*) > 1")

# Create a primary key on multiple fields
dbCreatePK(dbcon, "PENGUINS", 
           c("species", "sex", "body_mass_g",
             "culmen_length_mm", "culmen_depth_mm"))

# Check that the index was created
dbGetQuery(dbcon, 
  "SELECT name, sql FROM sqlite_master WHERE type='index' AND tbl_name='PENGUINS'")

# Clean up
dbDisconnect(dbcon)

RSQLite.toolkit documentation built on Sept. 2, 2026, 9:06 a.m.