From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Forward declaration of table |
Date: | 2016-08-23 17:24:12 |
Message-ID: | 7b1d8e20-7c12-c1c6-2fc8-477c42afd652@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 08/23/2016 10:10 AM, Alexander Farber wrote:
> Good evening,
>
> with PostgreSQL 9.5.3 I am using the following table to store 2-player
> games:
>
> DROP TABLE IF EXISTS words_games;
> CREATE TABLE words_games (
> gid SERIAL PRIMARY KEY,
> created timestamptz NOT NULL,
> player1 integer REFERENCES words_users(uid) ON DELETE
> CASCADE NOT NULL,
> player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
> played1 timestamptz,
> played2 timestamptz,
> -- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
> -- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
> score1 integer NOT NULL CHECK(score1 >= 0),
> score2 integer NOT NULL CHECK(score2 >= 0),
> hand1 varchar[7] NOT NULL,
> hand2 varchar[7] NOT NULL,
> pile varchar[116] NOT NULL,
> letters varchar[15][15] NOT NULL,
> values integer[15][15] NOT NULL,
> bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
> );
>
>
> This has worked well for me (when a user connects to the game server, I
> send her all games she is taking part in), but then I have decided to
> add another table to act as a "logging journal" for player moves:
>
> DROP TABLE IF EXISTS words_moves;
>
> DROP TYPE IF EXISTS words_action;
>
> CREATE TABLE words_moves (
> mid SERIAL PRIMARY KEY,
> action words_action NOT NULL,
> gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
> uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
> played timestamptz NOT NULL,
> tiles jsonb,
> score integer CHECK(score > 0)
> );
>
> Also, in the former table words_games I wanted to add references to the
> latest moves performed by players:
>
> -- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
> -- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
>
> The intention is: whenever a player connects to the server, sent her all
> active games and status updates on the recent opponent moves.
>
> However the 2 added columns do not work:
>
> ERROR: relation "words_moves" does not exist
> ERROR: relation "words_games" does not exist
> ERROR: relation "words_moves" does not exist
>
>
> So my question is if I can somehow "forward declare" the words_moves table?
Off the top of my head:
Change this:
--mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
--mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
to
mid1 integer
mid2 integer
and then after
CREATE TABLE words_moves ...
use ALTER TABLE ADD table_constraint :
https://www.postgresql.org/docs/9.5/static/sql-altertable.html
to add the FK references to word_games.
>
> Here are all tables of my game for more context:
> https://gist.github.com/afarber/c40b9fc5447335db7d24
>
> Thank you
> Alex
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2016-08-23 17:27:05 | Re: Forward declaration of table |
Previous Message | Alexander Farber | 2016-08-23 17:10:52 | Forward declaration of table |