I have encountered a problem with my database. the situation is as follows: you have 2 playersessions and each player session has the same turn number. meaning that in turn 1 player 1 played something and player 2 played something else but its classified as turn 1. the problem is that I cant make turn_no unique in my database, for the reason I just mentioned. the error I'm getting is because I don't have a unique constraint for table 'int_turn'. but if i make it unique, i violate the unique constraint when saving. I thought that I was allowed to have non-unique foreign keys.

CREATE TABLE IF NOT EXISTS int_turn(
    playersession_id INT REFERENCES int_playersession(playersession_id) ON DELETE CASCADE,
    turn_no INT NOT NULL ,
    time_spent INT NOT NULL,
    time_played TIMESTAMP NOT NULL,
    CONSTRAINT turn_pkey PRIMARY KEY (playersession_id,turn_no)
);

CREATE TABLE IF NOT EXISTS int_move(
    playersession_id INT REFERENCES int_playersession(playersession_id) ON DELETE CASCADE,
    turn_no INT REFERENCES int_turn(turn_no),
    move_no INT NOT NULL,
    x INT,
    y INT,
    tile_id INT REFERENCES int_tile,
    CONSTRAINT move_pk PRIMARY KEY (playersession_id,move_no, turn_no)
);

Also I think if this problem can be fixed, that I'm gonna encounter the same problem but with move_no, every turn has move_no which is even a bigger problem.

Here is my ERD but with the turn_no as unique just for you to see.

CodePudding user response：

You don't - it clearly doesn't make sense to try to reference a non-unique value.

However, your instincts are right, you just need to reference the primary key. So, you change your second table to be something like:

CREATE TABLE IF NOT EXISTS int_move(
    playersession_id INT NOT NULL,
    turn_no INT REFERENCES NOT NULL,
    move_no INT NOT NULL,
    x INT,
    y INT,
    tile_id INT REFERENCES int_tile,
    CONSTRAINT valid_player_turn FOREIGN KEY (playersession_id, turn_no) REFERENCES int_turn,
    CONSTRAINT move_pk PRIMARY KEY (playersession_id,move_no, turn_no)
);