There are two tables in the H2 database: employees and department.

In employees there are fields: ID , DEPARTMENT , FULLNAME , DEPARTMENT_ID .

In department: ID , DEPARTMENT .

Foreign key is provided for department.ID - employees.DEPARTMENT_ID .

I want to make it so that when a new employee is added to the table, the DEPARTMENT_ID field is automatically put down a value equal to the ID field value in the department table for the corresponding DEPARTMENT in both tables.

How can this be implemented?

At the moment I have this request:

 insert into employees(fullName, department) values(:fullName, :department)

and method:

 @Value("${query.insertNewEmployee}") private String insertNewEmployee; @Override public void insertNewEmployee(String fullName, String department) { Map<String, String> map = new HashMap<>(2); map.put("fullName", fullName); map.put("department", department); jdbcTemplate.update(insertNewEmployee, map); }
  • one
    I do not understand the problem, what do you want to do? You have one-to-many communication, several employees work in the same department. How are you going to automatically put down a department when adding a new employee, there are a lot of them and you need to choose a specific one. If there is a gui, do a combo box with the choice of department. - Aleksei Chibisov
  • Apparently, I did not quite correctly describe the question. The department is selected manually. Automatically, in accordance with the selected department, it is necessary that the department_id field is affixed - BigBadDev
  • one
    Then it is clear, the first thing that immediately catches your eye in your Employees table is the department, the department name and then the key to the department, this is not done. leave one key, otherwise duplication of information is obtained, and if you need to get a department name, join two tables: Select Employees.*,Department.department from Employees left join Department on Employees.DEPARTMENT_ID = Department.ID - Aleksei Chibisov
  • Accordingly, manually insert the id, not the name of the department. - Aleksei Chibisov

1 answer 1

make a subquery instead of:

 insert into employees(fullName, department) values(:fullName, :department)

will be:

 insert into employees(fullName, department) values(:fullName, (SELECT id FROM department.ID WHERE name = :department))