将一个表的uuid传递到另一个表作为postgresql中的外键值

发布于 2025-01-22 19:50:36 字数 1539 浏览 2 评论 0原文

我有table 员工在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
 );

另一个表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) 
);

lightee_id是ID中的外键表，但我不明白如何在雇员中插入一个值，因为UUID是唯一的。

我将值插入雇员表：

insert into employee (first_name, last_name, email, code) values ( 'jonh', 'smith', '[email protected]', '1');

然后，如果我尝试将值插入salary表：

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

然后 select 命令将返回雇员>雇员> /代码>值空。

但是，如果我将其制作默认uuid_generate_v4（），则结果为：key（losperee_id）=（C4CCD745-02BA-4A0E-8586-32E3C6A2B84A）不存在于表“雇员”中。<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<。 /code>

我明白，因为lightee_id是一个外键，它应该与雇员中的uuid匹配，但是由于uuid大多是唯一的，我该如何使其正常工作？

原文

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', '[email protected]', '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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

血之狂魔 2025-01-29 19:50:36

您必须使用已插入雇员表的uuid。您可以使用a cte 一个语句：

WITH new_employee AS (
    INSERT INTO employee (first_name, last_name, email, code)
    VALUES ('jonh', 'smith', '[email protected]', '1')
    RETURNING id
)
INSERT INTO salary (salary, employee_id)
    SELECT 1000, id
    FROM new_employee;

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', '[email protected]', '1')
    RETURNING id
)
INSERT INTO salary (salary, employee_id)
    SELECT 1000, id
    FROM new_employee;

回复收藏 0 原文

~没有更多了~