Why evolution in Play Framework doesn't work?

Question

I'm using Play 2.3 and trying to generate relational database by evolution for PostgreSQL 9.4.

I have following statements in my conf/evolutions/default/1.sql script:

ALTER TABLE ONLY round
ADD CONSTRAINT round_event_id_fkey FOREIGN KEY (event_id) REFERENCES event(id);

ALTER TABLE ONLY round
ADD CONSTRAINT round_event_id UNIQUE (event_id);

Following is my event table description:

Table "public.event"
            Column             |            Type             |                     Modifiers
-------------------------------+-----------------------------+----------------------------------------------------  id                            | integer                     | not null default nextval('event_id_seq'::regclass)  related_event_hash          | character varying(45)       |  start_time                    | timestamp without time zone |  end_time                      | timestamp without time zone |  name                          | character varying(45)       |  status                        | character varying(45)       | not null  owner_id                      | bigint                      | not null  venue_id                     | bigint                      |  participation_hash            | character varying(45)       |  number_of_participants        | integer |  number_of_backup_participants | integer                     |  created                       | timestamp without time zone | not null updated                       | timestamp without time zone | not null Indexes:
    "event_pkey" PRIMARY KEY, btree (id)
    "index_event_name" btree (name)
    "index_event_status" btree (status)
    "index_start_time" btree (start_time) Foreign-key constraints:
    "event_owner_id_fkey" FOREIGN KEY (owner_id) REFERENCES person(id)
    "event_venue_id_fkey" FOREIGN KEY (venue_id) REFERENCES venue(id) Referenced by:
    TABLE "anonymous_person" CONSTRAINT "anonymous_person_event_id_fkey" FOREIGN KEY (event_id) REFERENCES event(id)
    TABLE "mix_game" CONSTRAINT "mix_game_event_id_fkey" FOREIGN KEY (event_id) REFERENCES event(id)
    TABLE "participant" CONSTRAINT "participant_event_id_fkey" FOREIGN KEY (event_id) REFERENCES event(id)

When I start the application in a browser I get this error:

Database 'default' is in an inconsistent state!

While trying to run this SQL script, we got the following error:

ERROR: there is no unique constraint matching given keys for referenced table "round" [ERROR:0, SQLSTATE:42830] 

What could be wrong? How to fix this error and add foreign key constraints?

Note that it generates database round as follows without foreign key constraints.

                                 Table "public.round"


    Column      |         Type          |                     Modifiers
------------------+-----------------------+----------------------------------------------------
 id               | integer               | not null default nextval('round_id_seq'::regclass)
 round_no         | integer               | not null
 event_id         | bigint                | not null
 state            | character varying(20) | not null
 team_composition | character(12)         | not null
 result           | character varying(20) |
 description      | character varying(45) |
 play_time        | integer               | not null
 shift_time       | integer               |
 change_time      | integer               |
Indexes:
    "round_pkey" PRIMARY KEY, btree (id)
    "round_event_id" UNIQUE CONSTRAINT, btree (event_id)

Answer 1

Take a look at the documentation.

As you see you have to delimit the both Ups and Downs section by using comments in your SQL script.

Also, do not edit the 1.sql file because it is updated by the evolution mechanism. Start your own evolutions at 2.sql.