Add support for foreign key specification.

[julianbergerkoerber]

The Databricks SQL dialect offers the possibility to add a foreign key reference in the CREATE TABLE statement. I would like to use this with the datacontract.yml, but currently there is no possibility to do this.

For example, I would like to add the foreignKey as a setting for the fields settings, like this:

foreignKey:
  catalog: default
  schema: default
  table: producers
  column: producer

Is this possible, or are there any problems with this?
I also added an example on the desired output for my issue:

Example Contract

dataContractSpecification: 0.9.3
id: my-data-contract-id
info:
  title: My Data Contract
  version: 0.0.1
servers:
  production:
    type: databricks
    catalog: default
    schema: default
models:
  cars:
    description: The Cars table contains cars.
    type: table
    fields:
      producer:
        description: Producer of the car.
        type: string
        required: true
        primary: false
        foreignKey:
          catalog: default
          schema: default
          table: producers
          column: producer
  producers:
    description: The Procuders table contains car producer.
    type: table
    fields:
      producer:
        description: A Car Producer.
        type: string
        required: true
        primary: true

Output:

-- Data Contract: my-data-contract-id
-- SQL Dialect: databricks
CREATE OR REPLACE TABLE default.default.cars (
  producer STRING not null COMMENT "Producer of the car."
) COMMENT "The Cars table contains cars.";
CREATE OR REPLACE TABLE default.default.producers (
  producer STRING not null primary key COMMENT "A Car Producer."
) COMMENT "The Procuders table contains car producer.";

Desired Output:

-- Data Contract: my-data-contract-id
-- SQL Dialect: databricks
CREATE OR REPLACE TABLE default.default.cars (
  producer STRING not null COMMENT "Producer of the car."
  FOREIGN KEY (producer) REFERENCES default.default.producers(producer)
) COMMENT "The Cars table contains cars.";
CREATE OR REPLACE TABLE default.default.producers (
  producer STRING not null primary key COMMENT "A Car Producer."
) COMMENT "The Procuders table contains car producer.";

[jochenchrist]

The idiomatic way would be to use references:

dataContractSpecification: 0.9.3
id: my-data-contract-id
info:
  title: My Data Contract
  version: 0.0.1
servers:
  production:
    type: databricks
    catalog: default
    schema: default
models:
  cars:
    description: The Cars table contains cars.
    type: table
    fields:
      producer:
        description: Producer of the car.
        type: string
        required: true
        primary: false
        references: producers.producer
  producers:
    description: The Procuders table contains car producer.
    type: table
    fields:
      producer:
        description: A Car Producer.
        type: string
        required: true
        primary: true

Using this references field in sql export is not yet implemented, but should be easy to do. Would you be interested in contributing a PR?

[julianbergerkoerber]

Alright, let me have a look :)

[julianbergerkoerber]

After setting up the work environment, I have multiple failing tests without changing anything. Is this behaviour common and are there any tipps to fix this?

[stefannegele]

Hi @julianbergerkoerber . This is not common. Please elaborate your specific problem and errors in a dedicated ticket.

[jochenchrist]

Closed as stale.
If your issues persist, please open a new ticket with a minimal example