CodeIgniter Forums

Full Version: error inserting 2 rows in postgres
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hello,
In my codeigniter 3.1.0, postgresql 9.4.10, Ion_auth
I create 1 user user in pd_ion_users table and 1 rows in related pd_user_bank_account, which has rule:


Code:
ALTER TABLE ONLY pd_user_bank_account
   ADD CONSTRAINT pd_user_bank_account_id_fkey FOREIGN KEY (id) REFERENCES pd_ion_users(id);

In my action :

PHP Code:
       ...
       $this->db->trans_start();

       $user_id $this->ion_auth->register$postArray['username'], $postArray['username'], $postArray['email'], $additional_data  array(  $user_group_array   );
       echo '<pre>NEW $user_id::'.print_r($user_id,true).'</pre>'// I see new inserted ID, as 64

       ... 
       $query"select * from pd_update_user_bank_account( p_id := 64, p_country := 'ae', p_city := 'V', p_address := 'V', p_postcode := '01546', p_state := 'MA, Worcester7', 
p_business_name := 'aaAA', p_business_tax_id := 'A', p_owner_dob := '2017-03-17', p_first_name := 'a', p_last_name := 'A', p_ssn_last_4 := 'DDDD', p_type := 'I', 
p_acceptance_date := '2017-03-13', p_acceptance_ip := '127.0.0.1' )"

       $tr=pg_query($dbconn$query);


       if ($this->db->trans_status() === FALSE) {
           $this->db->trans_rollback();
       } else {
           $this->db->trans_commit();
       }

... 

And I have error:

Code:
Query failed: ERROR:  insert or update on table &quot;pd_user_bank_account&quot; violates foreign key constraint &quot;pd_user_bank_account_id_fkey&quot;
DETAIL:  Key (id)=(64) is not present in table &quot;pd_ion_users&quot;.
CONTEXT:  SQL statement &quot;INSERT INTO pd_user_bank_account ( id, country, city, address, postcode, state, business_name, business_tax_id, owner_dob, first_name, last_name, ssn_last_4, type, acceptance_date, acceptance_ip  )

       VALUES(  p_id,  p_country, p_city, p_address, p_postcode, p_state, p_business_name, p_business_tax_id, p_owner_dob, p_first_name, p_last_name, p_ssn_last_4, p_type,
p_acceptance_date, p_acceptance_ip  )&quot;
PL/pgSQL function pd_update_user_bank_account(integer,character varying,character varying,character varying,character varying,character varying,character varying,
\character varying,date,
character varying,character varying,character varying,
type_user_bank_account_type,date,character varying,timestamp without time zone) line 11 at SQL statement /mnt/_work_sdb8/wwwroot/wprods/application/core/MY_Controller.php 781

In Log I see lines:

Code:
INSERT INTO "pd_ion_users" ("active_status", "first_name", "last_name", "company", "phone", "country", "address", "address2", "city", "state", "postcode", "backend_template_id",
"frontend_template_id", "user_url", "username", "password", "email", "ip_address", "created_on") VALUES
('A', 'zz', 'zzz', 'zzzz', 'zzzz', 'us', 'bbbb', 'bbbbb', 'bb', '333', '01546', 1, 1, 'zzzz', 'z', '$2y$08$Lvf1XoXFNkjK1zmqgyMBF.5SsSYEav2aqypXEyaPir8kHudfRkxCW', '

[email=zz@sda.com]zz@sda.com[/email]', '127.0.0.1', 1489415280)

select * from pd_update_user_bank_account( p_id := 64, p_country := 'ae', p_city := 'V', p_address := 'V', p_postcode := '01546', p_state := 'MA, Worcester7', p_business_name := 'aaAA', p_business_tax_id := 'A', p_owner_dob := '2017-03-17', p_first_name := 'a', p_last_name := 'A', p_ssn_last_4 := 'DDDD', p_type := 'I', p_acceptance_date := '2017-03-13', p_acceptance_ip := '127.0.0.1' )

What is the reason and how to fix it?
Usually I use 1 pg function for adding of more 1  row, but in this case function $this->ion_auth->register makes some specific actions, which are not implemented on server...



Thanks!