Insert new row into table after performing checks in other tables 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 (=paid)

How can I do this?

personal_transaction has 2 columns paid and caid which are foreign keys to personal_account (column aid) and commercial_account (column aid).



For better understanding these are the checks I want to do before inserting:

SELECT * FROM personal_account
WHERE aid = 2 AND credit &gt;= 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.
For checking if 4 or ciad is the valid value or not you can define ciad as the foreign key in your personal_transaction table referencing the primary key of the commercial_account table.
Please check the usage here:
https://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html

Try this:

INSERT INTO personal_transaction (id, type, date, fund, paid, caid)
SELECT 3, credit, `2016-12-28`, 30, 2, 4
where (SELECT credit FROM personal_account WHERE id = 2) &gt;= 30;


I have assumed fund and paid are credit value and id fields respectively in personal_account table please adjust query according to actual fields names and try:

INSERT INTO personal_transaction (id, type, date, fund, paid, caid)
SELECT 3, credit, `2016-12-28`, 30, 2, 4
where (SELECT fund FROM personal_account WHERE paid = 2) &gt;= 30;