CODEWITHSUNDEEP
rpostgresql
Chris
Chris

Reputation: 2256

Add relationship (foreign key) fails

I'm trying to add a relationship between two tables (in schema mittschema).

The following MWE shows the error generated.

library(data.table)
library(DBI)
con <- dbConnect(RPostgres::Postgres()
                                 , host='localhost'
                                 , port='5432'
                                 , dbname='postgres'
                                 , user="xx"
                                 , password="yy")
tbl01 <- data.table(basket=c(1L, 2L, 3L), fruit=c("Apple", "Orange", "Citrus"))
tbl02 <- data.table(basket=c(1L, 2L, 3L), color=c("Red", "Blue", "Yellow"))
RPostgres::dbWriteTable(con, name=DBI::Id(schema="mittschema", table="tbl01"), value=tbl01, overwrite = TRUE)
RPostgres::dbWriteTable(con, name=DBI::Id(schema="mittschema", table="tbl02"), value=tbl02, overwrite = TRUE)
RPostgres::dbGetQuery(con, "alter table mittschema.tbl01 add primary key (basket)")
RPostgres::dbGetQuery(con, "alter table mittschema.tbl02 add primary key (basket)")
# The following command fails
RPostgres::dbGetQuery(con, "ALTER TABLE mittschema.tbl02 ADD CONSTRAINT lnk_tbl_01_tbl_02 FOREIGN KEY ( Basket ) REFERENCES mittschema.tbl01 ( Basket );")
# Error in result_create(conn@ptr, statement) : 
#    Failed to fetch row: ERROR:  there is no unique constraint matching given keys for referenced table "tbl01"
dbDisconnect(con)

How should I write the command to create the relationship?

Edit: For some reason it works now.

Upvotes: 1

Views: 246

Answers (1)

unutbu
unutbu

Reputation: 880957

These lines are merely assigning values to tmp.sql. 

tmp.sql <- "alter table mittschema.tbl01 add primary key (basket)"
tmp.sql <- "alter table mittschema.tbl02 add primary key (basket)"
tmp.sql <- "alter table mittschema.tbl01 add constraint unique (basket)"
tmp.sql <- "alter table mittschema.tbl02 add constraint unique (basket)"

You need to execute these SQL statements before adding foreign keys.

tmp.sql <- "alter table mittschema.tbl01 add primary key (basket)"
res <- RPostgres::dbSendQuery(con, tmp.sql)
tmp.sql <- "alter table mittschema.tbl02 add primary key (basket)"
res <- RPostgres::dbSendQuery(con, tmp.sql)
tmp.sql <- "alter table mittschema.tbl01 add constraint unique (basket)"
res <- RPostgres::dbSendQuery(con, tmp.sql)
tmp.sql <- "alter table mittschema.tbl02 add constraint unique (basket)"
res <- RPostgres::dbSendQuery(con, tmp.sql)

res <- RPostgres::dbSendQuery(con, "ALTER TABLE mittschema.tbl02 ADD CONSTRAINT lnk_tbl_01_tbl_02 FOREIGN KEY ( Basket ) REFERENCES mittschema.tbl01 ( Basket );")

Upvotes: 2

Related Questions