PRIMARY KEY variable is not being recognised as primary index

[OndrejMottl]

I am not sure if this is an issue of {RSQLite} or {DBI}. However, I created an SQLite database and a table using SQL calls, even though I have declared a variable as PRIMARY KEY, the database does not automatically recognise it as PRIMARY INDEX.

Example 1

Simple reproducible example

# Setup
library(DBI)
library(RSQLite)
library(here)

# Create a SQLite database
con <-
  DBI::dbConnect(
    RSQLite::SQLite(),
    here::here("testA.sqlite")
  )

# Make a new table using SQL call
DBI::dbGetQuery(
  con,
  'CREATE TABLE "Datasets" (
  "dataset_id" INTEGER PRIMARY KEY,
  "dataset_name" TEXT
);'
)
#> data frame with 0 columns and 0 rows

# Check that the table has been created
DBI::dbListTables(con)
#> [1] "Datasets"

# Check the content of the table
DBI::dbGetQuery(
  con,
  'SELECT * FROM "Datasets";'
)
#> [1] dataset_id   dataset_name
#> <0 rows> (or 0-length row.names)

# Check the Primary key
DBI::dbGetQuery(
  con,
  "PRAGMA index_list('Datasets');"
)
#> [1] seq     name    unique  origin  partial
#> <0 rows> (or 0-length row.names)

# Manualy create the primary key
DBI::dbGetQuery(
  con,
  "CREATE UNIQUE INDEX dataset_id ON Datasets(dataset_id);"
)

# Check again if the Primary key is present
DBI::dbGetQuery(
  con,
  "PRAGMA index_list('Datasets');"
)
#>   seq       name unique origin partial
#> 1   0 dataset_id      1      c       0

Created on 2024-08-22 with [reprex v2.0.2](https://reprex.tidyverse.org/)

However, the PRIMARY KEY is automatically increased by integers but still not recognised as PRIMARY INDEX

Example 2

# Setup
library(DBI)
library(RSQLite)
library(here)

# Create a SQLite database
con <-
  DBI::dbConnect(
    RSQLite::SQLite(),
    here::here("testB.sqlite")
  )

# Make a new table using SQL call
DBI::dbGetQuery(
  con,
  'CREATE TABLE "Samples" (
  "sample_id" INTEGER PRIMARY KEY,
  "sample_name" TEXT
);'
)
#> data frame with 0 columns and 0 rows

# Check that the table has been created
DBI::dbListTables(con)
#> [1] "Samples"

# Add some data to the table
DBI::dbGetQuery(
  con,
  'INSERT INTO "Samples" ("sample_name") VALUES ("Sample1");'
)

# Check the Primary key
DBI::dbGetQuery(
  con,
  "PRAGMA index_list('Datasets');"
)
#> [1] seq     name    unique  origin  partial
#> <0 rows> (or 0-length row.names)

# Check the content of the table
DBI::dbGetQuery(
  con,
  'SELECT * FROM "Samples";'
)
#>   sample_id sample_name
#> 1         1     Sample1

Created on 2024-08-22 with [reprex v2.0.2](https://reprex.tidyverse.org/)

[krlmlr]

Thanks. What does this look like in the sqlite3 CLI?