添加外键到现有表diesel Rust。

huangapple
go评论100阅读模式
英文:

Add foreign key to the existing table diesel Rust

问题

我有两张现有的表格：user_table和post。我需要建立这两张表格之间的关系，即将user_id添加到post表格中。
我有以下的迁移代码：

  1. ALTER TABLE post
  2. ADD CONSTRAINT fk_post_user_id
  3. FOREIGN KEY (user_id)
  4. REFERENCES user_table (id);

数据库结构：

  1. diesel::table! {
  2.     post (id) {
  3.         id -> Int4,
  4.         title -> Nullable<Varchar>,
  5.         content -> Nullable<Varchar>,
  6.         date -> Timestamp,
  7.         user_id -> Int4,
  8.     }
  9. }
  11. diesel::table! {
  12.     user_table (id) {
  13.         id -> Int4,
  14.         username -> Varchar,
  15.         email -> Varchar,
  16.         password -> Varchar,
  17.     }
  18. }
  20. diesel::joinable!(post -> user_table (user_id));
  22. diesel::allow_tables_to_appear_in_same_query!(
  23.     post,
  24.     user_table,
  25. );

我自己添加的这两行代码：

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

我的post和user模型：

  1. #[derive(Queryable, Identifiable, Associations)]
  2. #[belongs_to(User)]
  3. #[table_name="post"]
  4. pub struct Post {
  5.     pub id: i32,
  6.     pub title: String,
  7.     pub content: String,
  8.     pub user_id: i32
  9. }
  11. #[derive(Queryable, Identifiable, Clone)]
  12. #[table_name="user_table"]
  13. pub struct User {
  14.     pub id: i32,
  15.     pub username: String,
  16.     pub email: String,
  17.     pub password: String,
  18. }

创建表格的SQL查询：

  1. CREATE TABLE user_table (
  2.     id SERIAL PRIMARY KEY,
  3.     username VARCHAR NOT NULL UNIQUE,
  4.     email VARCHAR NOT NULL UNIQUE,
  5.     password VARCHAR NOT NULL
  6. );
  7. CREATE TABLE post (
  8.     id SERIAL PRIMARY KEY,
  9.     title VARCHAR,
  10.     content VARCHAR,
  11.     date timestamp NOT NULL DEFAULT NOW()
  12. );

我遇到了一个迁移错误：

列"user_id"在外键约束中引用，但不存在

我是后端开发的新手，卡在了这个问题上。非常感谢任何支持。

英文:

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

  1. ALTER TABLE post
  2. ADD CONSTRAINT fk_post_user_id
  3. FOREIGN KEY (user_id)
  4. REFERENCES user_table (id);

Schema

  1. diesel::table! {
  2.     post (id) {
  3.         id -&gt; Int4,
  4.         title -&gt; Nullable&lt;Varchar&gt;,
  5.         content -&gt; Nullable&lt;Varchar&gt;,
  6.         date -&gt; Timestamp,
  7.         user_id -&gt; Int4,
  8.     }
  9. }
  11. diesel::table! {
  12.     user_table (id) {
  13.         id -&gt; Int4,
  14.         username -&gt; Varchar,
  15.         email -&gt; Varchar,
  16.         password -&gt; Varchar,
  17.     }
  18. }
  20. diesel::joinable!(post -&gt; user_table (user_id));
  22. diesel::allow_tables_to_appear_in_same_query!(
  23.     post,
  24.     user_table,
  25. );

These two lines I added by myself

  1. diesel::joinable!(post -&gt; user_table (user_id));
  2. user_id -&gt; Int4

My post and user models

  1. #[derive(Queryable, Identifiable, Associations)]
  2. #[belongs_to(User)]
  3. #[table_name=&quot;post&quot;]
  4. pub struct Post {
  5.     pub id: i32,
  6.     pub title: String,
  7.     pub content: String,
  8.     pub user_id: i32
  9. }
  11. #[derive(Queryable, Identifiable, Clone)]
  12. #[table_name=&quot;user_table&quot;]
  13. pub struct User {
  14.     pub id: i32,
  15.     pub username: String,
  16.     pub email: String,
  17.     pub password: String,
  18. }

SQL queries for creating tables

  1. CREATE TABLE user_table (
  2.     id SERIAL PRIMARY KEY,
  3.     username VARCHAR NOT NULL UNIQUE,
  4.     email VARCHAR NOT NULL UNIQUE,
  5.     password VARCHAR NOT NULL
  6. );
  7. CREATE TABLE post (
  8.     id SERIAL PRIMARY KEY,
  9.     title VARCHAR,
  10.     content VARCHAR,
  11.     date timestamp NOT NULL DEFAULT NOW()
  12. );

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

答案1

得分: 1

关于您的迁移问题：

  1. ALTER TABLE post
  2. ADD CONSTRAINT fk_post_user_id
  3. FOREIGN KEY (user_id)
  4. REFERENCES user_table (id);

这基本上是在现有的列 user_id 上创建一个外键约束，该约束引用了另一个表，但是 user_id 列不存在。您可以通过在迁移之前添加 ALTER TABLE posts ADD COLUMN user_id INTEGER; 来解决这个问题，以创建 user_id 列。

英文:

The issue in with your migration:

  1. ALTER TABLE post
  2. ADD CONSTRAINT fk_post_user_id
  3. FOREIGN KEY (user_id)
  4. 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.

答案2

得分: 0

将user_id直接添加到posts表中：

  2. CREATE TABLE user_table (
  3.     id SERIAL PRIMARY KEY,
  4.     username VARCHAR NOT NULL UNIQUE,
  5.     email VARCHAR NOT NULL UNIQUE,
  6.     password VARCHAR NOT NULL
  7. );
  8. CREATE TABLE post (
  9.     id SERIAL PRIMARY KEY,
  10.     title VARCHAR,
  11.     content VARCHAR,
  12.     user_id INT,
  13.     date timestamp NOT NULL DEFAULT NOW()
  14. );

如果设置了此模式，外键约束将起作用。

请记得删除迁移（删除表）并重新运行迁移。

如果你的表中有一些数据，请小心，如果你删除表，数据将丢失。

英文:

I would suggest directly adding user_id

To posts table:

  2. CREATE TABLE user_table (
  3.     id SERIAL PRIMARY KEY,
  4.     username VARCHAR NOT NULL UNIQUE,
  5.     email VARCHAR NOT NULL UNIQUE,
  6.     password VARCHAR NOT NULL
  7. );
  8. CREATE TABLE post (
  9.     id SERIAL PRIMARY KEY,
  10.     title VARCHAR,
  11.     content VARCHAR,
  12.     user_id INT,
  13.     date timestamp NOT NULL DEFAULT NOW()
  14. );

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.

huangapple
  • 本文由 发表于 2023年7月20日 17:28:48
  • 转载请务必保留本文链接：https://go.coder-hub.com/76728484.html
  • postgresql
  • rust
  • rust-diesel
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定