SQL Foreign Key Issue

Question

I'm getting an issue with MySQL for a seeming simple addition of a foreign key. I asked Google, but to no avail. Here goes:

Create first table with:

| users | CREATE TABLE `users` (
  `username` varchar(32) NOT NULL DEFAULT '',
  `firstname` varchar(128) DEFAULT NULL,
  `lastname` varchar(128) DEFAULT NULL,
  `password` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

Create second table with:

| contacts | CREATE TABLE `contacts` (
  `username` varchar(32) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `phonenumber` varchar(32) DEFAULT NULL,
  `address` varchar(128) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

Now I need to add a foreign key which links 'contacts' with 'users'.

ALTER TABLE contacts ADD FOREIGN KEY (username) references USERS(username));

But I get this error: ERROR 1064 (42000): 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 ')' at line 1

The objective is obviously to ensure that all entries in 'contacts' have a corresponding 'username' entry in 'users'.

Environment: Ubuntu 11.04, MySQL 5.1.58

I must be making a stupid mistake somewhere. Suggestions welcome.

Answer 1

First, change the engine of these 2 table from MyISAM to InnoDB. MyISAM does not support FOREIGN KEY constraints:

ALTER TABLE users
ENGINE = InnoDB ;

ALTER TABLE contacts
ENGINE = InnoDB ;

Then add the FOREIGN KEY, removing the extra parenthesis. There are 2 ways to do this. With the code you had, that will automatically add an index (key) on column username:

ALTER TABLE contacts 
  ADD FOREIGN KEY (username) 
    REFERENCES users(username);

Or explictedly adding the Index (Key) yourself and the Foreign Key constraint (you also choose the names of the index and constraint yourself):

ALTER TABLE contacts 
  ADD KEY username_ie (username),
  ADD CONSTRAINT users_contacts_fk
    FOREIGN KEY (username) 
    REFERENCES users(username);