MySQL Foreign Keys

[Dani]

Funnily enough, I have absolutely no experience with MySQL foreign keys. I'd like to change that :) My database schema is rather complicated, and I have a lot of work ahead of me, but let's simplify things.

Suppose I have

Members Table:
id (PRIMARY KEY)
username
first_name
last_name
company
etc

Posts Table:
id (PRIMARY KEY)
message
member_id
member_username
etc

So now what I'd like to do is find a way to use foreign keys (I don't even know if this is possible or what it's designed for) to tell MySQL that posts.member_id relates back to members.id and posts.member_username relates back to members.username. Therefore, if someone changes their username and their record in the members table is updated, all of the posts by the member (whose records also include the username) get updated simultaneously as well.

Doable?

[jwenting]

the MySQL syntax may be slightly different (my main experience is with Oracle) but this is how you'd do it in Oracle:

`ALTER TABLE POSTS ADD CONSTRAINT POSTS_MEMBERS_FK FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER(ID) ENABLE;

[Dani]

But how do you tell it that the username column should update?

My database is denormalized.

[pritaeas]

But how do you tell it that the username column should update?

You shouldn't. In a normalized database the member name should not be repeated.

If you do want to update it, create an update trigger on the members table.

[Dani]

That seems more like what I need then.

P.S. Any advantages to a normalized database vs denormalized for an extremely read-heavy, write-light database?

[pritaeas]

In your case data duplication might be an advantage over normalization, but that's a performance tradeoff which you have to decide on. However with today's database servers the performance difference between the two is getting less and less.

[jwenting]

But how do you tell it that the username column should update?

You could use a trigger on the MEMBERS table which on update updates the field in the POSTS table as well.
What the syntax for that is in MySQL I don't know.

[Dani]

I have no experience with triggers. I did some Googling and came across something like this:

CREATE TRIGGER members_username
    AFTER UPDATE ON members
    FOR EACH ROW BEGIN
        update posts
        set posts.username = NEW.username
        where posts.userid = NEW.userid
    END

I'm getting an error message saying:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 9

[Dani]

Incidentally I tried adding a semi-colon at the end of the UPDATE statement (before the END) but that didn't seem to work either. :-/

[pritaeas]

CREATE TRIGGER members_username
AFTER UPDATE ON members
FOR EACH ROW 
    update posts
    set posts.username = NEW.username
    where posts.userid = NEW.userid

This works for me.

[cereal]

Try to change the delimeter before creating the trigger, like this:

-- temporary delimeter
DELIMETER //
CREATE TRIGGER members_username
    AFTER UPDATE ON members
    FOR EACH ROW BEGIN
        update posts
        set posts.username = NEW.username
        where posts.userid = NEW.userid;
    END//

-- back to default delimeter
DELIMETER ;