SQL语法错误？

Question

有谁知道下面的 SQL 查询有什么问题吗？

CREATE TABLE IF NOT EXISTS PrWorlds (                     # World table
 worldid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 worldname VARCHAR(32) NOT NULL UNIQUE                    # Name of world
);

CREATE TABLE IF NOT EXISTS PrEntries (                    # User/Group table
 entryid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(32) NOT NULL,                               # Name of user/group
 worldid INTEGER NOT NULL,                                # ID of the world the     user/group belongs to
 type TINYINT NOT NULL,                                   # Type denotes the entry type.     0 for a user, 1 for a group
 CONSTRAINT NameWorld UNIQUE (name, worldid, type),
 ENTRYINDEX
 FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrPermissions (                             # Table of     permission nodes
 permid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 permstring VARCHAR(64) NOT NULL,                                      # Permission node
 entryid INTEGER NOT NULL,                                             # Entry whom this     node belongs to
 CONSTRAINT PrEntryPerm UNIQUE (entryid, permstring),
 FOREIGN KEY(entryid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrInheritance (                  # Table of parent-child     relationships
 uinheritid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 childid INTEGER NOT NULL,                                  # Child entry
 parentid INTEGER NOT NULL,                                 # Parent entry
 parentorder INTEGER NOT NULL,                              # Denotes order of     inheritance. 
                                                            # Groups override other     groups' permissions/data 
                                                            # below them in the results
 CONSTRAINT PrParent UNIQUE (childid, parentid),
 CONSTRAINT PrOrderedInheritance UNIQUE (childid, parentorder),
 CONSTRAINT PrNoSelfInherit CHECK (childid <> parentid),
 FOREIGN KEY(childid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(parentid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrWorldBase (      # Table of the default groups in that world
 worldid INTEGER NOT NULL,
 defaultid INTEGER,                           # Default group
 FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(defaultid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrData (
 dataid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 entryid INTEGER NOT NULL ,                  # ID entry whom this data node belongs to
 path VARCHAR(64) NOT NULL,                  # Path to data node (e.g. "prefix", "build")
 data VARCHAR(64) NOT NULL,                  # Data node in string form (o.toString())
 CONSTRAINT PrDataUnique UNIQUE (entryid, path),
 FOREIGN KEY(entryid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrTracks (
 trackid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 trackname VARCHAR(64) NOT NULL UNIQUE,                      # Track name
 worldid INTEGER NOT NULL,                                   # ID of world track belongs             to
     CONSTRAINT TracksUnique UNIQUE (trackid, worldid),
 FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrTrackGroups (
 trackgroupid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 trackid INTEGER NOT NULL,                                          # ID of track
 gid INTEGER NOT NULL,                                              # ID of group in     track
 groupOrder INTEGER NOT NULL,                                       # Denotes position of     the group in the track
 CONSTRAINT TrackGroupsUnique UNIQUE (trackid, gid),
 FOREIGN KEY(trackid) REFERENCES PrTracks(trackid) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(gid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

在最新版本的 phpMyAdmin 中运行时会抛出此错误：

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CA' at line 8 

通过 yum 运行最新的可用 MySQL 服务器，PHP 版本是 5.3，也通过 yum 添加了 php-mysql。
所有这些都在 CentOS 5.5 64 位上运行。

Answer 1

删除 ENTRYINDEX 并将 AUTOINCRMENT 更改为 AUTO_INCRMENT。

正确的代码是这样的：

CREATE TABLE IF NOT EXISTS PrWorlds (                     # World table
 worldid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 worldname VARCHAR(32) NOT NULL UNIQUE                    # Name of world
);

CREATE TABLE IF NOT EXISTS PrEntries (                    # User/Group table
 entryid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 name VARCHAR(32) NOT NULL,                               # Name of user/group
 worldid INTEGER NOT NULL,                                # ID of the world the     user/group belongs to
 type TINYINT NOT NULL,                                   # Type denotes the entry type.     0 for a user, 1 for a group
 CONSTRAINT NameWorld UNIQUE (name, worldid, type),
 FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrPermissions (                             # Table of     permission nodes
 permid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 permstring VARCHAR(64) NOT NULL,                                      # Permission node
 entryid INTEGER NOT NULL,                                             # Entry whom this     node belongs to
 CONSTRAINT PrEntryPerm UNIQUE (entryid, permstring),
 FOREIGN KEY(entryid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE IF NOT EXISTS PrInheritance (                  # Table of parent-child     relationships
 uinheritid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 childid INTEGER NOT NULL,                                  # Child entry
 parentid INTEGER NOT NULL,                                 # Parent entry
 parentorder INTEGER NOT NULL,                              # Denotes order of     inheritance. 
                                                            # Groups override other     groups' permissions/data 
                                                            # below them in the results
 CONSTRAINT PrParent UNIQUE (childid, parentid),
 CONSTRAINT PrOrderedInheritance UNIQUE (childid, parentorder),
 CONSTRAINT PrNoSelfInherit CHECK (childid <> parentid),
 FOREIGN KEY(childid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(parentid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrWorldBase (      # Table of the default groups in that world
 worldid INTEGER NOT NULL,
 defaultid INTEGER,                           # Default group
 FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(defaultid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrData (
 dataid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 entryid INTEGER NOT NULL ,                  # ID entry whom this data node belongs to
 path VARCHAR(64) NOT NULL,                  # Path to data node (e.g. "prefix", "build")
 data VARCHAR(64) NOT NULL,                  # Data node in string form (o.toString())
 CONSTRAINT PrDataUnique UNIQUE (entryid, path),
 FOREIGN KEY(entryid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrTracks (
 trackid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 trackname VARCHAR(64) NOT NULL UNIQUE,                      # Track name
 worldid INTEGER NOT NULL,                                   # ID of world track belongs             to
     CONSTRAINT TracksUnique UNIQUE (trackid, worldid),
 FOREIGN KEY(worldid) REFERENCES PrWorlds(worldid) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS PrTrackGroups (
 trackgroupid INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
 trackid INTEGER NOT NULL,                                          # ID of track
 gid INTEGER NOT NULL,                                              # ID of group in     track
 groupOrder INTEGER NOT NULL,                                       # Denotes position of     the group in the track
 CONSTRAINT TrackGroupsUnique UNIQUE (trackid, gid),
 FOREIGN KEY(trackid) REFERENCES PrTracks(trackid) ON DELETE CASCADE ON UPDATE CASCADE,
 FOREIGN KEY(gid) REFERENCES PrEntries(entryid) ON DELETE CASCADE ON UPDATE CASCADE
);

Answer 2

您的数据库中的默认表类型是否是 MyISAM 或其他？ INNODB 支持 FOREIGN KEY，但 SQL 未指定要使用的表类型。