database - 表 postgreSQL 中的索引

Question

我正在从 mySQL 转移到 postgreSQL，想知道如何在 postgreSQL 的表中添加索引

CREATE TABLE IF NOT EXISTS  game_history
(
 id SERIAL,
 PRIMARY KEY(id),
 INDEX fk_game_history_user_idx (game_id ASC) ,
 CONSTRAINT fk_game_history_user
 FOREIGN KEY (game_id )
 REFERENCES mydb.game (id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION
) 

来自

   INDEX fk_game_history_user_idx (game_id ASC) ,
 CONSTRAINT fk_game_history_user
 FOREIGN KEY (game_id )
 REFERENCES mydb.game (id)
 ON DELETE NO ACTION
 ON UPDATE NO ACTION
) 

我不确定。

Answer 1

您需要一个单独的create index 语句并且您需要定义一个game_id 列:

CREATE TABLE IF NOT EXISTS  game_history
(
 id      SERIAL,
 game_id integer not null, -- you need to define the column
                           -- otherwise you can't have a foreign key
 PRIMARY KEY(id),
 CONSTRAINT fk_game_history_user
   FOREIGN KEY (game_id)
   REFERENCES game (id)
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
);

CREATE INDEX fk_game_history_user_idx
  ON game_history (game_id ASC);

关于 create table 语句的更多细节在手册中:http://www.postgresql.org/docs/current/static/sql-createtable.html

以及有关create index 语句的更多详细信息:http://www.postgresql.org/docs/current/static/sql-createindex.html