DirtyPlaya
New Member
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:\[code\]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);\[/code\]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.