mysql5 shema - foreign key error?

[Terence]

Hi! When i try to create my mysql 5 tables i get an error for table 'cds':
Can't create table '.\cds.frm' (errno: 150)
Does someone has an idea what my fault is?
Tx for help..

CREATE TABLE `albums` (
`albumid` bigint(20) unsigned NOT NULL,
`album` varchar(60) NOT NULL,
`album_strip_l0` varchar(60) default NULL,
PRIMARY KEY (`albumid`),
UNIQUE KEY `albums_album_index` (`album`),
KEY `albums_albumsl0_index` (`album_strip_l0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `artist2album` (
`artist2albumid` bigint(20) unsigned NOT NULL,
`artistid` bigint(20) unsigned NOT NULL,
`albumid` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`artist2albumid`),
UNIQUE KEY `a2a_album_artistid_index` (`albumid`,`artistid`),
KEY `a2a_albumid_index` (`albumid`),
KEY `a2a_artistid_index` (`artistid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `artists` (
`artistid` bigint(20) unsigned NOT NULL,
`artist` varchar(60) NOT NULL,
`artist_strip_l0` varchar(60) default NULL,
PRIMARY KEY (`artistid`),
UNIQUE KEY `artists_artist_index` (`artist`),
KEY `artists_artistsl0_index` (`artist_strip_l0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `genres` (
`genreid` int(5) unsigned NOT NULL,
`genre` varchar(20) NOT NULL,
PRIMARY KEY (`genreid`),
UNIQUE KEY `genres_genre_index` (`genre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `submitters` (
`submitterid` int(5) unsigned NOT NULL,
`submitter` varchar(30) NOT NULL,
`submitcounter` int(5) unsigned,
PRIMARY KEY (`submitterid`),
UNIQUE KEY `submitters_submitter_index` (`submitter`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cds` (
`cdid` bigint(20) unsigned NOT NULL,
`artist2albumid` bigint(20) unsigned NOT NULL,
`ayear` smallint(2) unsigned default NULL,
`genreid` int(5) unsigned default NULL,
`discid` varchar(8) NOT NULL,
`playlengthsecs` int(5) unsigned default NULL,
`submitterid` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`cdid`),
UNIQUE KEY `keyindex` (`artist2albumid`,`discid`,`genreid`,`ayear`),
KEY `cds_year_index` (`ayear`),
KEY `cds_genre_index` (`genreid`),
KEY `cds_discid_index` (`discid`),
CONSTRAINT `FK_cds_aTOa` FOREIGN KEY (`artist2albumid`) REFERENCES `artist2album` (`artist2albumid`),
CONSTRAINT `FK_cds_genre` FOREIGN KEY (`genreid`) REFERENCES `genres` (`genreid`),
CONSTRAINT `FK_cds_submitter` FOREIGN KEY (`submitterid`) REFERENCES `submitters` (`submitterid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `songs` (
`songid` bigint(20) unsigned NOT NULL,
`song` varchar(60) NOT NULL,
`song_strip_l0` varchar(60) default NULL,
PRIMARY KEY (`songid`),
UNIQUE KEY `songs_song_index` (`song`),
KEY `songs_songsl0_index` (`song_strip_l0`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cdtracks` (
`songid` bigint(20) unsigned NOT NULL,
`cdid` bigint(20) unsigned NOT NULL,
`track` smallint(2) NOT NULL,
`offset` smallint(6) NOT NULL,
`playlength` smallint(5) NOT NULL,
PRIMARY KEY (`songid`,`cdid`,`track`),
KEY `cdtracks_cdid_index` (`cdid`),
KEY `cdtracks_songid_index` (`songid`),
CONSTRAINT `FK_cdtracks_cdid` FOREIGN KEY (`cdid`) REFERENCES `cds` (`cdid`),
CONSTRAINT `FK_cdtracks_songid` FOREIGN KEY (`songid`) REFERENCES `songs` (`songid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `keyindex` (
`tablename` varchar(20) NOT NULL,
`nextindex` bigint(20) unsigned NOT NULL default '0',
PRIMARY KEY (`tablename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[zippo]

Hmm.. could be a file write permission error?

BTW:
What the hell is in the CDS table definition (`discid` varchar(Cool NOT NULL, ..) ?

[Terence]

hmm very strange - i restarted - still same problems but this time i used orginal mysql5 query tool and cdtracks table now produces same error - i don't understand this. :/

Ps. the cool is made from orion out of

Code: Select all

8)

- originally

Code: Select all

(`discid` varchar(8) NOT NULL, ..)

[Terence]

ahh i found the bug, i'm so silly!
Its a incompatible data type length for foreign key submitterid, in table submitters and table cds.. now it works.

Btw. it would be impossible that it worked for you then ?

Something else, i know quite noob but- what does the number for in bracktes behind the integer data typ define .. the number of bytes or the number of digits that is allowed to have max?
eg. `cdid` bigint(20) unsigned NOT NULL,
I expect about 8.000.000 entries, so i am not sure if i have to take as big values in ddt i did!

[zippo]

Number of digits