asked on

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?

ASKER CERTIFIED SOLUTION

Yuval_Shohat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

heman7719

ASKER

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>

Yuval_Shohat

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.