0

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;
1
  • Do you need to insert one row or a bunch of rows in one statement?
    – Akina
    Commented Dec 14, 2020 at 4:52

2 Answers 2

2

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);
1
  • And it might be appropriate to put those in a transaction (BEGIN...COMMIT) if they aren't already.
    – Rick James
    Commented Dec 14, 2020 at 17:35
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.