0

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 <> '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 & txnid=txn1 then no non-withdrawal record with same txn id should be able to be inserted.

4
  • @ArshadMunir You tell that "restricts to insert new row with txn_type <> withdrawal and txn_id = txn1." Does this means: if a row with some txn_type and txn_id is inserted then another row with the same txn_type and txn_id is allowed but the row with another txn_type but the same txn_id is restricted? – Akina Apr 27 at 7:47
  • Is the situation symmetric, so that if a non-withdrawal already exists, you can't also add a withdrawal? Or is it path-dependent? – jjanes Apr 27 at 15:02
  • @Akina correct. – Arshad Munir Apr 28 at 6:57
  • @jjanes my situation is like if a record exist with transaction_type=withdrawal & txnid=txn1 then no non-withdrawal record with same txn id should be able to be inserted. – Arshad Munir Apr 28 at 6:59
0

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.

0

Create user-defined function which checks needed condition. Use it in CHECK constraint:

CREATE TABLE t ( txn_type VARCHAR(255),
                 txn_id VARCHAR(255)
               );
CREATE FUNCTION my_check (text, text)
RETURNS BOOLEAN
AS $$
SELECT NOT EXISTS ( SELECT NULL
                    FROM t
                    WHERE t.txn_id = $2 AND t.txn_type != $1 );
$$ 
LANGUAGE SQL;
ALTER TABLE t 
   ADD CONSTRAINT my_constraint CHECK (my_check(txn_type, txn_id));
INSERT INTO t VALUES ('asd', 'qwe');
1 rows affected
INSERT INTO t VALUES ('asd', 'qwe');
1 rows affected
INSERT INTO t VALUES ('zxc', 'qwe');
ERROR:  new row for relation "t" violates check constraint "my_constraint"
DETAIL:  Failing row contains (zxc, qwe).
SELECT * FROM t;
txn_type | txn_id
:------- | :-----
asd      | qwe   
asd      | qwe   

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.