Search code examples
databricksdatabricks-unity-catalog

REPLACE TABLE in databricks, primary key conservation

I have many tables in my Databricks Catalog that need a schema upgrade.

I reproduced the data in staging tables with upgraded schema and want to replace the original tables with the content of staging tables, so I'm opting for the REPLACE TABLE command.

(REPLACE TABLE mytable AS SELECT * FROM staging_mytable)

With the new schema comes a change in primary key, and is included in the staging tables. If I use the REPLACE command as provided above, would the new primary key be persisted in mytable, or do I need to specify it after with ADD CONSTRAINT command?

Solution

  • No, you don't have to specify ADD CONSTRAINT here. REPLACE TABLE command in Databricks will automatically persist the new primary key defined in the staging table. Primary key constraint is part of the schema definition, and when you replace the table, the entire schema, including the primary key, is updated.