db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Constants for CHAR() FOR BIT DATA?
Date Sat, 28 Sep 2019 15:43:21 GMT
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


Mime
View raw message