Question 212078 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Add foreign key to the existing table diesel Rust

I have two existing tables: user_table and post. I need to make the relation between these two tables i.e. add user_id to the post table.
I have the following migration

ALTER TABLE post
ADD CONSTRAINT fk_post_user_id
FOREIGN KEY (user_id)
REFERENCES user_table (id);

Schema

diesel::table! {
    post (id) {
        id -> Int4,
        title -> Nullable<Varchar>,
        content -> Nullable<Varchar>,
        date -> Timestamp,
        user_id -> Int4,
    }
}

diesel::table! {
    user_table (id) {
        id -> Int4,
        username -> Varchar,
        email -> Varchar,
        password -> Varchar,
    }
}

diesel::joinable!(post -> user_table (user_id));

diesel::allow_tables_to_appear_in_same_query!(
    post,
    user_table,
);

These two lines I added by myself

diesel::joinable!(post -> user_table (user_id));
user_id -> Int4

My post and user models

#[derive(Queryable, Identifiable, Associations)]
#[belongs_to(User)]
#[table_name="post"]
pub struct Post {
    pub id: i32,
    pub title: String,
    pub content: String,
    pub user_id: i32
}

#[derive(Queryable, Identifiable, Clone)]
#[table_name="user_table"]
pub struct User {
    pub id: i32,
    pub username: String,
    pub email: String,
    pub password: String,
}

SQL queries for creating tables

CREATE TABLE user_table (
    id SERIAL PRIMARY KEY,
    username VARCHAR NOT NULL UNIQUE,
    email VARCHAR NOT NULL UNIQUE,
    password VARCHAR NOT NULL
);
CREATE TABLE post (
    id SERIAL PRIMARY KEY,
    title VARCHAR,
    content VARCHAR,
    date timestamp NOT NULL DEFAULT NOW()
);

I am getting a migration error

column "user_id" referenced in foreign key constraint does not exist

I am new to the backend development and I am stuck with this problem.
Would be grateful for any support

Tags:

2

Answers


  1. 0

    The issue in with your migration:

    ALTER TABLE post
ADD CONSTRAINT fk_post_user_id
FOREIGN KEY (user_id)
REFERENCES user_table (id);

    This basically says create a foreign key constraint on the existing column user_id that references the other table, but the user_id column does not exist. You can fix that by adding a ALTER TABLE posts ADD COLUMN user_id INTEGER; to your migration before creating the foreign key.

    Login or Signup to reply.
  2. 0

    I would suggest directly adding user_id

    To posts table:

    
CREATE TABLE user_table (
    id SERIAL PRIMARY KEY,
    username VARCHAR NOT NULL UNIQUE,
    email VARCHAR NOT NULL UNIQUE,
    password VARCHAR NOT NULL
);
CREATE TABLE post (
    id SERIAL PRIMARY KEY,
    title VARCHAR,
    content VARCHAR,
    user_id INT,
    date timestamp NOT NULL DEFAULT NOW()
);

    The FK constraint will work if this schema is set.

    Remember to drop migrations ( drop the tables) and re-run the migrations.

    Be careful if you have some data in your tables. If you drop a table data will be lost.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search