# MySQL database with foreign key support

A modified review request has been made here.

Can I please have someone review this simple database design below and tell me if this is the correct way to achieve what I want to do?

What I am trying to do:

1. When a cardID is deleted this cascades and deletes the surveyID associated row.
2. When a trackID is deleted this cascades and deletes the cardID associated row and its associated surveyID row.

ENTITY RELATIONSHIP DIAGRAM

MYSQL CODE

CREATE TABLE Card (
cardID int(11) NOT NULL AUTO_INCREMENT,
trackID int(11) NOT NULL,
fName varchar(21) NOT NULL,
mName varchar(1) DEFAULT NULL,
lName varchar(21) DEFAULT NULL,
email varchar(40) NOT NULL,
isMember int(1) NOT NULL,
PRIMARY KEY (cardID),
FOREIGN KEY (trackID) REFERENCES Tracker(trackID) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE Survey (
surveyID int(11) NOT NULL AUTO_INCREMENT,
cardID int(11) NOT NULL,
trackID int(11) NOT NULL,
q0 int(1) NOT NULL,
q1 int(1) DEFAULT NULL,
q2 int(1) DEFAULT NULL,
q3 int(1) DEFAULT NULL,
q4 int(1) DEFAULT NULL,
q5 int(1) DEFAULT NULL,
PRIMARY KEY (surveyID),
FOREIGN KEY (trackID) REFERENCES Tracker(trackID)  ON UPDATE CASCADE,
FOREIGN KEY (cardID) REFERENCES Card(cardID)ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE Tracker (
trackID int(11) NOT NULL AUTO_INCREMENT,
tName varchar(21) DEFAULT NULL,
tDesc varchar(50) DEFAULT NULL,
PRIMARY KEY (trackID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

• Could you write something about the specification? Some sample data also could help. Aug 13 '12 at 19:15
• Should you not be using InnoDB if you want Foreign Key restraints? Aug 13 '12 at 23:11
• @DavidBarker I am pretty sure you are correct. I tried to switch the engine type but I need to figure out what I need to do different when creating the tables because switching the engine type doesn't work. Find it kind of funny that MyISAM accepts it but converts the foreign keys to keys. Aug 14 '12 at 0:31

1. I'd use longer attribute names (firstName instead of fName, middleName, lastName). It's easier to read and maintain.
2. Are you sure that latin1 is enough for every data? Consider using UTF-8.
• I have made the change to utf8. That was a very good suggestion. For now I will leave the names as is because this is my prototype or proof of concept for a application that is establishing a remote connection to the database. I will definitely take you advice when I get going on the working beta. What about the Engine? I know InnoDB has foreign key support But I am having an issue with my MySql if I try and switch engines, not sure why since key support is on. Aug 13 '12 at 20:57