mySql error number 1005

I have created the database with the script below.

but it's keep on showing me the error : Please help

CREATE TABLE `user_master` (
  `UID` int(11) NOT NULL,
  `USER_NAME` varchar(50) NOT NULL,
  `PASSWORD` int(10) unsigned NOT NULL default '0',
  `ROLE` varchar(25) NOT NULL,
  `STATUS` varchar(20) NOT NULL default '',
  `CREATED_DATE` date default NULL,
  `UPDATED_DATE` date default NULL,
  `DEPARTMENT` varchar(45) NOT NULL default '',
  `SUPERIOR` varchar(45) NOT NULL default '',
  PRIMARY KEY  (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `profile_master` (
  `PID` int(11) NOT NULL,
  `PROFILE_NAME` varchar(50) NOT NULL,
  `CREATED_DATE` date NOT NULL,
  `UPDATED_DATE` date NOT NULL,
  `APPROVED_DATE` date default NULL,
  `ALERT` tinyint(1) default NULL,
  `PROFILE_TYPE` varchar(50) default NULL,
  `AID` varchar(20) NOT NULL default ' ',
  `STATUS` varchar(20) NOT NULL default '0',
  PRIMARY KEY  (`PID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


CREATE TABLE `incident_details` (
  `IID` int(11) NOT NULL,
  `PID` int(11) NOT NULL,
  `UID` int(11) NOT NULL,
  `STATUS` int(11) NOT NULL default '0',
  `CFID` int(10) unsigned default NULL,
  `PRIORITY` varchar(45) default NULL,
  KEY `IID` (`IID`),
  KEY `PID` (`PID`),
  KEY `UID` (`UID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `incident_details`
  ADD FOREIGN KEY (`IID`) REFERENCES `incident_master` (`IID`),
  ADD FOREIGN KEY (`PID`) REFERENCES `profile_master` (`PID`),
  ADD FOREIGN KEY (`UID`) REFERENCES `user_master` (`UID`);

********

It's keep on  showing me that alter table doesn't work, with the error code, 1005. but the incident_details table already have the key of IID, PID and UID alread. What else do i lack of?
heman7719Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yuval_ShohatCommented:
well, check this out,

http://bugs.mysql.com/bug.php?id=3491

though its in the bugs department, I dont know if a bug is the true proper word, its something bump, nop fixed yet.
Try adding the index, I think you will have it working in no time.

Yuval.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
heman7719Author Commented:
Hi, i haves sovled the problems. It's the INNODB and MYISAM table type different. I am trying to refer to a table of INNODB from MYISAM, after i changed both to MYISAM, it;s working fine. BUt then Yuval, do you think you can explain to me after i have issues show innodb status\G, how can i digest the information. What are those information making sense for me?

mysql> show innodb status\G;
*************************** 1. row ***************************
Status:
=====================================
060418 10:08:53 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 42, signal count 42
Mutex spin waits 11, rounds 160, OS waits 3
RW-shared spins 76, OS waits 38; RW-excl spins 1, OS waits 1
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060418 10:04:27 Error in foreign key constraint of table netradb/#sql-dc_f:
FOREIGN KEY (`IID`) REFERENCES `incident_master` (`IID`),
  ADD FOREIGN KEY (`PID`) REFERENCES `profile_master` (`PID`),
  ADD FOREIGN KEY (`UID`) REFERENCES `user_master` (`UID`):
Cannot resolve table name close to:
 (`IID`),
  ADD FOREIGN KEY (`PID`) REFERENCES `profile_master` (`PID`),
  ADD FOREIGN KEY (`UID`) REFERENCES `user_master` (`UID`)
------------
TRANSACTIONS
------------
Trx id counter 0 43723
Purge done for trx's n:o < 0 43721 undo n:o < 0 0
History list length 2
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 3680
MySQL thread id 21, query id 1293 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 43710, not started, OS thread id 3232
MySQL thread id 15, query id 1265 localhost 127.0.0.1 root
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
138 OS file reads, 1384 OS file writes, 575 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 315527, used cells 24, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 34336194
Log flushed up to   0 34336194
Last checkpoint at  0 34336194
0 pending log writes, 0 pending chkp writes
462 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 89146642; in additional pool allocated 276736
Buffer pool size   4864
Free buffers       4735
Database pages     128
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 128, created 0, written 877
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 324, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)

ERROR:
No query specified

mysql>
0
Yuval_ShohatCommented:
read about it here, this should give you some clues
http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html
but while are you looking at the innodb, if you have changed to myisam?
by the way, the errors are errors due to the failure in setting the foreign keys. pay attention to the header of each section: while most of the output is according to the parameters of the last 45 seconds or the time of the show command execution, the error in the foreign key section is the LATEST error (whice happend about 4 minutes earlier, according to the time in the headers).

hope this helps.
Yuval.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.