// Fabric Constraints for the Lazy Coder

When working with data and building data models, I personally seldom use the constraints feature on a database. Call me lazy - but I think constraints are adding unnessesary complexity when building data models for reporting. Especially if you are working with the some of new platforms - like Microsoft Fabric, where you are using staleless compute, aka. data storage is seperated from the compute layer.

I understand the need for contraints on other database systems like OLTP systems.

In reporting models it can be somewhat usefull to have constraints between tables, as they help/force you to some level of governance in your datamodel.

But how can we use this in Microsoft Fabric and are they easy to work with?

Data models in Microsoft Fabric

In Fabric we are utilizing the Delta Parquet files from the OneLake and either the Lakehouse or Warehouse experience to build our data models.

We can find contraints in, as this post is written, the Warehouse experience of Fabric. The service where we can use T-SQL DML (Data Manipulation Language) to clean and build our data into a complete data model. Perhaps through Stored Procedures - with some limitations.

Warehouse in Fabric

With the Warehouse we get the constraints with very little effort when working wtih the data model.

Above I’ve build a very simple data model based on the AdventureWorks demo dataset. It has one fact table Orders and 3 dimension tables Customers, Employees and Products.

The model is build using drag-and-drop of the different relationships between the tables by dragging from the fact table the relating column to the respective column in the dimension table - Ex. CustomerID from Orders to CustomerID on Customers.

When the relationship dialogue is showed, you can edit the relationship to fot your needs. Below and example from the relationship between Orders and Customers.

Notice the two options at the bottom of the dialogue Make this relationship active and Assume referential integrity.

The first one, Make this relationship active, is also known from the Power BI semantic models (note the new name here) where you can have relationships between tables that are either active or inactive giving you different options when using DAX - you can find other blog posts around the universe to read more on this.

The second option, Assume referential integrity, gives you the power to control contraints on your semantic model. If you enable this option, the Fabric Warehouse will also create a contraint between the two tables on the given colum.

In this example I’ve made the two relationships [Orders]CustomerID -> [Customer]CustomerID and [Orders]EmployeeID -> [Employees]EmployeeID with this option enabled, where as the relationshop between [Orders]ProductID -> [Products]ProductID does not have this option enabled. Notice that there is no distinct difference in the visual representation of the relationships in the semantic model.

Finding the contraints in the database

The simplest way to find the existing contraints in the database is with the below T-SQL query:

SELECT 
    CONSTRAINT_NAME
    ,TABLE_NAME
    ,CONSTRAINT_TYPE
FROM 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
ORDER BY CONSTRAINT_TYPE

In above example semantic model I get below results:

The information above shows what we now have 3 unique constraints and 3 foreign key constraints. So even though we have not made all the relationships the same way in the semantic model, we get the same constraints in the database.

But we do get them 😉 - this is perhaps one of the simplest way to build constraints in a semantic model.

What can we then do with the constraints in the database

I had the same question when I first saw the constraints. So you are not alone.

First of all, the constraints blocks me from deleting referenced columns - example:

DROP TABLE dwh.customers

Output: Could not drop object ‘dwh.customers’ because it is referenced by a FOREIGN KEY constraint.

Interesting - so when we build our semantic model in the Warehouse service, we also get constraints that blocks us from deleting referenced obejcts and columns. I like this as the governance for keeping the semantic model live and working is auto-helping us out-of-the-box with this feature.

But - be aware!

Accoring to the documentation from Microsoft around constraints read them here, we need to take some things into account.

The build relationships also makes unique constraints on the referenced columns. Giving us an issue if the columns are actually not unique.

From the documentation: .

The foreigh keys are not enforced, but we can get wrong results of the uniqueness of a columns is not unique. I will not do a demo of this here, as you can find a good one in the documentation listed above.

Conclusion

So be aware when building your semantic model in the Warehouse experience in Microsoft Fabric:

• Uniqueness must be unique in the referenced columns
• Governance is helping you not dropping referenced objects

☕