Re: Forward declaration of table

[Igor Neyman <ineyman@xxxxxxxxxxxxxx>]

 

 

 

Regards,

Igor

 

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alexander Farber
Sent: Tuesday, August 23, 2016 1:11 PM
To: pgsql-general <pgsql-general@xxxxxxxxxxxxxx>
Subject: [GENERAL] Forward declaration of table

 

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?

Here are all tables of my game for more context: 

https://gist.github.com/afarber/c40b9fc5447335db7d24

 

Thank you

Alex

 

Alex,

I think, you’ve got this reference “backwards”.

Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on delete cascade).

So, you don’t need mid1, mid2 columns in WORD_GAMES table.

What you need is this column in WORD_MOVES table:

 

gid integer REFERENCES WORD_GAMES ON DELETE CASCADE

 

Am right/wrong?

 

Regards,

Igor