MySQL同時將值插入規範化關係數據庫（多個表）

我已將我的數據庫標準化為 4NF。現在，當記錄已拆分為多個表時，如何同時插入值？大多數子表引用它們的父表 CUSTOMER 列customer_ID作為它們的外鍵。父表中的主鍵是自動遞增的。以這種方式設置的表格有很多，但我認為以下 3 個範例足以描繪圖片：

---

CREATE TABLE customer (
 CustomerID INT(11) NOT NULL AUTO_INCREMENT,
 CusCatID INT(2) NOT NULL DEFAULT 1 COMMENT 'This is supposed to reference the customer catergpries table. But its giving a foreihn key/ index probpem?',
 `First Name` VARCHAR(50) NOT NULL,
 LastName VARCHAR(255) NOT NULL,
 EmailAddress VARCHAR(50) NOT NULL,
 GENDER BIT(1) NOT NULL,
 DOB DATE NOT NULL,
 Cus_Status_ID INT(11) NOT NULL DEFAULT 1,
 PRIMARY KEY (CustomerID)
)

ALTER TABLE customer 
 ADD CONSTRAINT FK_customer_CusCatID FOREIGN KEY (CusCatID)
   REFERENCES customer_category(CusCategoryID) ON DELETE NO ACTION;

ALTER TABLE customer 
 ADD CONSTRAINT FK_customer_Cus_Status_ID FOREIGN KEY (Cus_Status_ID)
   REFERENCES status(StatusID) ON DELETE CASCADE ON UPDATE CASCADE;

---

CREATE TABLE customer_accounts (
 Cus_ID INT(11) DEFAULT NULL,
 AccountNo INT(11) NOT NULL
)

ALTER TABLE customer_accounts 
 ADD UNIQUE INDEX AccountNo(AccountNo);

ALTER TABLE customer_accounts 
 ADD CONSTRAINT FK_customer_accounts_AccountNo FOREIGN KEY (AccountNo)
   REFERENCES account(AccountNo) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE customer_accounts 
 ADD CONSTRAINT FK_customer_accounts_Cus_ID FOREIGN KEY (Cus_ID)
   REFERENCES customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;

---

CREATE TABLE customer_authentication (
 auth_id_cusId INT(11) NOT NULL,
 Username VARCHAR(255) NOT NULL,
 Password VARCHAR(255) NOT NULL,
 PIN INT(6) NOT NULL,
 PRIMARY KEY (auth_id_cusId)
)

ALTER TABLE customer_authentication 
 ADD UNIQUE INDEX Username(Username);

ALTER TABLE customer_authentication 
 ADD CONSTRAINT FK_customer_authentication_auth_id_cusId FOREIGN KEY (auth_id_cusId)
   REFERENCES customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;

您需要使用LAST_INSERT_ID()來獲取在父表的 PrimaryKey 插入之後生成的標識值，然後將其用於 ForeignKey 以插入到子表中。

根據文件：

在沒有參數的情況下，LAST_INSERT_ID() 返回一個 BIGINT UNSIGNED（64 位）值，表示作為最近執行的 INSERT 語句的結果為 AUTO_INCREMENT 列成功插入的第一個自動生成的值。如果沒有成功插入行，則 LAST_INSERT_ID() 的值保持不變。

您可以在此StackOverflow 文章中查看更多資訊。

謝謝你的幫助。我設法整理了一個可行的解決方案，如下所示：

INSERT INTO `customer` (`CustomerID`, `CusCatID`, `First Name`, `LastName`, `EmailAddress`, `GENDER`, `DOB`, `Cus_Status_ID`) 
VALUES (NULL, '1', 'John', 'Doe', 'vkc@fms.cd', '1', '1980-12-24', '1');
set @csid := LAST_INSERT_ID();
INSERT INTO customer_authentication (auth_id_cusId, Username, Password, PIN)
 VALUES ( @csid, ' @doeman', 'Xpasssw0rd', 0);
INSERT INTO account (AccountNo, AccountTypeID, Balance, AccStatusID)
 VALUES (0, 0, 0, 0);
INSERT INTO customer_accounts (Cus_ID, AccountNo)
 VALUES (@csid, 254458543);

引用自：https://dba.stackexchange.com/questions/281468