替代DB2中的断言

Question

使用DB2，我试图实现一个影响多个表的约束，即断言。让我举一个简单的例子：

假设有两张桌子“人”和“狗”：

CREATE TABLE person (
    id INT NOT NULL PRIMARY KEY, 
    name VARCHAR(255)
)

CREATE TABLE dog(
    id INT NOT NULL PRIMARY KEY, 
    name VARCHAR(255), 
    owner INT NOT NULL REFERENCES person
)

你想确保每个人都拥有，2狗。让我们为这个例子插入2个人和他们的狗：

-- Insert Paul and his two dogs
INSERT INTO person VALUES (1, 'Paul')
INSERT INTO dog VALUES (1, 'Elmo', 1)
INSERT INTO dog VALUES (2, 'Ripper', 1)

-- Insert Amy and only one dog
INSERT INTO person VALUES (2, 'Amy')
INSERT INTO dog VALUES (3, 'Onko', 2)

当然，我可以找到那些没有两只狗的人的身份证，下面是这样的陈述：

SELECT person.id
FROM person 
    LEFT JOIN dog ON person.id = dog.owner 
GROUP BY person.id 
HAVING COUNT(dog.id) <> 2

-- | id |
-- ------
-- |  2 |

如果每个人都拥有两只狗，那么这个结果集当然是空的。为了确保这一点，您可能希望创建一个在DB2中不可用的断言。创建触发器似乎是另一种选择：(让我们先清除表，然后再创建触发器)

DELETE FROM dog
DELETE FROM person

CREATE TRIGGER person_has_two_dogs_1
AFTER INSERT ON person
FOR EACH STATEMENT
WHEN (
    EXISTS (SELECT person.id FROM person LEFT JOIN dog ON person.id = dog.owner GROUP BY person.id HAVING COUNT(dog.id) IS NULL OR COUNT(dog.id) <> 2)
)
SIGNAL SQLSTATE '23506' SET MESSAGE_TEXT = 'A person does not own exactly 2 dogs'

(要完成此触发器，当然还必须创建对person的删除和对狗的插入/更新/删除)

问题是，现在我不能再插入任何人，因为一个新插入的人当然没有狗。根据直觉，我将通过使用一个事务来解决这个问题，即要求DBMS关闭所有约束一秒钟，让我插入一个新的人和他的狗，然后检查约束。

不幸的是，触发器在DB2中不被视为约束，因此不受事务的影响。我尝试过很多种方法：

通过交互式外壳设置自动提交关闭。

UPDATE COMMAND OPTIONS USING c OFF

通过命令行执行一个具有自动提交停用的脚本文件：

db2 +c -tvf filename

通过JDBC和自动提交关闭

conn.setAutoCommit(false)

当然，一切都是徒劳的，因为触发器不被视为约束，因此在事务处理期间也不会关闭。

有人知道如何在DB2中实现这样的断言，或者为一系列命令打开触发器吗？

Answer 1

我认为你需要重新考虑你的数据结构。对于你的简单例子，有主人谁参考狗，而不是狗引用它的主人。

CREATE TABLE person (
    id INT NOT NULL PRIMARY KEY, 
    name VARCHAR(255),
    dog1 INT NOT NULL REFERENCES dog,
    dog2 INT NOT NULL REFERENCES dog
)

CREATE TABLE dog(
    id INT NOT NULL PRIMARY KEY, 
    name VARCHAR(255)
)

您还可以添加一个约束，即dog1<>dog2。现在你需要先加狗，然后加主人。不过，你可能会有流浪狗。

但是，使用此设置，您可以创建一个同时显示狗狗和主人的视图，并且您可以通过该视图添加记录。为了实现你的目标，你可能会在表格上设置更多的限制，但你想让这个问题变得复杂吗？

实际上，当我再想一想的时候，让我们回到简单的话题。

CREATE TABLE person (
    id INT NOT NULL PRIMARY KEY, 
    name VARCHAR(255),
    dogname1 VARCHAR(255) NOT NULL,
    dogname2 VARCHAR(255) NOT NULL
)