Re: Constants for CHAR() FOR BIT DATA?

[Mark Raynsford]

On 2019-09-28T08:43:21 -0700
Rick Hillegas  wrote:

> On 9/28/19 2:11 AM, Mark Raynsford wrote:
> > create table core.users (
> >user_idchar (16) for bit data not null,
> >user_password_hash_algovarchar (64) not null,
> >user_password_hash varchar (64) not null,
> >user_email varchar (128) not null,
> >user_display_name  varchar (128) not null,
> >user_lockedboolean 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''

Hello!

Thanks very much, that's what I was looking for!

-- 
Mark Raynsford | http://www.io7m.com



pgpmvcBVpfhpz.pgp
Description: OpenPGP digital signature

Re: Constants for CHAR() FOR BIT DATA?

[Rick Hillegas]

On 9/28/19 2:11 AM, Mark Raynsford wrote:

create table core.users (
   user_idchar (16) for bit data not null,
   user_password_hash_algovarchar (64) not null,
   user_password_hash varchar (64) not null,
   user_email varchar (128) not null,
   user_display_name  varchar (128) not null,
   user_lockedboolean 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''


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''),

  constraint user_display_name_unique unique (user_display_name)
);

Hope this helps,
-Rick

Constants for CHAR() FOR BIT DATA?

[Mark Raynsford]

Hello!

I'm working with a table that looks like this:

create table core.users (
  user_idchar (16) for bit data not null,
  user_password_hash_algovarchar (64) not null,
  user_password_hash varchar (64) not null,
  user_email varchar (128) not null,
  user_display_name  varchar (128) not null,
  user_lockedboolean 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))

The user_id_nonzero check isn't accepted, however ("Cannot convert types
'CHAR' to 'CHAR () FOR BIT DATA'.").

What I'm actually trying to do: The application I'm working with
identifies users with java.util.UUID values. The application itself
applies a special meaning to the null UUID (all zeroes), and I want to
make sure that all-zero UUIDs never make it into the database. Is there
a valid way to specify the above constraint?

-- 
Mark Raynsford | http://www.io7m.com



pgpdDnKB8JDCN.pgp
Description: OpenPGP digital signature