Error Creating Primary Key Constraint in DLT

ani2409 · ‎07-22-2024

Hello There!

Greetings!!

I am getting the following error when trying to Create a DLT table in my Gold Layer..

com.databricks.sql.managedcatalog.PrimaryKeyColumnsNullableException: Cannot create the primary key `x_key` because its child column(s) `x_key` is nullable. Please change the column nullability and retry.

I have configured the table as below with constraints

CREATE OR REFRESH LIVE TABLE XYZ

(

CONSTRAINT pk_key_not_null EXPECT (x_key IS NOT NULL) ,

CONSTRAINT x_key PRIMARY KEY(x_key)

)

.....rest of the code

The same code runs fine in my UAT environment but fails in Dev...I am clueless why its happening...can someone help with it?

szymon_dybczak · ‎07-22-2024

Hi @ani2409 ,

So you need to do as they stated in error message. Column that is chosen to be primary key needs to be defined as NOT NULL.

Additionaly, could you check if on DEV environement you don't have null values in x_key column? Because, in this case, the null values wiill violate NOT NULL constraint.

And it can explain why on UAT the same code works. On UAT you have either:

- differently defined x_key column (with NOT NULL)

- different data compared to DEV environment (they don't contain NULL values in key column)

ani2409 · ‎07-23-2024

Thank you @szymon_dybczak for the response.

As you can see I have already defined the not null constraint in my definition for the primary key x_key
CONSTRAINT pk_key_not_null EXPECT (x_key IS NOT NULL)

But still I am getting the same error.Also I checked in source to check if we have any null values but we dont...and UAT and DEV have same data set.. 😞

szymon_dybczak · ‎07-23-2024

Hi @ani2409 ,

Maybe try to define column with primary key constraint in a way they do in the documentation:

CREATE OR REFRESH MATERIALIZED VIEW sales
(customer_id STRING NOT NULL PRIMARY KEY,
  customer_name STRING,
  number_of_line_items STRING,
  order_datetime STRING,
  order_number LONG,
  order_day_of_week STRING GENERATED ALWAYS AS (dayofweek(order_datetime)),
  CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES main.default.customers(customer_id)
)
COMMENT "Raw data on sales"
AS SELECT * FROM ...

Delta Live Tables SQL language reference | Databricks on AWS