I am considering including foreign keys using the InnoDB table type but am having trouble setting these up. I've read all the literature provided by mysql but still have problems.
Basic table schema is:-
#
# Table structure for table `company`
#
CREATE TABLE company (
companyId int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (companyId)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `contact`
#
CREATE TABLE contact (
contactId int(10) unsigned NOT NULL auto_increment,
companyId int(10) unsigned default NULL,
SBUId int(7) unsigned default NULL,
PRIMARY KEY (contactId),
INDEX comp_id (companyId),
FOREIGN KEY (companyId) REFERENCES company (companyId),
UNIQUE KEY SBUId (SBUId)
) TYPE=InnoDB;
#
# --------------------------------------------------------
#
# Table structure for table `event`
#
CREATE TABLE event (
eventId int(10) unsigned NOT NULL auto_increment,
contactId int(10) NOT NULL default '0',
PRIMARY KEY (eventId),
INDEX con_id (contactId),
FOREIGN KEY (contactId) REFERENCES contact (contactId)
) TYPE=InnoDB;
#
# --------------------------------------------------------
#
# Table structure for table `knowledgeskill`
#
CREATE TABLE knowledgeskill (
knowledgeSkillId int(10) NOT NULL auto_increment,
contactId int(10) NOT NULL default '0',
PRIMARY KEY (knowledgeSkillId),
INDEX con_id (contactId),
FOREIGN KEY (contactId) REFERENCES contact (contactId),
UNIQUE KEY contactId (contactId)
) TYPE=InnoDB;
#
# --------------------------------------------------------
#
# Table structure for table `placement`
#
CREATE TABLE placement (
placementId int(10) unsigned NOT NULL auto_increment,
contactId int(10) unsigned NOT NULL default '0',
SBUId int(7) NOT NULL default '0',
PRIMARY KEY (placementId),
INDEX con_id (contactId),
FOREIGN KEY (contactId) REFERENCES contact (contactId),
INDEX sbu_id (SBUId),
FOREIGN KEY (SBUId) REFERENCES (contact.SBUId)
) TYPE=InnoDB;
#
which has all the indexes created for the foreign keys as per the mysql instructions, but I keep getting: -
Can't create table '.\scismwebtest\event.frm' (errno: 150)
when I run this from phpmyadmin.
It creates the first two tables with keys but falls over on the third!
Has anyone got any experience with InnoDb and foreign keys that can help?
thanks
Basic table schema is:-
#
# Table structure for table `company`
#
CREATE TABLE company (
companyId int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (companyId)
) TYPE=InnoDB;
# --------------------------------------------------------
#
# Table structure for table `contact`
#
CREATE TABLE contact (
contactId int(10) unsigned NOT NULL auto_increment,
companyId int(10) unsigned default NULL,
SBUId int(7) unsigned default NULL,
PRIMARY KEY (contactId),
INDEX comp_id (companyId),
FOREIGN KEY (companyId) REFERENCES company (companyId),
UNIQUE KEY SBUId (SBUId)
) TYPE=InnoDB;
#
# --------------------------------------------------------
#
# Table structure for table `event`
#
CREATE TABLE event (
eventId int(10) unsigned NOT NULL auto_increment,
contactId int(10) NOT NULL default '0',
PRIMARY KEY (eventId),
INDEX con_id (contactId),
FOREIGN KEY (contactId) REFERENCES contact (contactId)
) TYPE=InnoDB;
#
# --------------------------------------------------------
#
# Table structure for table `knowledgeskill`
#
CREATE TABLE knowledgeskill (
knowledgeSkillId int(10) NOT NULL auto_increment,
contactId int(10) NOT NULL default '0',
PRIMARY KEY (knowledgeSkillId),
INDEX con_id (contactId),
FOREIGN KEY (contactId) REFERENCES contact (contactId),
UNIQUE KEY contactId (contactId)
) TYPE=InnoDB;
#
# --------------------------------------------------------
#
# Table structure for table `placement`
#
CREATE TABLE placement (
placementId int(10) unsigned NOT NULL auto_increment,
contactId int(10) unsigned NOT NULL default '0',
SBUId int(7) NOT NULL default '0',
PRIMARY KEY (placementId),
INDEX con_id (contactId),
FOREIGN KEY (contactId) REFERENCES contact (contactId),
INDEX sbu_id (SBUId),
FOREIGN KEY (SBUId) REFERENCES (contact.SBUId)
) TYPE=InnoDB;
#
which has all the indexes created for the foreign keys as per the mysql instructions, but I keep getting: -
Can't create table '.\scismwebtest\event.frm' (errno: 150)
when I run this from phpmyadmin.
It creates the first two tables with keys but falls over on the third!
Has anyone got any experience with InnoDb and foreign keys that can help?
thanks