Posted by JMA on 09/28/50 11:22

Hi Matt,

Maybe the following sample could help you:

-- Create the 3 tables containing information
--
create table table_a (ida int not null primary key) engine=innodb;
create table table_b (idb int not null primary key) engine=innodb;
create table table_c (idc int not null primary key) engine=innodb;

-- Create a relationship table between the 3 'information' tables
--
create table relation_abc
(
ida int not null,
idb int not null,
idc int not null,
constraint pk_abc primary key (ida, idb, idc),
constraint fk_a foreign key (ida) references table_a(ida) on update no
action on delete cascade,
constraint fk_b foreign key (idb) references table_b(idb) on update no
action on delete cascade,
constraint fk_c foreign key (idc) references table_c(idc) on update no
action on delete cascade
)
engine=innodb;

-- Insert sample data
--
insert into table_a values (1);
insert into table_a values (2);
insert into table_a values (3);

insert into table_b values (10);
insert into table_b values (20);
insert into table_b values (30);

insert into table_c values (100);
insert into table_c values (200);
insert into table_c values (300);

insert into relation_abc values (1, 10, 100);
insert into relation_abc values (1, 20, 100);
insert into relation_abc values (2, 20, 200);
insert into relation_abc values (2, 20, 300);
insert into relation_abc values (3, 10, 300);
insert into relation_abc values (3, 20, 300);
insert into relation_abc values (3, 30, 300);

-- After executing the following delete clauses, the relation table is empty
--
delete from table_a where ida=1;
delete from table_b where idb=20;
delete from table_c where idc=300;

I hope this sample show you the advantages of using referential integrity...

Regards

JMA


"matt" <guest@guest.com> a �crit dans le message de news:
r6idnWy_VKqI_33fRVn-iQ@comcast.com...
> does anyone know of any good examples of using INNODB tables and foreign
> keys to manage references to other tables. I want to manage removing
> entries and teh references in other tables.
>
> thanks!
>

• Next in forum: Pass an argument to Load data Infile
• Prev in forum: How do I SELECT system_time_zone?
• Thread view: Re: examples of INNODB & foreign keys

[Reply to this message]