This InnoDB Indexing Thing, I don't get it…

[Durghan]

:huh:

Okay, so I've read the forum threads I could find that relate to my issue but they don't answer my specific problem. I have 4 tables and need to create relationships between them. I understand I need to use InnoDB tables but that for it to work I have to explicitly create indexes for all my columns. When I go to manage indexes, create new, select the column to index and click APPLY I get;

Error 1073

BLOB column “catName” can't be used in key specification with the used table type.

The same message comes up when I check the Unique box but when I check the FULLTEXT box I get;

Error no. 1214

The used table type doesn't support FULLTEXT indexes.

What am I doing wrong?

Thanks.

[CalEvans]

Durghan,

You don't have to create indexes on ALL your fields, just the ones involved in the relationships. Since I have NEVER (Seriously, in 17 years of programming NEVER) seen a relationship on a text field I would suggest you not try to index your text fields.

If you are not new to databases then ignore this section.

1: All your tables should have a primary key field. This SHOULD be something like an autoincrement BIGINT(22) . (Phone numbers, SSN's and other pieces of information may make good CANDIDATE KEYS but for your PRIMARY KEY you don't want anything that means something)

2: Your relationships should be the PK of one table relating into another making a FK.

In the attached datamodel snippet, notice how the relationship form pageType to page is on pagetypeID, the PK for pageType.

HTH,

=C=

[Durghan]

I have to admit, I am very new to Databases…I have a very basic concept of what they can do which amount to pretty much just the fact that they can store and manage information. It's how to make them do it most efficiently that I am lost on.

I just checked my tables and I set them up to all have primary keys solely to make sure they have primary keys…They are all set as int but with differing numeric values. One table has it set at 7 while another has it set to 3, does that matter?

When I look at the table tree on the left side of the screen (I've only just now started trying out this software so excuse improper terminology) My tables all have a columns folder and an indexes folder. All the indexes folder contain an item that is the same name as my primary keys.

Basically I have three tables whose information needs to be provided to a fourth (if I understand this correctly). How do I create my fields in the fourth table that draw their information from the other three? Or am I straying down some dark and ugly path?

Thanks…

-=-=-=-=-=-==-=-=-=

Since posting the above I decided try just linking my 3 tables to the fourth using just the primary key fields and it's worked fine…But I'm confused, I thought I had to link the primary key of a certain table (table B for example) to the field in Table A where it's information would be used. This assumption is based on watching a co-worker create relationships on tables in MS Access where it seemed the line connecting the two tables went from the Primary key in one table to the corresponding field in another.

Can anyone clarify this for me? Thanks.

[CalEvans]

1: Ignore anything you learn about databases in Access. 🙂

Let's define a couple of terms here first:

RELATIONSHIP.

A RELATIONSHIP is just what you think it is. Something stored in table B that related to table A. You can have a RELATIONSHIP in just about any database tool including Excel. (Which is technically not a databasing tool but people use it as such.) People were using RELATIONSHIPs between tables in MySQL long before InnoDB or Gemini came along.

CONSTRAINT:

This is where the database engine enforces a relationship. There are 2 kinds of constraints enforced in MySQL with InnoDB, UPDATE and DELETE. Setting up CONSTRAINTS is usually where most people get messed up because in MySQL it can be a bit confusing. When a CONSTRAINT is setup on a relationship the engine checks to make sure the CONSTRAINT will not be violated before performing the action. Or, in the case of cascading delete CONSTRAINT, it will delete all the related records in table B when the parent record in table A is deleted.

So now to use what we've learned. If you create the tables myParent and myChild. each with a PK of ID (my personal favorite naming scheme) then you can add a relationship to myChild by adding a field OF THE SAME TYPE AND SIZE as myParentID to myChild. This makes myChild.myParentID a FOREIGN KEY. Whenever you add a record to myChild you are supposed to fill in the value of the myParentID with the value of the myParent.myParentID that this new record relates to. (read it again, it makes sense, I promise.)

This is a relationship.

Now, with InnoDB, we can now define CONSTRAINTS as well. Use SQLyog to define your CONSTRAINT. It will allow you to define the type of CONSTRAINT and then it will issue the necessary ALTER TABLE to build it. If you want the myChild record to be deleted when the myParent record is deleted then you need to check ON DELETE and then select CASCADE. now you have a RELATIONSHIP with a CONSTRAINT on it.

Exactly how all of this happens depends on the tool you are using. I have a DM tool for MySQL that automatically adds the field for me when I draw a relationship line. I use some that do not do this. If you are hand-coding the SQL you have to do it all yourself.

I hope this clears up some of the confusion.

=C=

[Durghan]

Cal, thanks for the response. Unfortunately, at this time, after having read your words, I am probably a bit more confused than I think I am. I won't have a chance to work on anything till next week probably so I'll have to come back here and see what I can understand at that time. If I have more questions i will post them.

Which DM Tool are you refering to that lets you draw the lines for the relationships? Thanks.

[Author]

Posts