CODEWITHSUNDEEP
mysqlsqldatabase-designforeign-keys
abruski
abruski

Reputation: 871

Referencing Table & Referenced Table

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

MySQL Workbench screenshot

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?

Upvotes: 14

Views: 27629

Answers (5)

Cybercartel
Cybercartel

Reputation: 12592

One country can have many settlements so the country table is the referenced table and the settlement table is the referencing table. I don't know about mandatory field.

Upvotes: 0

Cyrille
Cyrille

Reputation: 11

The Mandatory check box, if it seems to have no effect in generated SQL, has some effect on the graphic. If unchecked, you can see little circle at concerned relation line extremity (for Relationship notation crow's foot).

It means that the foreign key could be null, aka. not mandatory.

Upvotes: 1

abruski
abruski

Reputation: 871

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.

Upvotes: 20

onedaywhen
onedaywhen

Reputation: 57093

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.

Upvotes: 8

bpgergo
bpgergo

Reputation: 16057

settlements table (with the country_id field) is referencing to countries table (the id field)

I suppose mandatory checkbox has nothing to do with the relation. It is simply a not null constraint on the field. But you should consult the user manual of MySQL Workbench on this one.

Upvotes: 2

Related Questions