How to setup relationship between tables In phpMyA

Question

The question I have is, when I create a table for example: table1 with the following columns:

• customerId
• CustomerName
• Address
• State

Where customerId is the PRIMARY KEY with AUTO_INCREMENT.

And then table2 for example with columns:

• purchaseId
• customerId
• product
• cost

Where the PRIMARY KEY is purchaseId and the foreign Key is customerId from table1.

It should mean that I already established a relationship between table1 and table2 using the customerId.

Both these tables are initially empty so I wrote this SQL command:

INSERT INTO table1 (CustomerName,Address,State) VALUES('value1','value2','value3')

This works fine, but when I try to insert into the child table (table2) it tells me:

ERROR a foreign key constraint

So basically what I want to do is insert into the parent table then into the child table so that the customerId shows up in table2 (child Table) as a foreign key and corresponds with customerId in table1 (parent Table).

DO I HAVE TO CREATE THE TWO TABLES FIRST WITHOUT THE FOREIGN KEY THEN TRY TO ESTABLISH A RELATIONSHIP. It Keeps saying there is a constraint as long as the relationship was there.

Answer 1

The table2 foreign key constraint means that any table2 customerId value must appear as a customerId in table1. You are getting the error because you are inserting a customerID into table2 that doesn't appear in table1.

Since the DBMS is generating table1 customerIDs by auto increment, if you insert a row you have to get that value in order to insert a row using that customerID into table2.

I guess you say "I already established a relationship between table1 and table2" to mean "I declared a foreign key constraint". And I guess you think that means "after I insert into table1 the DBMS will use the auto-generated key value as the foreign key value when I insert into table2". But it doesn't mean that. You have to do that yourself. The foreign key constraint just means that the DBMS checks that every table2 customerId value appears as a table1 customerId value.

You can and must use any previously inserted key value as the corresponding value when you insert into a table with a foreign key to that key.

To get back the auto incremented key value generated by the DBMS use LAST_INSERT_ID():

INSERT INTO table1 (CustomerName,Address,State)
VALUES('value1','value2','value3');
INSERT INTO table2 (customerId,product,cost)
VALUES(LAST_INSERT_ID(),'valueA','valueB');

This is what it is for. But here are the problems if you don't use it.

First, if you are not in a serialized transaction then you must use LAST_INSERT_ID(). Because after your table1 insert but before your table2 insert others could have added rows and/or deleted rows including your new row and/or changed rows including your new row. So you cannot rely on querying table1 after its insert get some customerId value that you know you added.

Second, suppose you are in a serialized transaction and you don't use LAST_INSERT_ID().

If (CustomerName,Address,State) is also a superkey of table1, ie its values are unique, ie SQL UNIQUE/KEY/PK is declared on all or some of its columns, then you can use it to query for the associated new customerId:

set @customerId = (
    SELECT customerId
    FROM table1
    WHERE CustomerName = 'value1'
    AND Address = 'value2'
    AND State = 'value3');
INSERT INTO table2 (customerId,product,cost)
VALUES(@customerId,'valueA','valueB');

But if (CustomerName,Address,State) is not a superkey of table1 then you cannot do this. Because other rows that are duplicates for that subrow could be in table1. So you could get multiple rows back. So you would not know which is the newest one. Instead you have to query table1 before the insert, then insert, then find the difference between the old and new sets of customerIds:

CREATE TEMPORARY TABLE table1old (
    customerId (int) PRIMARY KEY
    );
INSERT INTO table1old
SELECT customerId FROM table1;

INSERT INTO table1 (CustomerName,Address,State)
VALUES('value1','value2','value3');

set @customerId = (
    SELECT customerId
    FROM table1
    WHERE CustomerName NOT IN table1old);
INSERT INTO table2 (customerId,product,cost)
VALUES(@customerId,'valueA','valueB');

Just use LAST_INSERT_ID().

PS: Interestingly, given the table definitions, ideally one could write:

INSERT INTO (
    SELECT CustomerName,Address,State,A,B
    FROM table1 JOIN table2
    USING (CustomerId))
VALUES('value1','value2','value3','valueA','valueB')

since there is just one pair of new table1 & table2 values that can result. There are some legal updates through views in SQL, although none involving multiple tables in MySQL currently