Primary and Foreign Keys in Databricks
Learn how primary and foreign keys work in Databricks Delta tables and Unity Catalog, and how constraints define relationships in your service records.
Contents
- What a Primary Key Constraint Does
- How Foreign Keys Maintain Table Relationships
- Unity Catalog Support for Constraints
- Databricks Key Constraint Questions
- Does Databricks enforce primary key constraints?
- Can a foreign key reference a primary key in another table?
- How are primary keys different from unique constraints?
- Where can I define key constraints on a Delta table?
In Databricks, primary and foreign keys are table constraints that identify rows and link tables together. A primary key constraint ensures each row in a Delta table has a unique value in one or more columns. A foreign key constraint enforces a relationship between two tables by referencing a primary key in another table. These constraints help keep your service and cost records consistent when stored in Delta tables under Unity Catalog.
What a Primary Key Constraint Does
A primary key constraint makes a column or set of columns uniquely identify each row in a Delta table. Every value in that column must be distinct and not null. When you define a primary key, Databricks enforces uniqueness within the table. This is useful for service records where a work order number must appear only once.
How Foreign Keys Maintain Table Relationships
A foreign key constraint connects a column in one Delta table to the primary key of another table. It ensures that the value in the foreign key column exists in the referenced table. For example, a parts table can reference a vehicle table using the vehicle ID. This linkage keeps table relationships accurate across your data model.
Unity Catalog Support for Constraints
Unity Catalog supports both primary key and foreign key constraints on Delta tables. These constraints are informational by default and are not used for query optimization. They are validated when you run explicit commands or when enabled for enforcement. This support helps you document and validate relationships in a managed environment.
Databricks Key Constraint Questions
Does Databricks enforce primary key constraints?
Enforcement depends on the setting. Databricks can enforce primary key constraints when you enable constraint enforcement on a Delta table. Without enforcement, the constraint is informational only. You can validate existing data against the constraint using a specific command.
Can a foreign key reference a primary key in another table?
Yes, a foreign key constraint in Databricks can reference a primary key in another Delta table. The referenced table must have a primary key defined on the matching column. The foreign key constraint ensures referential integrity between the two tables. This is a core part of defining table relationships.
How are primary keys different from unique constraints?
Primary key columns cannot contain null values, whereas unique constraints allow nulls. A table can have only one primary key but multiple unique constraints. In Databricks, both are supported on Delta tables. For identifying rows, a primary key is the expected choice for a table relationship.
Where can I define key constraints on a Delta table?
You can define primary and foreign key constraints when creating or altering a Delta table. The syntax follows standard SQL patterns for table constraints. These definitions are visible in Unity Catalog. You can also add or drop constraints on existing tables using ALTER TABLE statements.