Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE a (
    a_id SERIAL PRIMARY KEY,
    a_value  varchar(20),
    confirm boolean
);
CREATE TABLE b (
    b_id SERIAL PRIMARY KEY,
    a_id INT REFERENCES a(a_id) NOT NULL,
    b_value varchar(20)
);

INSERT INTO a (a_value) VALUES ('a1'),('a2'),('a3');
INSERT INTO b (a_id, b_value) VALUES (1, 'a1-b1'),(1, 'a1-b2'),(2, 'f2-b1'),(2, 'a2bbb'),(3, 'a3b3');
3 rows affected

5 rows affected

UPDATE a SET confirm = true WHERE a_id = 1;
1 rows affected

--Не получится изменить для 1
UPDATE a SET a_value = 'AA1' WHERE NOT confirm AND a_id = 1;
--Так получится
UPDATE a SET a_value = 'AA2' WHERE NOT confirm AND a_id = 2;

SELECT * FROM a;


a_id
a_value
confirm


2
a2
null

3
a3
null

1
a1
t


--Не получится изменить для 1
DELETE FROM b WHERE (SELECT NOT confirm FROM a WHERE a_id = 1) AND a_id = 1;
INSERT INTO b (a_id, b_value) VALUES (1, 'AA1-BB1'),(1, 'AA1-BB2'),(1, 'AA1-BB3') 
    WHERE (SELECT NOT confirm FROM a WHERE a_id = 1)

SELECT * FROM b;
ERROR:  syntax error at or near "WHERE"
LINE 4:     WHERE (SELECT NOT confirm FROM a WHERE a_id = 1)
            ^