join sql tables without Primary Keys or Foreign Keys common to 2 tables

Question

I have to do an SQL search in several tables.

I need to relate the two through a common "id" but I do not know how because I do not have declared Primary Keys or Foreign Keys.

Follows code snippet:

SELECT a.iban, TO_CHAR(date_, 'month ,yyyy-dd') AS "Opening date", o.client_id AS "Client NR.", op.status
FROM operation o JOIN open_operation op ON o.id_op = op.id
                 JOIN account a ON o.account_id = a.id   
                 JOIN client c ON o.client_id = c.id 

Here is information that needs to be included in the previous query:

SELECT y.name 
FROM agency y JOIN account a ON y.id = a.agency_id

Answer 1

Even if there are no keys declared, you have described these fields as follows:

agency.id = account.agency_id

So a simple JOIN resolveia, depending on the result you want a subselect may be required.

SELECT a.iban
   , TO_CHAR(date_, 'month ,yyyy-dd') AS "Opening date"
   , o.client_id AS "Client NR.", op.status
   , y.name
FROM operation o 
JOIN open_operation op 
   ON o.id_op = op.id
JOIN account a 
   ON o.account_id = a.id   
JOIN client c 
   ON o.client_id = c.id 
JOIN agency y
   ON y.id = a.agency_id

Answer 2

The definition of Primary key and Foreign key are not necessary. They are a definition that limits certain actions of the system.

Though I’m not advised, technically, you can have a database with no defined key.

You may, for example, be defining relationships only in the queries. It is a matter of approach.

for example, the attribute X can represent a relation for 2 or more tables, although it is not widely used, or even for different attributes of the same table in different situations.

It’s all a matter of approach.