Primary key and not null

[Shree23]

Hi Expert,

how we can get primary key and not null and cluster index in table creation

%Sql

create table table1 values (id int , product char)

expected output

create table table1 values (id int  not null primary key, product char) and cluster index

[Kaniz_Fatma]

Hi @Shree23, To create a table with a primary key that is not null and a clustered index, you can use the following SQL commands:

First, create the table with the NOT NULL constraint and PRIMARY KEY:

sql
CREATE TABLE table1 (
  id INT NOT NULL PRIMARY KEY,
  product CHAR
);

Next, to create a clustered index, use the ALTER TABLE command with the CLUSTER BY clause:

sql
ALTER TABLE table1 CLUSTER BY (id);

Please note that the clustering command is specific to Databricks Delta and may not work in other SQL environments.

[Shree23]

it is giving error message   Dataanalysisexception:table constraint are only supported in unity catalog

[Shree23]

Also tried this steps but still not working ... 

https://learn.microsoft.com/en-us/azure/databricks/data-governance/unity-catalog/enable-workspaces#e...

[Kaniz_Fatma]

Hi @Shree23, The error message you’re encountering, “DataAnalysisException: Table constraints are only supported in Unity Catalog,” indicates that you’re trying to create a table with a primary key constraint, but this feature is only available within the Unity Catalog in Databricks. 

Let’s break it down:

Unity Catalog: This is a specific catalog in Databricks that supports features like primary keys and foreign keys. It’s essential for enforcing constraints on tables.

Primary Key Constraints: These constraints ensure that specific columns in a table contain unique values. In your case, you’re trying to define a primary key for the persons table using the first_name and last_name columns.

Error Message: Since primary key constraints are only supported within the Unity Catalog, you’ll need to ensure that your table creation script is executed within this context.

Here are some steps to troubleshoot:

• Verify that you’re indeed creating the table inside the Unity Catalog.
• Confirm that Unity Catalog is enabled for your workspace.
• If you’re still facing issues, consider raising a support case with Databricks for further investigation.

Remember, primary keys and foreign keys are informational in Databricks—they provide useful metadata.... If you need additional assistance, feel free to ask! 🚀

[Shree23]

Suggestion please 

[Shree23]

sugggestion pls