高分请教MYSQL问题

[j4sxw]

1.InooDB的表有没有*.MYD,*.MYI文件.

2.要在数据库使用约束的话,表类型一般是用什么?

3.我在数据库中使用了约束,表类型是InooDB,
使用mysqldump -d -p -uroot db_ocms>db_ocms.sql成功
但在新建一个数据库,运行db_ocms.sql,却出现can't create table .\db_ocms\area_tb.frm
请问是什么问题?急需求助,分不够可以加.


我导出的脚本如下:

db_ocms.sql-- MySQL dump 9.11
--
-- Host: localhost Database: ocms_db
-- ------------------------------------------------------
-- Server version 4.0.20a-nt

--
-- Table structure for table `area_tb`
--

CREATE TABLE area_tb (
areaID smallint(6) NOT NULL auto_increment,
area varchar(18) default NULL,
modeID varchar(10) default NULL,
PRIMARY KEY (areaID),
KEY modeID (modeID),
CONSTRAINT `0_366` FOREIGN KEY (`modeID`) REFERENCES `mode_tb` (`modeID`)
) TYPE=InnoDB;

--
-- Table structure for table `data_tb`
--

CREATE TABLE data_tb (
dataID smallint(6) NOT NULL auto_increment,
dataName char(18) default NULL,
dataTime tinyint(4) default NULL,
modeID char(10) default NULL,
PRIMARY KEY (dataID),
KEY modeID (modeID),
CONSTRAINT `0_369` FOREIGN KEY (`modeID`) REFERENCES `mode_tb` (`modeID`)
) TYPE=InnoDB;

--
-- Table structure for table `dept_tb`
--

CREATE TABLE dept_tb (
deptID varchar(6) NOT NULL default '',
deptName varchar(20) default NULL,
principal varchar(20) default NULL,
PRIMARY KEY (deptID)
) TYPE=InnoDB;

--
-- Table structure for table `gridnesting_tb`
--

CREATE TABLE gridnesting_tb (
gridNestingID smallint(6) NOT NULL auto_increment,
gridNesting varchar(18) default NULL,
modeID varchar(10) default NULL,
PRIMARY KEY (gridNestingID),
KEY modeID (modeID),
CONSTRAINT `0_378` FOREIGN KEY (`modeID`) REFERENCES `mode_tb` (`modeID`)
) TYPE=InnoDB;

--
-- Table structure for table `initcase_tb`
--

CREATE TABLE initcase_tb (
initCaseID smallint(6) NOT NULL auto_increment,
initCase varchar(18) default NULL,
modeID varchar(10) default NULL,
PRIMARY KEY (initCaseID),
KEY modeID (modeID),
CONSTRAINT `0_375` FOREIGN KEY (`modeID`) REFERENCES `mode_tb` (`modeID`)
) TYPE=InnoDB;

--
-- Table structure for table `mm5_tb`
--

CREATE TABLE mm5_tb (
taskID varchar(22) NOT NULL default '0',
accountMode varchar(18) default NULL,
isCheck char(1) default NULL,
resultDeal char(1) default NULL,
dataDate date default NULL,
runCaseID smallint(6) default NULL,
gridNestingID smallint(6) default NULL,
areaID smallint(6) default NULL,
initCaseID smallint(6) default NULL,
webPath varchar(200) default NULL,
nodePath varchar(200) default NULL,
resultPath varchar(200) default NULL,
dataID smallint(6) default NULL,
PRIMARY KEY (taskID),
KEY dataID (dataID),
KEY areaID (areaID),
KEY runCaseID (runCaseID),
KEY initCaseID (initCaseID),
KEY gridNestingID (gridNestingID),
CONSTRAINT `mm5_tb_ibfk_1` FOREIGN KEY (`dataID`) REFERENCES `data_tb` (`dataID`),
CONSTRAINT `mm5_tb_ibfk_2` FOREIGN KEY (`runCaseID`) REFERENCES `runcase_tb` (`runCaseID`),
CONSTRAINT `mm5_tb_ibfk_3` FOREIGN KEY (`areaID`) REFERENCES `area_tb` (`areaID`),
CONSTRAINT `mm5_tb_ibfk_4` FOREIGN KEY (`initCaseID`) REFERENCES `initcase_tb` (`initCaseID`),
CONSTRAINT `mm5_tb_ibfk_5` FOREIGN KEY (`gridNestingID`) REFERENCES `gridnesting_tb` (`gridNestingID`)
) TYPE=InnoDB;

--
-- Table structure for table `mode_tb`
--

CREATE TABLE mode_tb (
modeID varchar(10) NOT NULL default '',
modeName varchar(16) default NULL,
modeSort char(1) default NULL,
runSort varchar(18) default NULL,
exePath varchar(150) default NULL,
exeName varchar(20) default NULL,
PRIMARY KEY (modeID)
) TYPE=InnoDB COMMENT='InnoDB free: 3072 kB';

--
-- Table structure for table `node_tb`
--

