I am new to postgres and building out schemas for an institute. I was sent the most current data given by the institute. I built out a schema and created primary keys and foreign keys based on the UUIDs. When building the schema I would wrangle the big csv file in R studio and pull out each table based on the unique traits. I would then upload the unique tables/csv files to DBeaver. In DBeaver I would then join the table IDs back onto each table based on the unique trait to populate the foreign key. Finally I would then remove redundant columns that were needed to join the foreign key and primary key and add primary and foreign key constraints.
The institute has now sent me new data for the schema. What is the appropriate method to upload new data to each table following wrangling in R studio and how does the database know how to populate foreign keys if the unique columns are not present in the tables any more?
Side not, this data base is not fully normalized on purpose. We wanted to keep the schema simple for ease of backend querying from people who do not know databases.
Building relationship/adding foreign key based on uniqueness.
update survey s
set visit_id =
(select v.visit_id
from visit v
where (v."date", v.survey_time, v.site) = (s."date", s.survey_time, s.site))