CODEWITHSUNDEEP
sqliter
brews
brews

Reputation: 671

Set or create primary key in SQLite from R

I've tried to do a merge() in R between two data.frames but am running out of RAM, so I'd like to export the data.frame to an SQLite database (using the RSQLite library).

Writing the data.frame to the database is simple enough with dbWriteTable(). But, I don't think that I can perform the 'JOIN' operation in SQLite3 without a primary key...

My question is: Is there a way to set/create a primary key for a data.frame when I create an SQLite table in R? If I can't do this, does anyone have a clever work-around they've been using?

I realize that this is an R question, and not so much a stats question...

Upvotes: 4

Views: 3808

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95741

In general, SQL databases join on common values. So you can do a JOIN operation without creating a primary key.

SQLite is more forgiving than most with respect to data and data types. (It doesn't really support typed data in the SQL sense.) It allows stuff like this.

CREATE TABLE v1 (n integer);
INSERT INTO "v1" VALUES(1);
INSERT INTO "v1" VALUES(2);
INSERT INTO "v1" VALUES(3);
INSERT INTO "v1" VALUES(4);
INSERT INTO "v1" VALUES('Oh, bugger.');

CREATE TABLE v2 (n integer);
INSERT INTO "v2" VALUES(1);
INSERT INTO "v2" VALUES(3);
INSERT INTO "v2" VALUES(5);
INSERT INTO "v2" VALUES('Oh, bugger.');

select v1.n from v1 
inner join v2 on (v2.n = v1.n);
1
3
Oh, bugger.

Upvotes: 1

G. Grothendieck
G. Grothendieck

Reputation: 270045

This type of question is discussed on R-sig-DB. In particular, see:

https://stat.ethz.ch/pipermail/r-sig-db/2010q1/000813.html

Upvotes: 3

Related Questions