Insert data to two table with primary and foreign key

Question

I tried to find solution for my problem on other questions, but I couldn't. Maybe my problem is in the database tables. I made an application that connects to a Firebird database, and I try to insert data into two tables. One of them is like this:

Id	Name	AddressId
2	NameOfCustomer	1

and second

Id	Address	Default	CustomerId
1	Long Street 1, 88300 London	1	2

I write insert in SQL like this:

EXECUTE BLOCK AS
declare variable id_customer bigint;
declare variable id_address bigint;
BEGIN
select gen_id(CUSTOMER_ID_GEN, 1) from rdb$database into :id_customer;
select gen_id(CUSTOMER_ADDRESSES_ID_GEN, 1) from rdb$database into        :id_address;
INSERT INTO CUSTOMER (Id, Name, AddressId) values (:id_customer, 'Name', :id_address);
INSERT INTO CUSTOMER_ADDRESS (Id, Address, Default, CustomerId) values   (:id_address, 'Short Street 1', 1, :id_customer);
END

When I run code I have exception like this:

violation of FOREIGN KEY constraint "CUSTOMER_ADDRESS_ID_FK" on table "CUSTOMER" Foreign key reference target does not exist.

Maybe someone know how to write this SQL in Firebird without error? Should I disable foreign key for a while, and then try to insert data?

Below DLLs of two tables:

CREATE TABLE CUSTOMER
(
  ID Integer NOT NULL,
  NAME STRING50_D NOT NULL,
  ADDRESS_ID Integer,
  PRIMARY KEY (ID)
);

ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_ADDRESS_ID_FK FOREIGN KEY (ADDRESS_ID) REFERENCES CUSTOMER_ADDRESS (ID) ON DELETE SET DEFAULT;

CREATE TABLE R3_CONTACT_ADDRESSES
(
  ID Integer NOT NULL,
  ADDRESS STRING200_D,
  DEFAULT BOOLEAN_D,
  CUSTOMER_ID Integer NOT NULL,
  PRIMARY KEY (ID)
);

ALTER TABLE CUSTOMER_ADDRESS ADD CONSTRAINT CUSTOMER_ADDR_CUSTOMER_ID_FK FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (ID) ON UPDATE CASCADE ON DELETE CASCADE;

user_466862 · Answer

Your definition has a cyclic dependency, because CUSTOMER has a foreign key to CUSTOMER_ADDRESS, and CUSTOMER_ADDRESS has a foreign key to CUSTOMER. Constraints in Firebird are not deferred until commit, but checked immediately. As a result, you cannot insert CUSTOMER because the referenced address does not exist yet, and you cannot insert CUSTOMER_ADDRESS first either, because then the customer does not exist yet.

To fix this, you need to take one of the following approaches

Insert CUSTOMER with ADDRESS_ID set to NULL (or leave it out of the insert), and update CUSTOMER to set ADDRESS_ID after you inserted the record into ADDRESS_ID.
If a customer has a single address, then put the address information in CUSTOMER and drop CUSTOMER_ADDRESS.
Remove ADDRESS_ID from CUSTOMER, and rely on CUSTOMER_ID in CUSTOMER_ADDRESS to link the records.
Remove CUSTOMER_ID from CUSTOMER_ADDRESS, and rely in ADDRESS_ID in CUSTOMER to link the records. With this approach you need to insert CUSTOMER_ADDRESS first. If this works for you, then taking approach 2 might be the better choice.

Insert data to two table with primary and foreign key

1 Answers