How to impose this exclusion constraint?

Question

I have a key-value table.

CREATE TABLE keyvalues ( key TEXT NOT NULL, value TEXT )

I want to impose a constraint that if a key has an entry with NULL value, it cannot have any other entries. How do I do that?

To clarify: I want to allow ("key1", "value1"), ("key1", "value2"). But if I have ("key2", NULL), I want to not allow ("key2", "value3").

Huh? key is NOT NULL it can't have a NULL value. You are going to show some (pseudo)code explaining what you want to achieve. — Adrian Klaver
– Adrian Klaver, Commented Feb 18, 2021 at 18:26
I mean an entry that looks like ("key", NULL). That is, the value field is NULL. I want to allow ("key1", "value1"), ("key1", "value2"). But if I have ("key2", NULL), I want to not allow ("key2", "value3"). — Craig
– Craig, Commented Feb 18, 2021 at 18:27
Since PostgreSQL supports triggers you can explore BEFORE INSERT. — PM 77-1
– PM 77-1, Commented Feb 18, 2021 at 18:28
What if you have a value present for a key and try to insert a row with the same key and a NULL value? — clamp
– clamp, Commented Feb 18, 2021 at 22:11

Sotis · Accepted Answer · 2021-02-18 21:53:07Z

You can use a trigger, like this:

CREATE OR REPLACE FUNCTION trigger_function() RETURNS trigger LANGUAGE plpgsql AS $function$ begin if exists (select 1 from keyvalues key = new.key and value is null) then RAISE EXCEPTION 'Key-value not allowed'; end if; RETURN new; end; $function$ ;

Then you create the trigger on the table

CREATE TRIGGER trigger_on_table BEFORE INSERT OR UPDATE ON keyvalues FOR EACH ROW EXECUTE PROCEDURE trigger_function();

And test it:

insert INTO keyvalues SELECT 'a','a'

OK

insert INTO keyvalues SELECT 'a','b'

OK

insert INTO keyvalues SELECT 'b',null

OK

insert INTO keyvalues SELECT 'b','b'

ERROR: Key-value not allowed

Collectives™ on Stack Overflow

How to impose this exclusion constraint?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related