Reference Table and Reference Table

Question

I try to understand the relationship and naturally questions arise.

What does a reference table mean and what does a reference table mean? In the above example, which one needs to be referenced and which one is referenced? Suppose, for the sake of argument, that the settlements table is a child table (a calculation cannot exist without a country). Should this child table be referenced or referenced?

I prefer not to open a new question for such a small question: What does this Mandatory flag mean? Does this mean a settlement table is required or country_id is required? Or maybe something else?

Answer 1

Found a really good explanation in the PostgreSQL documentation.

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

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

Suppose also that you have a table in which orders of these products are stored. We want the order table to contain only orders of products that really exist. Thus, we define the foreign key constraint in the order table, which refers to the product table:

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

It is now impossible to create orders with product_no entries that are not displayed in the product table.

We say that in this situation, the order table is the reference table, and the product table is the reference table. Similarly, there are links and columns that are referenced.

Answer 2

A linked table is a parent table.

The link table is the "child" table.

The key is in SQL DDL, for example.

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

References to Country calculations, implies references to countries.

Answer 3

settlements table (with country_id field) refers to countries table (id field)

I suppose a mandatory flag is not relevant. This is just an undefined field constraint. But you should consult the MySQL Workbench user guide on this.

Answer 4

A mandatory flag, if it does not affect the generated SQL, has some effect on the graphics. If the box is unchecked, you can see a small circle on the border of the final relationship line (to indicate the relationship note).

This means that the foreign key can be null otherwise. not necessary.

Answer 5

There can be many settlements in one country, therefore the country table is a reference table, and the calculation table is a reference table. I do not know about the required field.