Restrict insertion of new row if a row exists with certain field values Using a Postgres DB. I have a table let say t with columns txn_type, txn_id. I want to add a constraint where it restricts to insert new row if I have a row with particular values existing in table.
e. g row 1
withdrawal, txn1
I am trying to add a constraint where it restricts to insert new row with txn_type &lt;&gt; 'withdrawal' and txn_id = txn1.
e.g it restricts to insert row with following values
non_withdrawal, txn1
My situation is like if a record exist with transaction_type = withdrawal &amp; txnid=txn1 then no non-withdrawal record with same txn id should be able to be inserted.
Answer left in a comment by matigo:
Two ways to do this would be through a BEFORE INSERT trigger or via a stored procedure. With a trigger, you can be sure that every application that writes directly to the database must follow the prescribed rules. With a stored procedure you can (potentially) simplify the entire process if there are multiple steps that must be followed in succession.