MySQL Insert Values Into Normalized Relational Database (multiple tables) at the same time

Question

I have normalised my database to 4NF. How do I now insert values at the same time when records have been split into multiple tables? most of the child tables reference their parent table CUSTOMER column customer_ID as their foreign keys. The primary key in the parent table is auto-incremented. There are many tables set up this way, but I thought the following 3 samples would sufficiently paint the picture:

---

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;

Answer 1

Thank you for the assist. I managed to put together a working solution as follows:

INSERT INTO `customer` (`CustomerID`, `CusCatID`, `First Name`, `LastName`, `EmailAddress`, `GENDER`, `DOB`, `Cus_Status_ID`) 
VALUES (NULL, '1', 'John', 'Doe', '[email protected]', '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);

Answer 2

You'll want to use LAST_INSERT_ID() to get the identity value that was generated after the INSERT for the PrimaryKey of the Parent table then use it for the ForeignKey to INSERT into the children tables.

Per the documentation:

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

You can see more information in this StackOverflow post.