Posted by Rik on 05/07/06 15:10

Is it possible to enforce intergrity in linked tables in MySQL?
I'm not sure I've got the english wording right:

What is mean:
I've got:

table1.
id
field1
field2
etc...

table2
id
ref = table1.id
field1
field2
etc...

Now if I want to add a row to table2, ref must exists in table1.id. Is there
a MySQL setting for this I cannot see, or do have to create custom code to
check this?

Similarly, if I delete a row from table1, I want all rows from table2
deleted where ref = table1.id

I could offcourse create a table 'relations', containing:
id (autoincrement int)
table1 (varchar)
field1 (varchar)
relationship (enum('one-to-one','one-to-many'))
table2 (varchar)
field2 (varchar)
delete (int(1) (boolean, wether to delete related records))

And route all queries through a custom made database-object that checks the
table "relations" on every query and acts accordingly.

A MySQL setting that is set in the database (doesn't need to be called in
PHP script) would be preferred, but I can't find one.

Grtz,
--
Rik Wasmus

[Back to original message]