CREATE TABLE node_tb (
nodeID smallint(5) NOT NULL default '0',
nodeName char(10) default NULL,
nodeIP char(15) default NULL,
prmPort tinyint(4) default NULL,
port char(6) default NULL,
state char(1) default NULL,
hangCount tinyint(4) default NULL,
PRIMARY KEY (nodeID)
) TYPE=MyISAM;

--
-- Table structure for table `right_tb`
--

CREATE TABLE right_tb (
rightID tinyint(4) NOT NULL default '0',
rightName varchar(20) default NULL,
rightValue bigint(20) default NULL,
description varchar(40) default NULL,
PRIMARY KEY (rightID)
) TYPE=MyISAM;

--
-- Table structure for table `role_tb`
--

CREATE TABLE role_tb (
roleID varchar(6) NOT NULL default '',
roleName varchar(20) default NULL,
roleValue bigint(20) default NULL,
nodeList varchar(200) default NULL,
tasks tinyint(4) default NULL,
maxLevel tinyint(4) default NULL,
rightList varchar(80) default NULL,
PRIMARY KEY (roleID)
) TYPE=InnoDB;

--
-- Table structure for table `runcase_tb`
--

CREATE TABLE runcase_tb (
runCaseID smallint(6) NOT NULL auto_increment,
runCase varchar(20) default NULL,
modeID varchar(10) default NULL,
PRIMARY KEY (runCaseID),
KEY modeID (modeID),
CONSTRAINT `0_372` FOREIGN KEY (`modeID`) REFERENCES `mode_tb` (`modeID`)
) TYPE=InnoDB;

--
-- Table structure for table `task_tb`
--

CREATE TABLE task_tb (
taskID varchar(22) NOT NULL default '',
submitTime datetime default NULL,
startTime datetime default NULL,
state char(1) default NULL,
taskSort char(1) default NULL,
nodeList varchar(200) default NULL,
level tinyint(4) default NULL,
maxRunTime tinyint(4) default NULL,
finishTime datetime default NULL,
exeName varchar(18) default NULL,
exePath varchar(150) default NULL,
destineRunTime datetime default NULL,
nodes tinyint(4) default NULL,
runSort char(1) default NULL,
modeID varchar(10) default NULL,
nodeID smallint(5) default NULL,
userID varchar(10) default NULL,
freeList varchar(200) default NULL,
PRIMARY KEY (taskID),
KEY userID (userID),
KEY nodeID (nodeID),
KEY modeID (modeID),
CONSTRAINT `0_408` FOREIGN KEY (`userID`) REFERENCES `user_tb` (`userID`),
CONSTRAINT `0_426` FOREIGN KEY (`modeID`) REFERENCES `mode_tb` (`modeID`)
) TYPE=InnoDB;

--
-- Table structure for table `taskresult_tb`
--

CREATE TABLE taskresult_tb (
taskID varchar(22) NOT NULL default '',
submitTime datetime default NULL,
startTime datetime default NULL,
state char(1) default NULL,
taskSort char(1) default NULL,
nodeList varchar(200) default NULL,
level tinyint(4) default NULL,
maxRunTime tinyint(4) default NULL,
finishTime datetime default NULL,
exeName varchar(18) default NULL,
exePath varchar(150) default NULL,
destineRunTime datetime default NULL,
nodes tinyint(4) default NULL,
runSort char(1) default NULL,
modeID varchar(10) default NULL,
nodeID smallint(5) default NULL,
userID varchar(10) default NULL,
freeList varchar(200) default NULL,
PRIMARY KEY (taskID),
KEY userID (userID),
KEY modeID (modeID),
CONSTRAINT `taskresult_tb_ibfk_1` FOREIGN KEY (`userID`) REFERENCES `user_tb` (`userID`),
CONSTRAINT `taskresult_tb_ibfk_2` FOREIGN KEY (`modeID`) REFERENCES `mode_tb` (`modeID`)
) TYPE=InnoDB;

--
-- Table structure for table `user_tb`
--

CREATE TABLE user_tb (
userID varchar(10) NOT NULL default '',
userName varchar(20) default NULL,
password varchar(32) default NULL,
phone varchar(18) default NULL,
mobilePhone varchar(18) default NULL,
deptID varchar(6) default NULL,
roleID varchar(6) default NULL,
PRIMARY KEY (userID),
KEY deptID (deptID),
KEY roleID (roleID),
CONSTRAINT `0_350` FOREIGN KEY (`deptID`) REFERENCES `dept_tb` (`deptID`),
CONSTRAINT `0_354` FOREIGN KEY (`roleID`) REFERENCES `role_tb` (`roleID`)
) TYPE=InnoDB;

[fish21cn]

如果有约束，导出的sql的不能直接执行，因为导出的sql并没有按照逻辑关系来一一建表。

需要你自己调整， 把没有使用约束的表（或者说主码做外码的表）先建。

[sq老张]

同意楼上的