About many to many relationships

[Roland Bouman]

Hi everyone!

There's just one thing that I keep wondering about....Check it out:

use test;

create table parent(
name varchar(30) not null
)
engine = InnoDB
;

create index i_parent on parent (
name
);

create table child(
name varchar(30) not null
, parent_name varchar(30) not null
)
engine = InnoDB
;

alter table child
add constraint fk_child_parent
foreign key (parent_name)
references parent (name)
;

insert into parent (name) values ('parent_name');
insert into parent (name) values ('parent_name');

insert into child(name,parent_name) values ('child_name','parent_name');

So, what we have here is a foreign key referencing the columns that are neither in a primary key nor in a unique constraint. Effectively this allows for many to many relationships directly, so, *without* an intersection table. As far as I can see, this is something only MySQL supports. MS SQL Server, Oracle, MS Access and loads of other products only support this when the foreign key references the columns that are completely covered by either a PRIMARY KEY or a UNIQUE constraint.

I read in the Reference Manual that this is actually a feature, but what I am wondering about is: did anyone ever use this? Why? I am really, genuinly interested in a real use case. Please, let me know in what cases you used this feature.

Thanks in advance,

Roland.