Which id column acts as the unique identifier (primary key) in Odoo?

[SakshiBansal11]

Hi Community,

I’m working on integrating data from multiple Odoo tables into a Microsoft Fabric Lakehouse. I’ve noticed that many tables have an id column, but I want to confirm which of these actually act as the unique identifier (primary key) for each table.

Could someone please confirm if the id column is the true primary key in these tables, or if any table uses a different unique identifier?

As i have notice this id column is not unique for some tables .

This will help ensure accurate joins and proper SCD (Slowly Changing Dimension) logic in my Fabric pipeline.

Thanks in advance!

[v-karpurapud]

Hi @SakshiBansal11 

We have not yet received a response from you regarding your query. If the previous response was helpful, please let us know. If not, feel free to provide more details so we can assist you further.

Thank you.

[v-karpurapud]

Hi @SakshiBansal11 

Thank you for reaching out to the Microsoft Fabric community.
 
As @BeaBF  noted, while the id column serves as the primary key in most Odoo tables, its uniqueness is limited to each table and does not extend across the entire database. Since Odoo operates on a PostgreSQL backend, there are exceptions to this rule due to features such as model inheritance, many-to-many relationships, and database views. In these scenarios, id values may overlap between related tables or appear non-unique in certain views. To reliably identify primary keys, using the recommended SQL query against PostgreSQL’s system catalog is an effective approach. When integrating with Microsoft Fabric Lakehouse, it is best practice to use only confirmed primary key columns from base tables for joins and to leverage fields like write_date or __last_update for tracking changes in Slowly Changing Dimensions (SCD).

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[BeaBF]

@SakshiBansal11 Hi! You can use this query to check PKs in PostgreSQL:

SELECT
kcu.table_name,
kcu.column_name
FROM
information_schema.table_constraints tco
JOIN
information_schema.key_column_usage kcu
ON kcu.constraint_name = tco.constraint_name
WHERE
tco.constraint_type = 'PRIMARY KEY';

While most Odoo tables do indeed use an id column as the primary key, not all id columns are guaranteed to be globally unique across all tables, and some tables may behave differently due to views, inheritance, or external IDs.

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!