英文:
How to defer foreign key constraint in Postgres
问题
我尝试将外键约束设置为可延迟，这样在向查找/枢轴表插入数据时就不会进行检查，直到事务结束。然而，在代码中它不起作用，就像在psql shell中一样。和在psql shell中一样，我在代码中使用begin开始一个事务。
这是SQL语句：
create table campaign_r_company (campaign_id uuid not null references campaign(id) on delete cascade deferrable initially deferred,company_id varchar(32) not null,primary key (campaign_id, company_id));
这是代码：
tx, err := d.Begin()if err != nil {return err}err = h(tx) // 在这个函数中会调用两个数据库查询if err == nil {err = tx.Commit()}
h(tx)函数：
_, err := cxt.Exec(fmt.Sprintf(`INSERT INTO hp_campaign (%s) VALUES (%s)`, proplist("", campaignProps), arglist(1, len(campaignProps))),id, v.Name, created, v.Updated,)if err != nil {return err}v.Id = idv.Created = createdif (opts & StoreOptionStoreRelated) == StoreOptionStoreRelated {err := d.attach("company", "campaign_r_company", v.Companies, v.Id)if err != nil {return err}}
attach()函数：
func (d *Database) attach(entityName string, tableName string, ids []string, campaignID string) error {for _, id := range ids {stmt := fmt.Sprintf(`INSERT INTO %s (%s) VALUES ($1, $2)`, tableName, fmt.Sprintf("campaign_id, %s_id", entityName))_, err := d.db.Exec(stmt, campaignID, id)if err != nil {return err}}return nil}
错误信息：
insert or update on table "campaign_r_company" violates foreign key constraint "campaign_r_company_campaign_id_fkey"
英文:
I tried to set a foreign key constraint deferrable so that it won't be checked when I insert into a lookup/pivot table until end of transaction. However, it woks in psql shell but its just not working in the code. same as in the psql shell, I start a transaction with begin in code as well.
This is the sql:
create table campaign_r_company (campaign_id uuid not null references campaign(id) on delete cascade deferrable initially deferred,company_id varchar(32) not null,primary key (campaign_id, company_id));
Here's the code:
tx, err := d.Begin()if err != nil {return err}err = h(tx) // there are two db queries will be called in this functionif err == nil {err = tx.Commit()}
h(tx):
_, err := cxt.Exec(fmt.Sprintf(`INSERT INTO hp_campaign (%s) VALUES (%s)`, proplist("", campaignProps), arglist(1, len(campaignProps))),id, v.Name, created, v.Updated,)if err != nil {return err}v.Id = idv.Created = createdif (opts & StoreOptionStoreRelated) == StoreOptionStoreRelated {err := d.attach("company", "campaign_r_company", v.Companies, v.Id)if err != nil {return err}}
attach():
func (d *Database) attach(entityName string, tableName string, ids []string, campaignID string) error {for _, id := range ids {stmt := fmt.Sprintf(`INSERT INTO %s (%s) VALUES ($1, $2)`, tableName, fmt.Sprintf("campaign_id, %s_id", entityName))_, err := d.db.Exec(stmt, campaignID, id)if err != nil {return err}}return nil}
Error:
insert or update on table "campaign_r_company" violates foreign key constraint "campaign_r_company_campaign_id_fkey"
答案1
得分: 1
根据更新的代码和后续的评论，我们现在知道问题是两个查询被分别执行，而不是在一个事务中执行。
英文:
From the updated code, and subsequent comments, we know now that the issue was that the two queries were executed separately and not in a single transaction.
答案2
得分: 0
如果您不使用手动事务管理，Go+PG会为您处理此事。在这种情况下，每个语句都是一个单独的事务，并且约束条件在每个事务结束时进行检查。因此，如果存在异常，将会引发异常。
英文:
If you do not use manual transaction management, Go+PG does this for you. In this case any statement is a single transaction and constraints are get checked at the end of each. So exception is raised.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论