Automatically populate SQL column based on foreign key constraint

Table 1:

parent_id string_id
5001      a
8759      b
3957      c

string_id is the primary key for Table 1. parent_id has an UNIQUE constraint on it.

Table 2:

child_id parent_id string_id
1        5001      a
2        8759      b
3        3957      c

child_id is the primary key for Table 2. string_id is the foreign key to Table 1.

When I populate new row into Table 2, I do NOT want to insert the parent_id in my query by joining/merging Table 1.

INSERT INTO table_2 (string_id)
VALUES ('a'),
       ('b'),
       ('c')

Could anyone give me suggestion whether SQL can populate the parent_id automatically by using built in relationship (e.g. constraint, foreign keys, cascade)? If join is the only way, could you also let me know so that I can stop investigating?

Basically, I want to know what is the most elegant way of solving this problem. I could use join with INSERT, but I think that is an ugly query for such a simple functionality.

Thank you.

Solution 1:

The purpose of storing tables with foreign key constraints is so that the data does not need to be duplicated. SQL then offers the JOIN for looking up values.

I would strongly encourage you to use JOIN to look up the parent_id when you need it. If you like, you can write the query once and use a JOIN -- and then anyone using the view would have the parent_id.