Member Avatar for jakesee

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!

  • 1 Contributor
  • 1 Reply
  • 77 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by jakesee
Member Avatar for jakesee

Ok,

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?

Thanks.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.