时间戳

首页 > 解决方案 > 在插入或删除之前检查是否存在多对多关系

sql - 在插入或删除之前检查是否存在多对多关系

问题描述

我有 3 张桌子

例如：

Book
id
title

Tag
id
name

BookTag
book_id
tag_id

不允许有没有标签的书的目标。即，当我尝试插入/删除数据时，我需要通过多对多的方式在数据库级别检查 Book 至少有一个标签。如果这样的验证失败，它应该抛出constaint violation error或某种类似的东西。我应该如何实施？它可以通过check constraint还是我应该创建一些trigger，如果可以，那么如何？

请帮我。提前感谢您的帮助

标签： sqlpostgresqltriggerssqlalchemyconstraints

解决方案

book您可以通过在表中添加指向表中的标记（任何标记）的外键来在纯数据库级别强制执行此操作book_tag。到目前为止，您的数据库模型如下所示：

create table book (
  id int primary key not null,
  title varchar(50)
);

create table tag (
  id int primary key not null,
  name varchar(50)
);

create table book_tag (
  book_id int not null,
  book_tag int not null,
  primary key (book_id, book_tag)
);

现在，添加指向标签的额外外键：

alter table book add column a_tag int not null;

alter table book add constraint fk1 foreign key (id, a_tag)
  references book_tag (book_id, tag_id) deferrable initially deferred;

现在当您插入一本书时，它可以暂时没有标签，但只有在交易尚未完成时。您需要在提交之前插入标签。如果不这样做，约束将失败，事务将回滚，并且插入不会发生。

注意：请注意，这需要使用可延迟约束（查看deferrable initially deferred），这是SQL 标准的一部分，但很少实现。幸运的是，PostgreSQL 可以。

编辑 - 添加一个例子

考虑到之前修改过的表格，您可以尝试插入不带标签（将失败）和带标签（成功）的书，如下所示：

insert into tag (id, name) values (10, 'classic');
insert into tag (id, name) values (12, 'action');
insert into tag (id, name) values (13, 'science fiction');

-- begin transaction
insert into book (id, title, a_tag) values (1, 'Moby Dick', 123);
commit; -- fails

-- begin transaction
insert into book (id, title, a_tag) values (2, 'Frankenstein', 456);
insert into book_tag (book_id, book_tag) values (2, 10);
insert into book_tag (book_id, book_tag) values (2, 13);
update book set a_tag = 10;
commit; -- succeeds

推荐阅读