How to select from table A and then insert selected id inside table B with one query?

I’m trying to implement a very basic banking system. the goal is to have different types of transactions ( deposit, withdraw, transfer ) inside a table and refer to them as IDs inside transaction tables.

CREATE TABLE transaction_types (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR UNIQUE NOT NULL
)

CREATE TABLE transactions (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  type_id INTEGER NOT NULL,
  amount FLOAT NOT NULL
)

What I’m trying to accomplish is:

1. When inserting into transactions table no record can have an invalid type_id ( type_id should exist in transaction_types table )
2. First of all get type_id from transaction_types table and then insert inside transactions table, with one query ( if it’s possible, I’m fairly new )

I’m using Node.js/Typescript and PostgreSQL, any help is appreciated a lot.

Answer

For (1): modify Transactions table definition by adding REFERENCES transaction_types(id) to the end of the type_id column definition prior to the comma.

For (2), assuming you know the name of the transaction_type, you can accomplish this by:

INSERT INTO transactions(type_id, amount) 
VALUES ((SELECT id from transaction_types WHERE name = 'Withdrawal'), 999.99)

By the way, my PostgreSQL requires SERIAL instead of INTEGER AUTOINCREMENT