|
|
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!
>
[Back to original message]
|