Hi all,

(sorry, I am not sure of the technical terms and jargon)

I am currently using MySQL for an "inheritance pattern" (please advice on the correct terminology) where I have a "base table" with auto_increment and "sub_type table" that has id that reference the base id.

I see the people use psuedo SQL that looks similar to the following to express the DB table structure and relation.

CREATE TABLE sub_type {
   sub_type_id UNSIGNED INTEGER
   FOREIGN KEY (sub_type_id) REFERENCE (base_id)

I read that some DBs can automatically help the user DELETE base when sub_type is DELETEd and vice versa.

My questions are:
1 .Is this possible in MySQL 5.0? (I googled but somehow it says it is for InnoDB?)
2. What is the syntax for the SQL
3. How about on INSERT into sub_type/base table?
4. What are pitfalls to take note using this structure, if any?

Thanks in advance!


I found out it is to add ENGINE=INNODB after the CREATE TABLE SQL for the FOREIGN KEY to work.

Now, I have another question.
What is the best practice to retrieve a sub_type if I only have base_id and do not know the destination sub_type?

Do I have to go through each sub_type table and search for the base_id?