如何參考父表添加兩個外鍵并在子表中添加值-有解無憂

我的資料庫 USERS、QUESTION 和 ANSWER 中有 3 個表。我希望 QUESTION 表與 USERS 和 ANSWER 表以及 ANSWER 表與 USER 和 QUESTION 表進行互動。到目前為止，我已經使用外鍵將 QUESTION 表與 USERS 表相關聯，并將 ANSWER 表與 QUESTION 表相關聯。當我嘗試在 mysql 的 ANSWER 表中添加第二個外鍵時，我收到一個錯誤 a) 重復的外鍵約束名稱 'userid' 即使我嘗試使用查詢添加它仍然給我一個錯誤。

我可以在外鍵的幫助下參考 USERS 表在 QUESTION 表中添加值嗎？例如，我在 USERS 表中有用戶，用戶 ID 為 1，我想在 QUESTION 表中添加值，其中 quesid 為 1，用戶 ID 也為 1。直到現在我已經嘗試過這個查詢

INSERT INTO users_ques 
    SELECT "What is JAVA","Please Share the details" FROM users WHERE quesid = 1;

下面是我的表格和他們的腳本

用戶表

CREATE TABLE `users` (
  `userid` int NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `firstname` varchar(45) NOT NULL,
  `lastname` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `address` varchar(45) DEFAULT NULL,
  `phone` int DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

問題表

CREATE TABLE `users_ques` (
  `quesid` int NOT NULL AUTO_INCREMENT,
  `ques` varchar(50) NOT NULL,
  `ques_desc` varchar(150) NOT NULL,
  PRIMARY KEY (`quesid`),
  CONSTRAINT `userid` FOREIGN KEY (`quesid`) REFERENCES `users` (`userid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

答案表

CREATE TABLE `users_ans` (
  `ansid` int NOT NULL AUTO_INCREMENT,
  `ans` varchar(200) NOT NULL,
  PRIMARY KEY (`ansid`),
  CONSTRAINT `quesid` FOREIGN KEY (`ansid`) REFERENCES `users_ques` (`quesid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

uj5u.com熱心網友回復：

推薦結構（作為基礎，根據實際資料調整）：

CREATE TABLE user (
  user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- unique index
  username VARCHAR(255) NOT NULL, 
  password VARBINARY(255) NOT NULL,                -- password hash, binary
  firstname VARCHAR(255) NOT NULL,
  lastname VARCHAR(255) DEFAULT NULL,
  email VARCHAR(255) DEFAULT NULL,
  address VARCHAR(255) DEFAULT NULL,
  phone BIGINT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE question (
  question_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- unique index
  question_text TEXT NOT NULL,        
  user_id INT NOT NULL,                                 -- id of the user who have asked
  CONSTRAINT user_to_question FOREIGN KEY (user_id) REFERENCES user (user_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE answer (
  answer_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  -- unique index
  answer_text TEXT NOT NULL,
  question_id INT NOT NULL,                           -- what question it answers on
  user_id INT NOT NULL,                               -- id of the user who have answered
  CONSTRAINT user_to_answer FOREIGN KEY (user_id) REFERENCES user (user_id),
  CONSTRAINT question_to_answer FOREIGN KEY (question_id) REFERENCES question (question_id)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

在這個方案中，我沒有顯示用戶不能回答他自己的問題的限制。我認為這樣的限制是沒有意義的。但是，如果您仍然需要它，那么您可以通過在answer表上創建 2 個觸發器（BEFORE INSERT 和 BEFORE UPDATE）來添加它。

uj5u.com熱心網友回復：

您得到的錯誤與它所說的完全一樣，您正在嘗試使用該符號添加另一個約束，userid但您已經在users_ques表上有一個具有該名稱的約束。

根據13.1.20.5 外鍵約束

CONSTRAINT 符號值（如果已定義）在資料庫中必須是唯一的。重復符號會導致類似以下錯誤：ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121)。

因此，您要么需要為您的約束分配一個資料庫唯一名稱，要么允許服務器為您分配一個。如果您想明確命名它們，請使用不太可能發生沖突的富有表現力的名稱。類似的東西FK_<table_name>_<column_name>。

此外，可能是復制和粘貼錯誤，但您users_ans看起來完全錯誤。ansid似乎是典型的代理鍵，但隨后用作users_ques. quesid在你的約束下。看起來你不見了userid，而且quesid。您的約束和列存在類似問題users_ques

/* Allowing the server to create the indices and auto-name the constraints */
CREATE TABLE `users_ans` (
    `ansid` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `quesid` int UNSIGNED NOT NULL,
    `userid` int UNSIGNED NOT NULL,
    `ans` varchar(200) NOT NULL,

    PRIMARY KEY (`ansid`),

    FOREIGN KEY (`quesid`)
      REFERENCES `users_ques` (`quesid`),
    FOREIGN KEY (`userid`)
      REFERENCES `users` (`userid`)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

/* Explicitly naming indices and constraints */
CREATE TABLE `users_ans` (
    `ansid` int UNSIGNED NOT NULL AUTO_INCREMENT,
    `quesid` int UNSIGNED NOT NULL,
    `userid` int UNSIGNED NOT NULL,
    `ans` varchar(200) NOT NULL,

    PRIMARY KEY (`ansid`),
    INDEX `idx_users_ans_quesid` (quesid),
    INDEX `idx_users_ans_userid` (userid),

    CONSTRAINT `FK_users_ans_quesid`
      FOREIGN KEY `idx_users_ans_quesid` (`quesid`)
      REFERENCES `users_ques` (`quesid`),
    CONSTRAINT `FK_users_ans_userid`
      FOREIGN KEY `idx_users_ans_userid` (`userid`)
      REFERENCES `users` (`userid`)
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

如果用戶每個問題只能有一個答案，那么您可以放棄當前的代理鍵以支持 (quesid, userid) 上的自然鍵 -

/* This option only applies if a user can only answer a question once */
CREATE TABLE `users_ans` (
    `quesid` int UNSIGNED NOT NULL,
    `userid` int UNSIGNED NOT NULL,
    `ans` varchar(200) NOT NULL,

    PRIMARY KEY (`quesid`, `userid`),
    INDEX `idx_inv_primary` (`userid`, `quesid`), -- probably needed for joins in both directions and will be used by `FK_users_ans_userid`

    CONSTRAINT `FK_users_ans_quesid`
      FOREIGN KEY (`quesid`)
      REFERENCES `users_ques` (`quesid`)
      ON DELETE RESTRICT
      ON UPDATE RESTRICT,
    CONSTRAINT `FK_users_ans_userid`
      FOREIGN KEY (`userid`)
      REFERENCES `users` (`userid`)
      ON DELETE RESTRICT
      ON UPDATE RESTRICT
) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

如最后一個示例所示，我更喜歡包含參考操作，即使使用默認值也是如此，因為它使意圖清晰。

這是一個小db<>fiddle

您的 QUESTION 表似乎命名不當。根據您在 create table 陳述句上方的標題，questions這似乎是該表的明顯名稱。

我不明白您的第二個問題，也不明白您為什么要將其添加userid到“問題表”中。如果您需要更多幫助，請在您的問題中添加更多詳細資訊以澄清問題。

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/412732.html

標籤：

上一篇：從Flask服務器獲取音頻并在React上以可播放格式顯示音頻

下一篇：如何從外部.txt檔案中讀取資料，將它們存盤在陣列中，過濾并將它們寫入Fortran90的新檔案中？