On Tue, 2002-06-25 at 10:54, Roger Baklund wrote:
> Like Heikki said: your problem is probably that you don't have indexes on
> the foreign keys.
Setting an INDEX on each of those columns does fix the problem. It then
does allow me to remove the NOT NULL from the tracks table as I
originally had it. In fact now I see this in the on-line docs: "Both
tables have to be InnoDB type and there must be an index where the
foreign key and the referenced key are listed as the first columns.
InnoDB does not auto-create indexes on foreign keys or referenced keys:
you have to create them explicitly." However, this is not in the HTML
docs which come with the mysql.com RPMs.
>From a usability perspective, since other databases don't seem to be so
picky about this sort of thing, InnoDB ought to (IMHO):
a) Act on REFERENCES clauses (i.e. column type REFERENCES ref-table)
instead of ignoring them.
b) Auto-create indexes on the foreign keys (but not the referenced
keys). It does this for UNIQUE columns already.
c) The referenced key column name ought to be optional, defaulting to
the primary key of the referenced table. See example below.
All three of the above seem fairly common, applying to at least
PostgreSQL and Oracle (from my reading of the docs, I am not an Oracle
expert).
For comparision, here is the equivalent schema in PostgreSQL
(particularly the tracks table):
CREATE TABLE artists (
artist_id SERIAL
NOT NULL PRIMARY KEY,
artist VARCHAR (32) NOT NULL UNIQUE
) ;
CREATE TABLE albums (
album_id SERIAL
NOT NULL PRIMARY KEY,
album VARCHAR (32) NOT NULL,
year DATE
) ;
CREATE INDEX by_album ON albums(album);
CREATE TABLE genres (
genre_id SMALLINT NOT NULL PRIMARY KEY,
genre VARCHAR (32) NOT NULL
) ;
CREATE TABLE tracks (
track_id SERIAL
NOT NULL PRIMARY KEY,
artist_id INTEGER NOT NULL
REFERENCES artists,
album_id INTEGER NOT NULL
REFERENCES albums,
track SMALLINT,
title VARCHAR (32) NOT NULL,
genre_id SMALLINT REFERENCES genres,
comment VARCHAR (32) NOT NULL DEFAULT '',
filename VARCHAR (255) NOT NULL
) ;
CREATE UNIQUE INDEX album_tracks ON tracks(album_id, track);
CREATE INDEX by_title ON tracks(title);
For comparison purposes, here is the equivalent MySQL:
CREATE TABLE artists (
artist_id INTEGER UNSIGNED AUTO_INCREMENT
NOT NULL PRIMARY KEY,
artist VARCHAR (32) NOT NULL UNIQUE
) type=InnoDB;
CREATE TABLE albums (
album_id INTEGER UNSIGNED AUTO_INCREMENT
NOT NULL PRIMARY KEY,
album VARCHAR (32) NOT NULL,
year YEAR
) type=InnoDB;
CREATE INDEX by_album ON albums(album);
CREATE TABLE genres (
genre_id SMALLINT NOT NULL PRIMARY KEY,
genre VARCHAR (32) NOT NULL
) type=InnoDB;
CREATE TABLE tracks (
track_id INTEGER UNSIGNED AUTO_INCREMENT
NOT NULL PRIMARY KEY,
artist_id INTEGER UNSIGNED NOT NULL,
album_id INTEGER UNSIGNED NOT NULL,
track SMALLINT,
title VARCHAR (32) NOT NULL,
genre_id SMALLINT,
comment VARCHAR (32) NOT NULL DEFAULT '',
filename VARCHAR (255) NOT NULL,
INDEX (artist_id),
INDEX (album_id),
INDEX (genre_id),
FOREIGN KEY (artist_id) REFERENCES artists(artist_id),
FOREIGN KEY (album_id) REFERENCES albums(album_id),
FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
) type=InnoDB;
CREATE UNIQUE INDEX album_tracks ON tracks(album_id, track);
CREATE INDEX by_title ON tracks(title);
--
Andy Dustman PGP: 0x930B8AB6
@ .net http://dustman.net/andy
"Cogito, ergo sum." -- Rene Descartes
"I yam what I yam and that's all what I yam." -- Popeye
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
