1

I have table Employee in Postgres:

drop table if exists employee; 
create table employee ( 
 id uuid default uuid_generate_v4 () primary key, 
 first_name varchar not null, 
 last_name varchar not null
 );

And another table salary :

drop table if exists salary; 
create table salary ( 
 check_id uuid default uuid_generate_v4 () primary key,  
 salary int not null, 
 employee_id uuid  references employee (id) 
);

employee_id is the foreign key to id in the Employee table, but I don't understand how to insert a value inside employee_id since UUID is unique.

I am inserting values into Employee table:

insert into employee (first_name, last_name, email, code) values ( 'jonh', 'smith', 'jonh@example.com', '1');

And then if I try insert values into salary table:

insert into salary (salary ) values ('1000');

Then select command will return employee_id value empty.

But if I make it default uuid_generate_v4 (), then result is: Key (employee_id)=(c4ccd745-02ba-4a0e-8586-32e3c6a2b84a) is not present in table "employee".

I understand that because employee_id is a foreign key it should match with uuid in employee, but since uuid is mostly unique, how can I make it work?

Farkhad
  • 89
  • 7

1 Answers1

2

You have to use the uuid that was inserted into the employee table. You can do this with a CTE in a single statement:

WITH new_employee AS (
    INSERT INTO employee (first_name, last_name, email, code)
    VALUES ('jonh', 'smith', 'jonh@example.com', '1')
    RETURNING id
)
INSERT INTO salary (salary, employee_id)
    SELECT 1000, id
    FROM new_employee;
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • Hi Loressa, if the answer solves your issue you should accept it as the correct answer. That way other SO users will know that the question has been correctly answered – Patrick Apr 21 '22 at 11:31