Re: Key encryption and relational integrity

Il 26/03/2019 15:24, Adrian Klaver ha scritto:
> On 3/26/19 7:19 AM, Moreno Andreo wrote:
>> Hello folks :-)
>>
>> Is there any workaround to implement key encryption without breaking
>> relational integrity?
>
> This is going to need more information.
OK, I'll try to be as clearer as I can
> For starters 'key' has separate meanings for encryption and RI. I
> could make some guesses about what you want, but to avoid false
> assumptions a simple example would be helpful.
In a master-detail relation, I need to encrypt one of master table PK or
detail table FK, in order to achieve pseudonimization, required by GDPR
in Europe when managing particular data
Imagine I have
Table users
id   surname    last name
1    John            Doe
2    Jane            Doe
3    Foo             Bar

Table medications
id    user_id    med
1     1                Medication
2     1                Ear check
...
...
medications.user_id is FK on users.id
we should achieve

Table medications
id    user_id        med
1    sgkighs98    Medication
2    sghighs98    Ear check

or the opposite (users.id encryption and medications.user_id kept plain)

At a first glance, it IS breaking relational integrity, so is there a
way to manage this encryption internally so RI is kept safe?

Thanks

Moreno.-