0-to-many and 1- to-many relationship in PostgreSQL code

moth
February 17, 2023
294 views
0 votes
2 Answers

From a PostgreSQL article on foreign keys constraints at https://www.postgresql.org/docs/current/ddl-constraints.html:

Say you have the product table :
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 non-NULL product_no entries
that do not appear in the products table.

Is this a 0-to-many relationship?

How can I set a one-to-many relationship?

By doing this?

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

If that is the case, why does pgadmin4 generate this crow foot notation that denotes a zero-to-many when I configure the table as above?

Answers

- Bergi
- February 17, 2023 at 6:09 am
- 0 votes
0
this crow foot notation denotes a zero-to-many relationship.

No, it doesn’t. Do not confuse the cardinality (which is directional) with the type of the relation. The circle denotes the lower bound of the orders cardinality (and may be omitted) – see "many" vs "zero or many"/"one or many" crowfoot notation? on Software Engineering.

Marking the product_no as NOT NULL only changes a zero-or-one-to-many relationship into an exactly-one-to-many relationship. Both are commonly referred to as "one-to-many" or "1:N" relationships. This would be expressed as either a dash-dash or dash-circle (or, amiguously, just dash) for the cardinality of products.

Now the circle (or dash) on the crow foot is just about the many side (orders) – is it …-to-zero-or-many or …-to-one-or-many? And in your schema, it is indeed a zero-or-many cardinality, since there can be any number of orders – including 0 – for a product.

_{Notice that an actual 0,1:1,N, 1:1..N or 1..N:1..N relationship (not …:0,N) is surprisingly hard to represent in SQL since you have a chicken-and-egg problem, see How to Create a real one-to-one relationship in SQL Server or 1:N relationship where N must be at least one entry).}

Login or Signup to reply.

- Christophe
- February 24, 2023 at 7:16 pm
- 0 votes
0
You are right but ambiguous about the missing diagram

In your tutorial:
- A product is not necessary used in any order. So, on the many side the relation is optional
- The foreign key constraint ensures that the value in product_no must exist in the table products. But the foreign key can be NULL, in which case there is no value, and the existence check is not applicable (see also Can a foreign key be NULL and/or duplicate). In consequence, the one side of the relation is also optional
This is why the tutorial relation is an optional one-to-many (sometimes abusively called zero-to-many). In crow’s foot notation, the explicit notation would be with hollow circles (representing the O from optional):

If you change the constraint from optional to mandatory, you indeed just have to specify that the foreign key as NOT NULL like you did in your second alternative, which would lead to:

Making the relation mandatory on the "many" side would mean that for every product, there’s at least one order using it. This would require some programmatic enforcement that could not be parsed solely from the SQL structure.

pgadmin4 tool limitations

I’m not an expert of pgadmin4, but a quick look at the diagramming tool documentation shows that it only knows one-to-many and many-to-many relations, and that the dialogues do not allow to make a difference between optional and mandatory. It is for example a known bug that it cannot represent a one-to-one relation.

Looking at the diagrams in the documentation, in similar SO questions and the official issue tracker, it appears that the diagramming tool only knows:

for many

and

for one

It means that the tool cannot make a difference between mandatory (double bar for one, bar and trident for many) and optional (circle bar for one, circle trident for many), and that the symbols used are to be understood as unspecified in this regard. The small dot is therefore ambiguous and could be understood as a fancy notation of the crow’s foot trident.

Conclusion

Since the diagramming tool cannot deal with this subtleties, the reverse engineering from the SQL statement cannot represent these variations in an accurate way, even if it would have the algorithms to detect them in the constraints.
Login or Signup to reply.