1

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.

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
);

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.

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

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.

Community
  • 1
  • 1
scottb
  • 9,908
  • 3
  • 40
  • 56
  • "Arrays solve many problems but should not be used as a substitute for tables." It sounds to me as though your mrn's should be in a separate table, each with a foreign key reference to the client id record. Queries are able to handle this sort of thing. – scottb Jan 16 '15 at 05:17

1 Answers1

2

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.

fredt
  • 24,044
  • 3
  • 40
  • 61