MySQL – Insert New Row After Performing Checks in Other Tables

insertMySQL

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 >= 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.

Best Answer

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) >= 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) >= 30;
Related Question