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

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.

CodePudding user response：

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)
);