I'm working on a database design in which I've run into an amusing
problem.  I've got a workaround that's "good enough" but it's not very
satisfying.

I can't talk about the real application so I'll use some suitable
analogues for my real entities.

Suppose you have a table of angels.  Angels are required to be above
reproach in order to properly execute the duties of their office.

Unfortunately angels do sometimes blot their resume and have to serve
some kind of penance, after which they can resume their duties.

In my real application the privacy of the people I'm calling angels
cannot ever be compromised.  If the data were ever to escape into the
wild it would be front-page news.

I keep information about angelic transgressions is in a separate sins
table.

If an angel has never transgressed they will have no row in the sins
table.  If they have transgressed they will have at least one row.

Thus merely leaking the existence of a row would embarrass an angel,
even without disclosing any details.  It must be impossible for an
improper person ever to join the tables.

I can easily prevent access to the sins table with suitable grants but I
need to protect the data at rest too.  Databases get copied; disk
drives get swapped out.

I originally wanted to encrypt the foreign key in the sins table that
relates each sin to the the angel concerned, and of course I wanted to
declare the foreign key constraint.  I hoped this would be to conceal
the existence of a sin because the without the passphrase it would be
impossible to join the tables.

Unfortunately it seems Ingres column encryption isn't compatible with a
foreign key declaration.  

So, I'm open to suggestions.  Hardware-level encryption seems like a
good alternative, but what else?

Alternatively, am I mis-using encryption?  Is it possible to do what I
was trying to do?

--
Roy

UK Actian User Association Conference 2012 will be on Tuesday June 19 2012.
Register now at https://www.regonline.co.uk/ukiua2012
The latest information is available from www.uk-iua.org.uk.