Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2009
    Posts
    5

    difference between some relation

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Depending on which tools you are using, this can be normal. Not all tools recognize or enforce cardinality rule differences like that.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  4. #4
    Join Date
    Nov 2009
    Posts
    5
    does orcale,mysql, db2,... doe a distinction in term of sql code?

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

  6. #6
    Join Date
    Nov 2009
    Posts
    5
    ok so

    no difference for

    0,1
    1,1

    0,n
    1,n

  7. #7
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Quote Originally Posted by dportas View Post
    But criticising the weak points of SQL rarely wins me any support in this forum unfortunately.
    SQL has many weaknesses, but the relational model itself has weaknesses. There is no perfect answer (other than myself of course ).

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •