PostgreSQL : is it possible to add in child table first using alter table to remove F.K and then adding data in parent table?

Viewed 43

I've 2 tables Address(parent) and Store(child) and both the tables are empty.

Is it possible to insert data in the store table(child) with a foreign key value in this and this foreign key value gets added in Address(parent) automatically using postgreSQL query?

Address(Parent)

id (P.K)
city
street 
house


Store(Child)

id 
phone
name
Address_id (F.K)

Step 1: first add data in the store table containing F.K of the Parent table considering both of them are empty. I've tried adding by disabling F.K but as I re-assign F.k It is giving me

"ERROR: insert or update on table "store" violates foreign key constraint "store_address_fkey" Detail: Key (address)=(1) is not present in table "address". "

Step 2: After Step1 problem resolves I want to add corresponding F.K(store) to P.K(address) in Parent table.

Any method available to do this type of query generation.

Error:

ERROR: Migration of schema "public" to version "4 - Insert" failed! Changes successfully rolled back.
ERROR: Migration V4__Insert.sql failed
-------------------------------
SQL State  : 23503
Error Code : 0
Message    : ERROR: insert or update on table "store" violates foreign key constraint "store_address_fkey"
  Detail: Key (address)=(1) is not present in table "address".
Location   : sql/V4__Insert.sql (/home/naveen.p@ah.zymrinc.com/flyway-9.2.2/sql/V4__Insert.sql)
Line       : 1
Statement  : INSERT INTO store(ID, PHONE, NAME, ADDRESS) VALUES(1, '88002001453', 'MskSouvenir', 1)

Caused by: Migration V4__Insert.sql failed
-------------------------------
SQL State  : 23503
Error Code : 0
Message    : ERROR: insert or update on table "store" violates foreign key constraint "store_address_fkey"
  Detail: Key (address)=(1) is not present in table "address".
Location   : sql/V4__Insert.sql (/home/naveen.p@ah.zymrinc.com/flyway-9.2.2/sql/V4__Insert.sql)
Line       : 1
Statement  : INSERT INTO store(ID, PHONE, NAME, ADDRESS) VALUES(1, '88002001453', 'MskSouvenir', 1)

Caused by: org.postgresql.util.PSQLException: ERROR: insert or update on table "store" violates foreign key constraint "store_address_fkey"
  Detail: Key (address)=(1) is not present in table "address".


 

1 Answers

No, it is not possible to insert data into a child table if the corresponding parent record does not exist and the FK is active. The point of defining FKs is precisely to prevent this happening

Related