One-to-many relationship check in Oracle

Is there a beautiful simple solution how to check if two tables have one-to-many relationship between each other using only their names as parameters? I've seen solutions with joining tables on specific fields but can I do it without knowing what foreign keys my tables have? (Or possibly get it from system tables like user_tab_columns etc.)

If you want to find one-to-many relationships then you are looking for the targets of a FOREIGN KEY constraint and you can find that from the data dictionary.

For example, if you have the tables:

CREATE TABLE workers (
  id        NUMBER
            CONSTRAINT workers__id__pk PRIMARY KEY
);

CREATE TABLE jobs (
  id        NUMBER
            CONSTRAINT jobs__id__pk PRIMARY KEY,
  worker_id CONSTRAINT jobs__worker_id__kf REFERENCES workers(id)
);

Then each job is for one worker but one worker can have many jobs.

You can find the

SELECT MAX(ucc.table_name) AS table_name,
       LISTAGG(ucc.column_name, ',') WITHIN GROUP (ORDER BY ucc.position) AS column_name,
       MAX(rucc.table_name) AS referenced_table_name,
       LISTAGG(rucc.column_name, ',') WITHIN GROUP (ORDER BY rucc.position) AS referenced_column_name
FROM   user_constraints uc
       INNER JOIN user_cons_columns ucc
       ON (    uc.owner = ucc.owner
           AND uc.constraint_name = ucc.constraint_name)
       INNER JOIN user_cons_columns rucc
       ON (    rucc.owner = uc.r_owner
           AND rucc.constraint_name = uc.r_constraint_name
           AND rucc.position = ucc.position)
WHERE  uc.constraint_type = 'R'
AND    rucc.table_name = 'WORKERS'
GROUP BY uc.constraint_name;

Which outputs:

Showing that the JOBS table references the WORKERS table via the WORKER_ID and ID columns, respectively.

fiddle