I want to insert a new row in a table only if a value of the tuple I want to insert exists in another table. I want to perform that check for more than one value.
Let me show you an example to better understand what I want to do:
I have a table called
personal_transaction. I want to perform the following insert:
INSERT INTO personal_transaction (id, type, date, fund, paid, caid) VALUES (3, credit, `2016-12-28`, 30, 2, 4)
Before inserting the tuple into the table, I want to check if 4 (=
caid) is a valid key in table called
commercial_account and also check if credit is greater than or equal to 30 (=
fund) in the table called
personal_account where id = 2 (=
How can I do this?
personal_transaction has 2 columns
caid which are foreign keys to
For better understanding these are the checks I want to do before inserting:
SELECT * FROM personal_account WHERE aid = 2 AND credit >= 30; SELECT * FROM commercial_account WHERE aid = 4;
If those selects both produce a row each as a result then the following query:
INSERT INTO personal_transaction (type, date, fund, paid, caid) VALUES (credit, `2016-12-29`, 30, 2, 4)
is clear to be done, otherwise no insert occurs.