CODEWITHSUNDEEP
databasepostgresql
rodolfo navalon
rodolfo navalon

Reputation: 193

Index inside a table postgreSQL

I am transferring from mySQL to postgreSQL and wondering how can I add index inside the table of 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
)

FROM 

   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
)

I am not sure on this.

Upvotes: 0

Views: 2781

Answers (1)

user330315
user330315

Reputation:

You need a separate create index statement and you need to define a game_id column:

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);

More details about the create table statement are in the manual: http://www.postgresql.org/docs/current/static/sql-createtable.html

And more details about the create index statement: http://www.postgresql.org/docs/current/static/sql-createindex.html

Upvotes: 3

Related Questions