refrential integrity

[ihento]

Hi mates

I have been using mysql for two years, but am trapped in this liltle problem, please don't laugh.

What I wanna do is simply create two tables, such that a field in one table is a foreign key in the other.

i.e.
create table a ( val1 int primary key )
create table b ( val1 int, val2 int, primary key (val1), foriegn key (val2) references a(val1) ).

What I want is that table b must not have any value in val2 that is not present in table a. and one a value from table a is deleted, all the corresponding values from table b are automatically deleted !!!

Thanks for replying -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[looney]

That syntax looks okay, but val2 needs to have an index, and both tables have to be InnoDB.

This should help you
Bye -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --