英文:
How to alter data constraint in duckdb R
问题
I'm here to help with the translation:
我正在尝试在duckdb（R API）中将 Not Null 约束修改为 Null 约束，但无法成功。以下是问题的示例：
drv <- duckdb()
con <- dbConnect(drv)
dbExecute(con, "CREATE TABLE db(a varchar(1) NOT NULL, b varchar(1) NOT NULL)")
d <- data.table(a = 1:3, b = c("a", NA, "b"))
# 测试 'not null' 约束
dbWriteTable(con, name = "db", value = d, append = TRUE)
Error: Constraint Error: NOT NULL constraint failed: db.b
在这里，我试图修改 'db' 以允许我在列 b 中写入具有 NA 值的 'd'。
> dbSendQuery(con, "ALTER TABLE db ALTER COLUMN b SET DEFAULT NULL")
<duckdb_result 46970 connection=b6ff0 statement='ALTER TABLE db ALTER COLUMN b SET DEFAULT NULL'>
> dbWriteTable(con, name = "db", value = d, append = TRUE)
Error: Constraint Error: NOT NULL constraint failed: db.b
似乎 'dbSendQuery' 用于修改表格（至少没有错误），但很明显约束并未更改。对于如何使约束更改生效，有任何想法吗？
英文:
I am trying to alter a Not Null constraint to a Null constraint in duckdb (R api) and can't get it to stick. Here is an example of the problem.
drv<- duckdb()
con<- dbConnect(drv)
dbExecute(con, "CREATE TABLE db(a varchar(1) NOT NULL, b varchar(1) NOT NULL)")
d<- data.table(a = 1:3, b=c("a", NA, "b"))
#test 'not null' constraint
dbWriteTable(con, name = "db", value = d, append = T)
Error: Constraint Error: NOT NULL constraint failed: db.b
So here I try and alter 'db' to allow me to write 'd' with its NA value in column b
> dbSendQuery(con, "ALTER TABLE db ALTER COLUMN b SET DEFAULT NULL")
<duckdb_result 46970 connection=b6ff0 statement='ALTER TABLE db ALTER COLUMN b SET DEFAULT NULL'>
> dbWriteTable(con, name = "db", value = d, append = T)
Error: Constraint Error: NOT NULL constraint failed: db.b
It appears the 'dbSendQuery' to alter the table worked (no error at least), but its clear that the constraint was not changed. Any ideas on how to make the constraint change stick?
答案1
得分: 2
使用 DROP NOT NULL 而不是 SET NOT NULL。
DBI::dbExecute(duck, "ALTER TABLE db ALTER COLUMN b DROP NOT NULL")
# [1] 0
DBI::dbWriteTable(duck, name = "db", value = d, append = T)
DBI::dbGetQuery(duck, "select * from db")
# a b
# 1 1 a
# 2 2 <NA>
# 3 3 b
英文:
Use DROP NOT NULL instead of SET NOT NULL.
d <- data.table(a = 1:3, b=c("a", NA, "b"))DBI::dbWriteTable(duck, name = "db", value = d, append = T)
duck <- DBI::dbConnect(duckdb::duckdb())
DBI::dbWriteTable(duck, name = "db", value = d, append = T)
# ERROR [2023-05-13 14:11:15] {"msg":"uncaught error","error":"rapi_execute: Failed to run query\nError: Constraint Error: NOT NULL constraint failed: db.b","where":["DBI::dbWriteTable(duck, name = \"","DBI::dbWriteTable(duck, name = \"",".local(conn, name, value, ...)"],"pid":"511449"}
# Error: rapi_execute: Failed to run query
# Error: Constraint Error: NOT NULL constraint failed: db.b
Fixed:
DBI::dbExecute(duck, "ALTER TABLE db ALTER COLUMN b DROP NOT NULL")
# [1] 0
DBI::dbWriteTable(duck, name = "db", value = d, append = T)
DBI::dbGetQuery(duck, "select * from db")
# a b
# 1 1 a
# 2 2 <NA>
# 3 3 b
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论