CODEWITHSUNDEEP
postgresql
rotten
rotten

Reputation: 1630

postgresql functional foreign keys

Here is what I would like to do:

create table stuff
   (some_key  varchar primary key,
    some_data varchar)
;

create table obfuscated_stuff
   (some_key_sha1 varchar references digest(stuff (some_key), 'sha1'),
    other_data varchar)
;

In other words, I'd like to set up a foreign key to the other table based on a one way hash without having to put that hash in the original table.

This is a policy/regulatory requirement. I can build the "obfuscated_stuff" table without the FK constraint, but I'd rather have it there.

It appears using a function in an FK constraint is not allowed. I also can't do an FK to a view (which might have the function in it). Any other suggestions?

Upvotes: 1

Views: 735

Answers (1)

Laposhasú Acsa
Laposhasú Acsa

Reputation: 1580

As per the current documentation you can't do this. Only a column or group of column names allowed.

You are right, that you can not make FKs on views. This is true for materialized views too.

You can write a trigger on BEFORE INSERT/UPDATE of the referer column, that checks the referred indexed column. On violation, return NULL or exception. On the referred table's PK, you can even create an index with the hashed values.

Upvotes: 1

Related Questions