MySQL: How to do foreign keys?

Question

I wish to have a foreign key in a table but I have no idea how to do it. I wish have a UserID Column in the "wall" table so that I can link back and get the userid's details etc. How would i go about doing this?

Wall Table:

Users Table:

Answer 1

1) In order to have a foreign key column called userid in the WALL table, you need to create the column - skip to #2 if the column already exists:

ALTER TABLE WALL
  ADD COLUMN userid INT(25) NOT NULL

If you want to allow WALL records to exist without the userid being specified:

ALTER TABLE WALL
  ADD COLUMN userid INT(25) NULL

2) Now you add the foreign key constraint:

ALTER TABLE WALL
 ADD FOREIGN KEY (userid) REFERENCES USERS(userid)

Answer 2

Add a UserID column to the Wall table and then create a foreign key on it. In SQL, it'd look like this:

CREATE TABLE Wall (
    ...
    UserID INT,
    ...
    FOREIGN KEY (UserID) REFERENCES User (UserID) ON UPDATE CASCADE ON DELETE RESTRICT,
    ...
) ENGINE=InnoDB;

Note that you may not want to have ON DELETE RESTRICT. That's the most restrictive option. Read up about it on the MySQL website: http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html