MySQL Forums
Forum List  »  Newbie

FK error I think
Posted by: John Lucas
Date: April 01, 2005 09:24AM

Hi,

I am new to mySQL and I have been playing around with a new DB and have had good success. However I am having a problem with one table that has 5 FK in it. I am not sure what the problem is. I am running mySQL 4.1.9-nt-max via TCP/IP on WinXP prof.

I created the initial DB with the GUI mySQL Administrator, but things were getting out of hand with that. I did a dump of what I had and manually edited the file. Things are a lot cleaner that way, but I can not create one of my tables. Here is the part of the script. The last table is the one that fails.

DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
`project_id` float NOT NULL default '0',
`name` varchar(20) NOT NULL default '',
`company_id` float NOT NULL default '0',
`end_date` date default NULL,
`start_date` date default NULL,
PRIMARY KEY (`project_id`,`company_id`),
UNIQUE KEY `XPKproject` (`project_id`,`company_id`),
KEY `XIF32projectcompany` (`company_id`),
CONSTRAINT `FK_project_company` FOREIGN KEY (`company_id`) REFERENCES `company` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `files`;
CREATE TABLE `files` (
`modified_date` date default NULL,
`created_date` date default NULL,
`file_author` float default NULL,
`file_name` varchar(20) default NULL,
`file_id` float NOT NULL default '0',
`project_id` float NOT NULL default '0',
`company_id` float NOT NULL default '0',
PRIMARY KEY (`file_id`,`project_id`,`company_id`),
UNIQUE KEY `XPKfiles` (`file_id`,`project_id`,`company_id`),
KEY `XIF33files` (`project_id`,`company_id`),
CONSTRAINT `FK_files_project` FOREIGN KEY (`project_id`, `company_id`) REFERENCES `project` (`project_id`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `component`;
CREATE TABLE `component` (
`component_id` float NOT NULL default '0',
`name` varchar(18) default NULL,
`project_id` float NOT NULL default '0',
`company_id` float NOT NULL default '0',
PRIMARY KEY (`project_id`,`company_id`,`component_id`),
UNIQUE KEY `XPKcomponent` (`project_id`,`company_id`,`component_id`),
KEY `XIF21component_project` (`project_id`,`company_id`),
CONSTRAINT `FK_component_project` FOREIGN KEY ( `project_id`, `company_id`) REFERENCES `project` (`project_id`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `module`;
CREATE TABLE `module` (
`module_id` float NOT NULL default '0',
`name` varchar(20) NOT NULL default '',
`project_id` float NOT NULL default '0',
`company_id` float NOT NULL default '0',
PRIMARY KEY (`module_id`,`project_id`,`company_id`),
UNIQUE KEY `XPKmodule` (`module_id`,`project_id`,`company_id`),
KEY `XIF20module` (`project_id`,`company_id`),
CONSTRAINT `FK_module_project` FOREIGN KEY (`project_id`, `company_id`) REFERENCES `project` (`project_id`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `file_hierarchy`;
CREATE TABLE `file_hierarchy` (
`module_id` float NOT NULL default '0',
`project_id` float NOT NULL default '0',
`file_id` float NOT NULL default '0',
`component_id` float NOT NULL default '0',
`company_id` float NOT NULL default '0',
PRIMARY KEY (`module_id`,`project_id`,`file_id`,`component_id`,`company_id`),
UNIQUE KEY `XPKfile_hierarchy` (`module_id`,`project_id`,`file_id`,`component_id`,`company_id`),
KEY `XIF22file_hierarchy` (`module_id`,`project_id`,`company_id`),
KEY `XIF23file_hierarchy` (`file_id`,`project_id`,`company_id`),
KEY `XIF24file_hierarchy` (`project_id`,`component_id`,`company_id`),
KEY `XIF25file_hierarchy` (`project_id`,`company_id`),
CONSTRAINT `FK_file_hierarchy4` FOREIGN KEY (`project_id`, `company_id`) REFERENCES `project` (`project_id`, `company_id`),
CONSTRAINT `FK_file_hierarchy2` FOREIGN KEY (`file_id`,`project_id`, `company_id`) REFERENCES `file` (`file_id`,`project_id`, `company_id`),
CONSTRAINT `FK_file_hierarchy1` FOREIGN KEY (`component_id`,`project_id`, `company_id`) REFERENCES `component` (`component_id`,`project_id`, `company_id`),
CONSTRAINT `FK_file_hierarchy3` FOREIGN KEY (`module_id`,`project_id`, `company_id`) REFERENCES `module` (`module_id`,`project_id`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ERROR 1005 (HY000) at line 429: Can't create table '.\projectsite\file_hierarchy
.frm' (errno: 150)

Thanks,

John

Options: ReplyQuote


Subject
Written By
Posted
FK error I think
April 01, 2005 09:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.