examples of INNODB & foreign keys

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!

"Steve" <Steve@127.0.0.1> wrote in message
news:jmp7r2-ebu.ln1@barnyard.sweetpig.dyndns.org...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Thu, 21 Jul 2005 23:03:51 -0400, matt in alt.comp.databases.mysql
> wrote:
>>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!
>
> create table xxxx
> (
> xxxID int not null auto_increment primary key,
> name varchar(80),
> job varchar(30),
> xxx00ID int not null references DDD(dddID)
> )
> type=InnoDB;

anybody got anything w/ an explanation?

"Steve" <Steve@127.0.0.1> wrote in message
news:ttt8r2-ef3.ln1@barnyard.sweetpig.dyndns.org...
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Fri, 22 Jul 2005 19:27:01 -0400, matt in alt.comp.databases.mysql
> wrote:
>>"Steve" <Steve@127.0.0.1> wrote in message
>>news:jmp7r2-ebu.ln1@barnyard.sweetpig.dyndns.org...
>>> -----BEGIN PGP SIGNED MESSAGE-----
>>> Hash: SHA1
>>>
>>> On Thu, 21 Jul 2005 23:03:51 -0400, matt in alt.comp.databases.mysql
>>> wrote:
>>>>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!
>>>
>>> create table xxxx
>>> (
>>> xxxID int not null auto_increment primary key,
>>> name varchar(80),
>>> job varchar(30),
>>> xxx00ID int not null references DDD(dddID)
>>> )
>>> type=InnoDB;
>
>>anybody got anything w/ an explanation?
>
> http://dev.mysql.com/doc/mysql/en/innodb.html
> http://dev.mysql.com/doc/mysql/en/in...nstraints.html
>

maybe if i explain what i'm doing and see if i'm talking about the right
thing. I currently have a database with 4 tables. 3 tables contain
information and a id for each entry. the 4th table is a relationship table
and it connects each table. right now the reference table only has the ids
of each of the other tables in it, but they are not set up as foreign keys.
If i erase an entry from a table, when i try to lookup the data in the
reference table, it is blank. Lastly, i have to do a left join each time i
look up data. I'm looking to find out if A) i'm doing this correctly, B) if
there is a better way of doing it, C), if i can look up the data with out
doing a left join. if anyone has an example (i.e., showing the creation of
the table, the data contained, and how the data is extracted) i'd appreciate
it.

thanks,

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!
>