hi

in some db designer tools, we can have

0 , n relation
and

1, n relation

in the generated sql code, i don't see any difference... is it normal?

thanks

Depending on which tools you are using, this can be normal. Not all tools recognize or enforce cardinality rule differences like that.

-PatP

In SQL it is generally impossible to implement a relationship with 1 -> n cardinality using declarative constraints. So although this is an important and common distinction in modelling business requirements it has to be done using procedural code - code which most modelling tools won't generate for you.

does orcale,mysql, db2,... doe a distinction in term of sql code?

Not in SQL DDL code, no. There is no special syntax in any of those DBMSs for a constraint that is mandatory on both sides of the relationship. A foreign key constraint is 1-> 0...n only. The workarounds require triggers and procedural code which are proprietary to the DBMS so that's why general purpose modelling tools are unlikely to support it.

There are a few conditions under which such constraints can be implemented in DDL. For example you can sometimes use CHECK constraints on tables or on views to implement cardinality constraints. See: DBAzine.com: Complex Constraints

There are serious limitations to this approach however. Even the constraints that you can implement this way don't necessarily work like "proper" constraints. A CHECK constraint on table A that references table B may not get checked when B is updated or when a row is deleted from A. If you use two constraints, one on each table, then you'll have to give one of them the DEFERRABLE option and defer constraint checking each time you populate the two tables - which basically puts you right back where we started: you need to use procedural code.

Given that cardinality and referential integrity constraints are such a common business requirement it's pretty poor that SQL DBMSs can't do better. Fundamentally the SQL model itself is at fault because of its lame FOREIGN KEY syntax and lack of multi-table updates. But criticising the weak points of SQL rarely wins me any support in this forum unfortunately.

ok so

no difference for

0,1
1,1

0,n
1,n

SQL has many weaknesses, but the relational model itself has weaknesses. There is no perfect answer (other than myself of course ).

Nobody expects perfection. However I do think it's reasonable to ask database software vendors to show some improvement in basic features after more than 30 years! Especially when you consider the outstanding innovations in other fields of IT during that same period. Commercial DBMS technology has stagnated and shown very little improvement in core features during the last three decades. Part of the reason is of course the cosy vested interests of a market carved up between only three big players.