Index inside a table postgreSQL

Question

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.

user330315 · Accepted Answer · 2013-04-15 21:19:40Z

3

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

answered Apr 15, 2013 at 21:19

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

IMSoP Over a year ago

Since the Primary Key and Foreign Key are each on a single column, you can put them further inline as id SERIAL PRIMARY KEY and game_id INT NOT NULL CONSTRAINT fk_game_history_user REFERENCES game (id), respectively.

Collectives™ on Stack Overflow

Index inside a table postgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related