1

I'm new to Databricks and when I want to alter my SQL tables by adding primary or FK, I get an absurd error that can't seem to debug it.

%sql
ALTER TABLE samples_abp215dsdp ADD CONSTRAINT PRIMARY (SampleID);

here is the error message:

Error in SQL statement: ParseException: 
mismatched input '(' expecting {'CHECK', 'FOREIGN', 'PRIMARY', 'UNIQUE'}(line 1, pos 55)

== SQL ==
ALTER TABLE samples_abp215dsdp ADD CONSTRAINT PRIMARY (SampleID)
------------------------------------------------------^^^

any help will be appreciated.

Pishi
  • 15
  • 1
  • 4
  • 1
    According to the [databricks roadmap](https://community.databricks.com/s/feed/0D53f00001l4ucUCAQ), from March ... the foreign key support is supposed to be _sometime this year_, so the capability may not yet be available. – Paul T. May 11 '22 at 01:17
  • Appreciate it, Paul. – Pishi May 11 '22 at 02:21

1 Answers1

3

spark do not have any concept of primary key. As spark is computation engine not database.

There are new features coming in 2022. Follow below list:

  • Unity catalog (seems that it will exists next to hive metastore and it will be possible to migrate)
  • Control metastore, unity creations etc. from admin console,
  • CPU clusters in "create cluster" (similar to SQL endpoints) where you specify how much cpus is needed
  • Data lineage
  • Delta sharing
  • Delta: primary keys
  • Delta: foreign keys
  • Updated partner connect with many new partners joining
  • Notebooks UI: transformations wizards (like select, drop, groupby from droplist etc.)
  • New global search
  • Integration of job service with git
  • Jobs monitoring
  • Repair and rerun failed tasks and pass values between tasks
  • SQL workflows in SQL view
  • create table enhanced via wizard
  • Delta live tables in GA
  • Serverless DBSQL
  • Private links (to VNET and to ExpressRoute) and encryption
  • ML: LightGBM, Prophet Models and Text Feature in Auto ML
  • ML: Feature store integration with Azure MySQL, Azure SQL and CosmosDB
  • ML: Feature store: point in time joins, time-based splits
  • ML: Feature store API
  • ML: Serverless model serving (enable serving button) with high availability SLA
  • ML: one click model deployment into registry

In 2023:

  • Delta: identity column
  • Delta: dynamic checkpoints and incremental commits to limit read/write operations with ADLS

Refer this link

Alex Ott
  • 80,552
  • 8
  • 87
  • 132
Abhishek K
  • 3,047
  • 1
  • 6
  • 19
  • Identity columns is already GA: https://docs.databricks.com/release-notes/runtime/10.4.html#identity-columns-support-in-delta-tables-is-now-generally-available – Alex Ott May 11 '22 at 13:37
  • 1
    Are the foreign key enforced or just used as "annotation"? – user3579222 Oct 13 '22 at 11:35
  • 1
    @user3579222 Per docs [here](https://learn.microsoft.com/en-us/azure/databricks/tables/constraints): "Primary and foreign keys are informational only and are not enforced." – Branson Fox Oct 13 '22 at 16:47