postgres add a constraint not null if value is true in another table

Question

I would like to know if it's possible to create table with a not null constraint on a column depending of the value of another column in another table. (In postgres)

exemple:

CREATE TABLE IF NOT EXISTS Ref (
    id SERIAL PRIMARY KEY,
    required BOOLEAN DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS Value (
    id SERIAL PRIMARY KEY,
    refId INTEGER REFERENCES Ref(id) NOT NULL,
    value TEXT,
);

So here I would like to add a constraint NOT NULL to value if required is TRUE.

So I tried to use some check but it seem that it only check column inside the same table :(.

Thanks in advance.

You can (a) migrate the required column to the Value table, or (b) duplicate it in both tables and then automatically duplicate the values by trigger functions, or by rule, or (c) implement the constraint in trigger functions applying to table Value while testing the required column in table Ref — Edouard
– Edouard, Commented Nov 25, 2021 at 14:17

Ramin Faracov · Accepted Answer · 2021-11-25 23:33:30Z

1

I understood that when required field is equal to true, then we must set NOT NULL to the Value table's field, else set to must be NULL. You can do this using PostgreSQL check constraints. Firstly we must create a function for checking field values.

CREATE OR REPLACE FUNCTION get_required(p_id integer, p_value text)
RETURNS BOOLEAN AS
$$
declare 
    v_ret bool;
begin
    select required into v_ret from tbl_ref where id = p_id;

    if (v_ret)and(p_value is null) then 
        return false; 
    end if;

    return true; 
END;
$$ LANGUAGE PLpgSQL;

Then we can use this function on the create table process:

CREATE TABLE tbl_val (
    id serial4 NOT NULL,
    ref_id int4 NOT NULL,
    "value" text NULL,
    CONSTRAINT tbl_val_check CHECK (get_required(ref_id, "value")),
    CONSTRAINT tbl_val_pkey PRIMARY KEY (id),
    CONSTRAINT tbl_val_fk FOREIGN KEY (ref_id) REFERENCES tbl_ref(id)
);

answered Nov 25, 2021 at 23:33

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thibaud Over a year ago

thanks a lot ! the function get_required() you created was just what I needed :)

Collectives™ on Stack Overflow

postgres add a constraint not null if value is true in another table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related