CODEWITHSUNDEEP
mysqlrodbcprimary-keytable-index
Rookatu
Rookatu

Reputation: 1507

dbWriteTable removes primary keys and indexes in MySQL

I create a MySQL table as follows:

create table schema_name.test (
col1 varchar(10),
col2 varchar(10),
primary key (col1),
index col1_index (col1)
)

and I write a data.frame to the table using dbWriteTable as follows:

> test <- data.frame(col1 = c("a","b"),col2 = c("apple","banana"))
> types <- c("varchar(10)","varchar(10)")
> names(types) <- names(test)
> dbWriteTable(conn = con, overwrite = TRUE, value = test, name = "test",field.types = types)

after doing this and inspecting the table in the MySQL environment I see that the primary key and index have been removed (con here is a connection object created with dbConnect( odbc::odbc(),"my_dsn_name")). This is undesired.

The behavior I'm trying to achieve is an overwrite of the data in the table whilst leaving the metadata/structure of the table intact.

I understand that I can create another table with the same structure, write the data to that table with dbWriteTable, then use an insert statement to copy the data from this other table into the target table, but this is quite a bit of overhead and feels like it should be unnecessary. Performing sequentially a delete and append of all data is similarly problematic.

Is there a more elegant solution to this problem, and is the destruction of keys and indexes intended behavior? The documentation is quite sparse and doesn't seem to offer solutions to this issue.

Upvotes: 2

Views: 1747

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

First I would like to point out that your current table definition has a redundancy:

CREATE TABLE schema_name.test (
    col1 VARCHAR(10),
    col2 VARCHAR(10),
    PRIMARY KEY (col1),
    INDEX col1_index (col1)
);

MySQL will automatically create an index on the primary key, so your index definition is unnecessary.

Regarding your actual R problem, if you don't want R to overwrite the table, then try calling dbWriteTable with overwrite=FALSE. From poking around here, it seems that dbWriteTable with overwrite=TRUE might be dropping your MySQL table and then recreating it.

Based on your comments below, if you want to remove all data in the table and then insert new data, you could first truncate the table, then make your call to dbWriteTable:

dbGetQuery(con, "TRUNCATE TABLE schema_name.test")
dbWriteTable(conn=con, overwrite=FALSE, append=TRUE, value=test, name="test", field.types=types)

Upvotes: 1

Related Questions