Hi Jonathan, thanks for your reply.
Jonathan Shaltz wrote:
> I'm not sure you really have a many-many
> relationship here. To be many-many, each record
> in A must be able to correspond to zero or more
> records in B, and vice versa. In your sample
> schema, the only way to make a Child correspond to
> more than one _different_ Parent is by duplicating
> rows in the Child table.
I see now that I should've made the example a bit more clear. Consider this:
create table A(
id int unsigned not null auto_increment
, name varchar(30) not null
, constraint pk_A primary key(
id
)
, index i_A (
name
)
)
engine=innodb
;
create table B(
id int unsigned not null auto_increment
, a_name varchar(30) not null
, constraint pk_b primary key(
id
)
, constraint fk_b_a foreign key (
a_name
) references a (
name
)
)
engine=innodb
;
insert into a (name) values ('name');
insert into a (name) values ('name');
insert into b (a_name) values ('name');
insert into b (a_name) values ('name');
mysql> select * from a;
+----+------+
| id | name |
+----+------+
| 1 | name |
| 2 | name |
+----+------+
2 rows in set (0.02 sec)
mysql> select * from b;
+----+--------+
| id | a_name |
+----+--------+
| 1 | name |
| 2 | name |
+----+--------+
2 rows in set (0.00 sec)
Now, each record in `a` corresponds to two records in `b`, and each record in `b` correspondes to two records in `a`, right?
There are no duplicates either, each record in both tables can be uniquely identified by their primary key.
> Maybe I misunderstand your purpose, but it looks
> like your Child table is acting as the
> intersection table.
It not a question of purpose. I'm aware of the usual design pattern to implement many to many relationships in a relational databases. It just puzzles me that this is even possible: there's no other rdbms that I know that allows one to create a foreign key that refers to a set of columns that's not in a unique (or primary key) constraint, whereas innodb is happy to let you do it as long as there's and index that contains these columns provided that these columns are adjacent and that there no columns (columns not present in the foreign key definition) appearing before (that is, with a lower ordinal number) this set of adjacent columns. This is a bit cryptic, but maybe this example will clarify:
table parent:
col1 int
, col2 int
, col3 int
with index:
1 col1
2 col3
3 col2
table child:
colA int
, colB int
, colC int
If we are to make a foreign key from the child table to the parent table, we can use any combination of column arrangements, as long as the first referencing column maps to col1. So, this:
alter table child foreign key (colA) references parent (col1)
works (Note that we do not need to reference all the columns in the index) but similar foreign keys to col2 or col3 will not work because col1 appears before those.
Now, to a foreign key spanning two columns, we must map to col1 first, and then to col3: col1 and col3 are adjacent index columns (theres no other column inbetween). So this:
alter table child foreign key (colA,colB) references parent (col1,col3)
works. This however:
alter table child foreign key (colB,colA) references parent (col3,col1)
does NOT work, because col3 is not the first indexed column. Also, this:
alter table child foreign key (colA,colB) references parent (col1,col2)
does NOT work, because col1 and col2 are not adjacent index columns: col3 is in between.
I hope you see what I mean.
Basically, I'm just wondering if anyone ever uses this, and what problems they solve with this device. This is most likely not a bug, because to reference explicitly specifies this behaviour. see:
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html
The manual just does not explain why innodb implements this behaviour in favour of the restricting foreign keys to reference columns in a unique or primary key constraint
> You could do this:
>
> Parents (Name)
> Bill
> Mary
>
> Children (Name, ParentName)
> George, Bill
> George, Mary
>
> ...but then your database won't be normalized.
No, I agree, but like at my modified example. Do you agree that the data there is in fact completely normalized?