How to Determine the Auto-Generated Primary Key Used as a Foreign Key for Another Table

How to determine the auto-generated primary key used as a foreign key for another table

Answer to Q1: Use data-modifying CTEs and return the serial PK with the RETURNING clause:

WITH ins_main AS (
   INSERT INTO main(col1)
   VALUES ('some value 1')
   RETURNING main_id    
   )
, ins_submain AS (
   INSERT INTO submain (main_id, col2)
   SELECT main_id, 'some value 2'
   FROM   ins_main
   RETURNING submain_id
   )
INSERT INTO subsub (submain_id, col3)
SELECT submain_id, 'some value 3'
FROM   ins_submain;

Requires Postgres 9.1 or later.

Related answers with explanation and links:

• Insert data in 3 tables at a time using Postgres
• PostgreSQL store value returned by RETURNING

Use Auto incremented unique id primary key as foreign key in another table

1. Insert the row in the parent table primary_details.

2. Then, retrieve the generated uid with the method you want : How to get the insert ID in JDBC?

3. Insert in the child table cust_info using the previously generated uid.

On a side note, your child table should have its own uid and a different field for the foreign key.

Example:

Customer
uid ---> primary key auto incremented

name

age

CustomerDetails
uid ---> primary key auto incremented

customer_id---> foreign key on Customer table

score

comments

Get/use autoincremented Primary Key of a new record as a Foreign Key for a record in another table

You can use inserted_primary_key to find the primary key of the most recently inserted record.

https://kite.com/python/docs/sqlalchemy.engine.ResultProxy.inserted_primary_key

According to the docs it's auto-populated depending on your databse backend, here's an example from https://overiq.com/sqlalchemy-101/crud-using-sqlalchemy-core/

ins = insert(customers)

r = conn.execute(ins, 
    first_name = "Tim", 
    last_name = "Snyder", 
    username = "timsnyder", 
    email = "timsnyder@mail.com",
    address = '1611 Sundown Lane',
    town = 'Langdale'
)
r.inserted_primary_key

How to use an auto incremented primary key as a foreign key as well?

It looks like you have the referencing and referenced tables in reverse. You may want to do:

ALTER TABLE `child ` ADD FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`);

You can also define the foreign key in the CREATE TABLE statement, as follows:

CREATE TABLE `parent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE `child` (
  `parent_id` int(11) DEFAULT NULL,
  `related_ids` int(11) DEFAULT NULL,
  KEY `parent_id` (`parent_id`),
  KEY `related_ids` (`related_ids`),
  FOREIGN KEY (`parent_id`) REFERENCES `parent`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Test case:

INSERT INTO parent (`data`) VALUES ('test data 1');
Query OK, 1 row affected (0.01 sec)

INSERT INTO parent (`data`) VALUES ('test data 2');
Query OK, 1 row affected (0.01 sec)

INSERT INTO child (`parent_id`, `related_ids`) VALUES (1, 100);
Query OK, 1 row affected (0.01 sec)

INSERT INTO child (`parent_id`, `related_ids`) VALUES (2, 100);
Query OK, 1 row affected (0.01 sec)

INSERT INTO child (`parent_id`, `related_ids`) VALUES (3, 100);
ERROR 1452 (23000): Cannot add or update a child row: 
  a foreign key constraint fails 

---

Related Topics