HSQLDB UNIQUE约束和SQL数组类型 [英] HSQLDB UNIQUE constraint and SQL Array Type

问题描述

我正在使用HSQLDB 2.2.9进行开发。 HSQLDB是支持SQL Array类型的RDBMS之一，我想使用此功能来解决数据库中的某些功能。

I am developing with HSQLDB 2.2.9. HSQLDB is one of the RDBMS's out there that supports the SQL Array type and I want to use this capability to address some functionality in my database.

我一直在运行一些在开发数据库时使用命令行查询，但是当为类型为VARCHAR（24）ARRAY []的列声明时，我不确定HSQLDB如何处理UNIQUE约束。我使用的DDL如下：

I've been running some command line queries as I develop my database, but I am not sure how the UNIQUE constraint is handled by HSQLDB when declared for a column of type VARCHAR(24) ARRAY[]. The DDL I am using follows:

CREATE CACHED TABLE Clients ( 
    cli_id       INTEGER              GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    mrn          VARCHAR(24) ARRAY    DEFAULT ARRAY[] NOT NULL UNIQUE,
    lastname     VARCHAR(48)          NOT NULL,
    firstname    VARCHAR(24)          NOT NULL,
    dob          INTEGER              DEFAULT 0 NOT NULL
);

mrn列包含每个客户唯一的标识符。在开发此数据库时，我了解到客户可能拥有一个或多个。解决此问题的一种方法是使用mrn列为每个客户存储mrn的数组。

The mrn column contains an identifier that is unique for each client. As I've developed this database, I've learned that a client may have one or more than one mrn's. One solution to this problem is to use the mrn column to store an array of mrn's for each client.

在这种情况下，UNIQUE的语义是什么？ HSQLDB是否断言数组中的每个元素都满足UNIQUE列约束？

What are the semantics of UNIQUE in this circumstance? Does HSQLDB assert that each element in the array satisfies the UNIQUE column constraint?

此答案建议PostgreSQL无法对SQL数组类型元素强制执行UNIQUE列约束。我想知道HSQLDB是否也是如此。

This answer Can PostgreSQL have a uniqueness constraint on array elements? suggests that Postgres 9.1 is unable to enforce the UNIQUE column constraint on SQL Array type elements. I wonder if this may be the case for HSQLDB as well.

推荐答案

HSQLDB支持数组的UNIQUE约束。数组列的唯一性约束可以比较存储在不同行中的整个数组，以确保它们是不同的。

HSQLDB supports UNIQUE constraints on arrays. A Unique constraint on an array column results in comparison of the whole array stored in different rows to ensure they are distinct.

当两个数组中的元素相同时，它们被视为相等每个索引和数组的长度都相同。

Two arrays are considered equal when they have equal elements at each index and the lengths of the arrays are the same.

对于您的应用程序，必须确保数组不包含空值并且其元素已排序。

For your application, you must ensure the array does not contain nulls and its elements are sorted.

这篇关于HSQLDB UNIQUE约束和SQL数组类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！