[RESOLVED] MySQL/phpMyAdmin Help with no InnoDB Support

[everythingis]

My hosting provider doesn't support the InnoDB storage engine, so I can't set foreign keys to link my user_id field from my member_table to my event_table that allows each user to create multiple events.

it does support MyISAM, but I can't seem to find a workaround in the mysql documentation to perform the same kind of functionality.

It would be nice to also be able to auto collapse the other table rows if a user deletes their account with foreign keys, but I'm more concerned with making sure that the event_table can be referenced with the same user_id from my member_table.

Anyone know a solution?

Thanks!

[niroshan]

Hi everythingis,

You can use triggers for this.

here is an good article to get you started.

Regards,
niroshan

[everythingis]

Thanks for the article, I don't know how well you know this, but in the MyISAM example they give, it says to create 2 primary keys for the IDs on the child table (one to set as the trigger referenced from the parent table).

However, my MySQL won't allow 2 primary keys to be set on the same table. I don't know if this is something I have to change with some sort of override, which I wouldn't feel confident in doing, or if it's because I've already created my tables, and would have to delete them and start from scratch?

Also, if I already have my tables created, how do I add the trigger to an existing field/table? I think this tutorial assumes the tables have not been created yet.

Here they give the example in 3 steps:

CREATE TABLE myisam_parent
(
mparent_id INT NOT NULL,
PRIMARY KEY (mparent_id)
) ENGINE=MYISAM;

CREATE TABLE myisam_child
(
mparent_id INT NOT NULL,
mchild_id INT NOT NULL,
PRIMARY KEY (mparent_id, mchild_id)
) ENGINE = MYISAM;

CREATE TRIGGER insert_myisam_child
BEFORE INSERT
ON myisam_child
FOR EACH ROW
BEGIN
IF (SELECT COUNT(*) FROM myisam_parent WHERE mparent_id=new.mparent_id)=0
THEN
INSERT error_msg VALUES ('Foreign Key Constraint Violated!');
END IF;
END;

[Weedpacket]

However, my MySQL won't allow 2 primary keys to be set on the same table.

PRIMARY KEY (mparent_id, mchild_id)

That's not two primary keys; it's one primary key on two columns. No table can have more than one primary key; that's why it's called the primary key.

Also, if I already have my tables created, how do I add the trigger to an existing field/table?

That's what the ALTER TABLE statement is for.

And on the subject of your host not providing the InnoDB engine - have you asked if they could add it?

[everythingis]

I made a big stink with my hosting company because they don't support the InnoDB engine, and they don't plan on adding it. So, eventually when my account expires, I'll be switching to another provider.

I reached out to a DBA that my friend works with & he said to just build the logic into the query to input the user_id as a regular integer field named something like member_id. It's not the way to make the trigger, but it's what I was looking for in terms of what I needed just as a reference between the tables.

I realize now that my comment about the primary key was kind of stupid, but I'm learning as I'm going & since I don't have the best foundation for understanding the language yet, even the explainations are somewhat over my head, it's been a struggle to not make an ass out of myself.