Inserting records using a transaction for table with child rows

[jmsilva]

Hi all,

Got a table with basic member information which contains 2 references to an address table, on the members table got a postal_address_id and physical_address_id fields referencing id of address table which are childs of members. Created a dialog and included all needed fields for data entry plus hidden id fields which are auto-increment fields for the members and both instances of the address table.

How do I make it so that inside the transaction the address records are inserted first and last the members record after assigning the new id values for both new addresses on the corresponding members record fields so that I do not get the error below.

Here is what my database looks like, should I denormalize and forget about all this:
Member Vendor Address
----------- ---------- -----------
MemberID VendorID AddressID
AddressID1 AddressID1 Street
AddressID2 AddressID2 City...

Error executing SQL Insert
INSERT INTO members (postal_address_id, physical_address_id, prefix, first_name, last_name, siffix, company_name, telephone_number, email_address) VALUES (:members_postal_address_id, :members_physical_address_id, :members_prefix, :members_first_name, :members_last_name, :members_siffix, :members_company_name, :members_telephone_number, :members_email_address)

Database API specific error

Your database has returned the following error code and description to Alpha Five.
Consult your database documentation for further information.

1452 - 'Cannot add or update a child row: a foreign key constraint fails (`mysmallbizz`.`members`, CONSTRAINT `postal_address_fk` FOREIGN KEY (`postal_address_id`) REFERENCES `addresses` (`id`))'

[lvasic]

Re: Inserting records using a transaction for table with child rows

The issue is with your constraint. Remove this from the SQL side and try your code. It looks like you are trying to build a one to many relationship table. If that is true then what logical is on the FOREIGN KEY?

[aburningflame]

Re: Inserting records using a transaction for table with child rows

do not denormalize. if you denormalize, you will have bad data!
you are receiving this error, because your grid is passing bad data.

INSERT INTO members (postal_address_id, physical_address_id, prefix, first_name, last_name, siffix, company_name, telephone_number, email_address) VALUES (:members_postal_address_id, :members_physical_address_id, :members_prefix, :members_first_name, :members_last_name, :members_siffix, :members_company_name, :members_telephone_number, :members_email_address)

I suspect your members_postal_address_id is being passed incorrectly.

If you want help debugging, feel free to contact us.

We would be glad to help.

[aburningflame]

Re: Inserting records using a transaction for table with child rows

The constraint is failing because the data is bad. If he removes the constraint and it works, then what? He's just left with data that references nothing.
And the next time he tries to inner join that table to the member address table - he is going to have skipped records.

There is an issue with your members_postal_id - that must be it.
Maybe it's passing 0, or a non existent postal id.

It's hard to say without actually looking at your code, however, like I said we'd be glad to help.

Feel free to send us an email for information

[jmsilva]

Re: Inserting records using a transaction for table with child rows

Constraint is 1 to 1...

[jmsilva]

Re: Inserting records using a transaction for table with child rows

Thanks for trying to help guys, should have been more specific, I am using a dialog, not a grid and it is for data entry only, the tables have no data yet, still in the design and development phase. After analyzing the relationships I have come to the conclusion that the addresses would have to be created before being able to link then to the members table and that is just not the purpose here.

Suppose, I been following an animal which does not exist yet in our limited relational database world, see I wanted to use the same address class object (table) for all the needs of the database (members, employees, customers, vendors, stores, etc), in other words I should probably de-normalize.

Besides that, you are correct Scott, the postal and the physical address_id's don't have a value, suppose I could make it work if I knew where to edit the SQL statement that Alpha generates for the after validate server side event, but can not find it, that would provably fail also as once the address record were inserted I would not have a way of retrieving the auto-incremented id primary key.

Check-out my new post, Does Alpha support many to many relationships