当在PostgreSQL中插入一行时，如何自动填充外键

Question

我目前正在Postgres 14下工作。 我有两个桌子的客户和联系人，当我在桌面联系人中添加新联系人时，我想自动从客户名称中找到客户。

CREATE TABLE customers(
   customer_id INT GENERATED ALWAYS AS IDENTITY,
   customer_name VARCHAR(255) NOT NULL UNIQUE,
   PRIMARY KEY(customer_id)
);

CREATE TABLE contacts(
   contact_id INT GENERATED ALWAYS AS IDENTITY,
   customer_id INT,
   contact_name VARCHAR(255) NOT NULL UNIQUE,
   PRIMARY KEY(contact_id),
   CONSTRAINT fk_customer
      FOREIGN KEY(customer_id) 
      REFERENCES customers(customer_id)
);

INSERT INTO customers(customer_name) VALUES('my_first_customer')

因此，从这里我想做这样的事情：

INSERT INTO contacts(customer_id, contact_name ) VALUES('my_first_customer',"thomas")

我想在联系人表中获得此结果：

contact_id	customer_id	contact_name
1	1	“ thomas”，

我试图制定一个函数以将值从名称更改为id，但要获得类型错误。 因为在触发之前被捕获的类型误差。这是我的功能

CREATE FUNCTION get_id_fct() 
RETURNS TRIGGER AS $get_id_fct$
DECLARE val_id INTEGER;
BEGIN 
SELECT customer_id INTO val_id FROM customers WHERE customers.customer_id = NEW.customer_id;
NEW.customer_id := val_id; 
RETURN NEW
END
$get_id_fct$  LANGUAGE plpgsql;

CREATE TRIGGER get_id
BEFORE INSERT ON contacts
FOR EACH ROW EXECUTE PROCEDURE get_id_fct();'

是他们解决的方法吗？还是我不知道执行此任务的具体方法？ 我是SQL的初学者。

Answer 1

您可以在插入语句中进行副查询，

INSERT INTO contacts(customer_id, contact_name ) VALUES (
(select customer_id from customers where customer_name = 'my_first_customer') ,'thomas');

如果有一个以上的客户名称有多个客户，并且如果没有给定名称

不同的方法（@WildPlasser提出）

INSERT INTO contacts(customer_id, contact_name ) 
select customer_id,'thomas' from customers where customer_name = 'my_first_customer';

，则将失败，并为Customer_id插入null值，在这种情况下，当没有给定名称的客户时，将不会创建行。当有多个具有给定名称的客户时，将创建每个记录。

或者，您可以使用而不是触发器创建视图。

create view v_contacts as 
select customer_name, contact_name from customers 
  join contacts on customers.customer_id = contacts.customer_id;

CREATE FUNCTION emp () RETURNS trigger AS $
BEGIN
    
  INSERT INTO contacts(customer_id, contact_name ) 
    VALUES((select customer_id from customers where customer_name = 
       NEW.customer_NAME),NEW.contact_NAME);
    
    RETURN NULL;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER v_contactst
    INSTEAD OF INSERT ON v_contacts
    FOR EACH ROW
EXECUTE FUNCTION emp();    

插入语句

INSERT INTO v_contacts(customer_name, contact_name ) VALUES('my_first_customer','thomas')

，只是注：列customer_name和contact_name不是唯一的，因此此代码不安全，如果customer_name没有存在此customer_name有多个记录