Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

InnoDB - Foreign Keys 2

Status
Not open for further replies.

squidster

Technical User
Oct 13, 2002
55
GB
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
 
Everything looks perfect to me but it does behave as you describe;
I would like to hear from sleipnir214 or swampBoogie, they will find what's wrong here.

Anyway, my opinion, I don't see any reason for creating these foreign keys as you can maintain your DB perfectly without.


Bye


Qatqat

The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)
 
Thanks for the comment. It would be great if I get some others.

I know it is possible to maintain without foreign keys, its more a case of wanting to see them work, particularly as they're so new to mysql.

From what I've seen in other forums most people don't bother with foreign keys as they consider them almost more of a liability.

My opinion is they're new so lets see if they work.
 
In the contact table the column contactid is unsigned but in the event table it is not. It seems like Mysql is pernickety about such details.
 
I have tried swampBoogie's suggestion and it works for the event and knowledgeskill table; it still does not want to create the placement one though.

Why?



Qatqat The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)
 
The placement table also had a similar problem with the SBUId. I got the syntax wrong on the FK references bit as well:-

FOREIGN KEY (SBUId) REFERENCES (contact.SBUId)

should be:-

FOREIGN KEY (SBUId) REFERENCES contact (SBUId)

I have got all tables created now so next I'll try it with the cascade statements.

Thanks for all your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top