Re: About many to many relationships

[Roland Bouman]

Hi Felix, thanks for replying.

Felix Geerinckx wrote:
> Even when it is listed as a feature, I consider it
> a bug.
> As you say, no other RDBMS allows. So using this
> 'feature' could lead to a porting nightmare.
>

That's why I'm so curious. It leads to all kinds of interesting problems. For example, when you look at the information_schema.key_column_usage table describing the example:

select *
from information_schema.key_column_usage
where constraint_name = 'fk_b_a'
\G

CONSTRAINT_CATALOG: NULL
CONSTRAINT_SCHEMA: test
CONSTRAINT_NAME: fk_b_a
TABLE_CATALOG: NULL
TABLE_SCHEMA: test
TABLE_NAME: b
COLUMN_NAME: a_name
ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
REFERENCED_TABLE_SCHEMA: test
REFERENCED_TABLE_NAME: a
REFERENCED_COLUMN_NAME: name

I know what it means, but it's definitely not true(POSITION_IN_*UNIQUE*_CONSTRAINT). When we try to find the corresponding constraint, it fails (of course, there is no such constraint):

select *
from information_schema.key_column_usage
where table_name = 'a'
and column_name = 'name'
\G

Empty set (0.01 sec)

> It would be interesting to model a simple
> many-to-many relationship (e.g. users <->
> user groups) and see what anomalies you will
> encounter when:
>
> making a user member of a group
> removing a member (who is member of several other
> groups) from a group
> changing the username of a user
> etc...

In the docs (same link) there is this phrase:

A deviation from SQL standards: If in the parent table there are several rows that have the same referenced key value, then InnoDB acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a RESTRICT type constraint, and there is a child row with several parent rows, InnoDB does not allow the deletion of any of those parent rows.

So that's what happens when deleting parent rows.

> (but don't count on me to do this, because it's a
> bug ;-)
>

Anyway, thanks for your thoughts on this. I appreciate it a lot.