Referencing Table & Referenced Table

Question

I'm trying to understand relationships and naturally questions appear.

What does referencing table mean and what does referenced table mean? In the above example which one should be referenced and which one referencing? Lets say for the sake of argument that the settlements table is a child table (settlement cannot exist without a country). Should this child table be referencing or referenced?

I prefer not to open a new question for such a little question: What does that Mandatory checkbox mean? Does it mean that the settlements table is required or that country_id is required? Or maybe something else?

Answer 1

Found a really good explanation in the PostgreSQL documentation.

Say you have the product table that we have used several times already:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Let's also assume you have a table storing orders of those products. We want to ensure that the orders table only contains orders of products that actually exist. So we define a foreign key constraint in the orders table that references the products table:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

Now it is impossible to create orders with product_no entries that do not appear in the products table.

We say that in this situation the orders table is the referencing table and the products table is the referenced table. Similarly, there are referencing and referenced columns.

Answer 2

The referenced table is the 'parent' table.

The referencing table is the 'child' table.

The clue is in the SQL DDL e.g.

ALTER TABLE Settlements ADD
   FOREIGN KEY (country_id)
   REFERENCES Countries (id);

Settlements references Countries, implies Countries is referenced.