cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

unvalidated the primary and foreign keys constraints?

jeremy98
New Contributor III

Hello community,
I'm inserting in a table defined (with primary key and foreign key set) some records in overwrite mode every moment I run a workflow where the task is defined. Why after inserting those records the DDL schema changes? Why I have my primary and foreign keys unset? Seems that changes the schema!

1 ACCEPTED SOLUTION

Accepted Solutions

Alberto_Umana
Databricks Employee
Databricks Employee

Hi @jeremy98,

When you use the "insert overwrite" mode in Databricks, it can lead to the schema being reset, which includes the removal of primary and foreign key constraints. This happens because the "insert overwrite" operation essentially replaces the entire table, which can result in the loss of any constraints that were previously defined

 

To maintain your primary and foreign key constraints, you might consider using a different strategy for updating your table. For example, using the "merge" strategy instead of "insert overwrite" can help preserve the schema and constraints. The "merge" strategy allows you to update, insert, or delete records based on a condition, which can be more efficient and less disruptive to the schema

View solution in original post

1 REPLY 1

Alberto_Umana
Databricks Employee
Databricks Employee

Hi @jeremy98,

When you use the "insert overwrite" mode in Databricks, it can lead to the schema being reset, which includes the removal of primary and foreign key constraints. This happens because the "insert overwrite" operation essentially replaces the entire table, which can result in the loss of any constraints that were previously defined

 

To maintain your primary and foreign key constraints, you might consider using a different strategy for updating your table. For example, using the "merge" strategy instead of "insert overwrite" can help preserve the schema and constraints. The "merge" strategy allows you to update, insert, or delete records based on a condition, which can be more efficient and less disruptive to the schema

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group