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/)
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 asPRIMARY INDEX
.Example 1
Simple reproducible example
However, the PRIMARY KEY is automatically increased by integers but still not recognised as PRIMARY INDEX
Example 2