Re: Forward declaration of table - Mailing list pgsql-general
| From | Igor Neyman |
|---|---|
| Subject | Re: Forward declaration of table |
| Date | |
| Msg-id | MWHPR07MB2877DFFDA4014BC410629D41DAEB0@MWHPR07MB2877.namprd07.prod.outlook.com Whole thread Raw |
| In response to | Forward declaration of table (Alexander Farber <alexander.farber@gmail.com>) |
| Responses |
Re: Forward declaration of table
(Alexander Farber <alexander.farber@gmail.com>)
|
| List | pgsql-general |
Regards,
Igor
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Tuesday, August 23, 2016 1:11 PM
To: pgsql-general <pgsql-general@postgresql.org>
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:
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
pgsql-general by date: