Insert new row into table after performing checks in other tables

Question

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.

Answer 1

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;

Answer 2

This is possible with INSERT INTO .... SELECT statement. I've made some test tables please note they can be different than your tables because you didnt give the tables. But this example should help you on the way. Good Luck

Test tables

CREATE TABLE `personal_transaction` (
  `id` int(11) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `fund` decimal(10,0) DEFAULT NULL,
  `paid` int(11) DEFAULT NULL,
  `caid` int(11) DEFAULT NULL
);

CREATE TABLE `commercial_account` (
  `id` int(11) DEFAULT NULL,
  `fund` decimal(10,0) DEFAULT NULL,
  `caid` int(11) DEFAULT NULL
);

Insert query

Please note that this query will fail if commercial_account table is empty or there are no records matching the WHERE statement.

INSERT INTO 
 personal_transaction
(
    id
  , TYPE
  , DATE
  , fund
  , paid
  , caid
) 
SELECT 
    3
  , 'credit'
  , '2016-12-28'
  , 30
  , 2
  , 4
FROM 
 commercial_account
WHERE
   commercial_account.fund >= 30
 AND
   commercial_account.caid = 4