On 9/28/19 2:11 AM, Mark Raynsford wrote:
create table core.users (
  user_id                    char (16) for bit data not null,
  user_password_hash_algo    varchar (64) not null,
  user_password_hash         varchar (64) not null,
  user_email                 varchar (128) not null,
  user_display_name          varchar (128) not null,
  user_locked                boolean not null,

  constraint user_id_key primary key (user_id),
  constraint user_id_nonzero check (user_id !=
  cast('\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0' as char(16) for bit data)),
  constraint user_display_name_unique unique (user_display_name))

Hi Mark,

The SQL Standard syntax for BINARY literals is a little odd. It is

  X'<sequence of hex digits>'


For instance:

  X'FEED'


For more information, please see the description of the CHAR datatype in the Derby Reference Manual: http://db.apache.org/derby/docs/10.15/ref/rrefsqlj57924.html

The following table definition works for me:

connect 'jdbc:derby:memory:db;create=true';

create table core.users
(
  user_id                    char (16) for bit data not null,
  user_password_hash_algo    varchar (64) not null,
  user_password_hash         varchar (64) not null,
  user_email                 varchar (128) not null,
  user_display_name          varchar (128) not null,
  user_locked                boolean not null,

  constraint user_id_key primary key (user_id),
  constraint user_id_nonzero check (user_id != X'00000000000000000000000000000000'),
  constraint user_display_name_unique unique (user_display_name)
);

Hope this helps,
-Rick