SQL Server - Validate Column In Every Insert

Question

I am writing a .net core 2.2 C#, code-first, api with a SQL Server backend. It was just requested to make the api a multi-tenant api. So, I added TenantId (INT NOT NULL) to all DB tables, QueryFilters, etc... Everything is working as expected.

I want to ensure I did not and no one in the future will forget to add the TenantId on all inserts. TenantId will always be a value greater than zero. Is there a way in SQL Server to ensure that every insert on every table has a TenantId value that is greater than zero?

You said you added `TenantId(int not null)`. Did you add that directly to the DB? You know you're gonna need to add that column and a check constraint through EF migrations...or you're gonna hork up your upgradability in the field. — Clay, Jan 09 '19 at 15:49
@Clay - I did not add that directly to the DB. I added through code. — Ben, Jan 09 '19 at 15:54
While you're solution would work, not every table needs to have the tenant ID. You could stick to having a few tables having it and the relationships that those tables map to as a foreign key are implicitly mapped to a specific tenant. For example, if you have a School (as tenant) with Students (mapped to a school) if each student took a class, by default those classes could be inferred to belong to a specific tenant without the ID. — Adam Hess, Jan 09 '19 at 16:28
@Adam - See comment about FK on accepted answer. Since I already have a tenantId on every table and everything coded is working, do you see any benefit to removing the tenantId from the "Students" table? — Ben, Jan 09 '19 at 16:53

Roman Marusyk · Accepted Answer · 2019-01-09T16:14:12.560

1

First, it seems that TenantId should be a ForeignKey to the table Tenant. If yes then it definitely cannot be zero or less, else :

Add CHECK Constraint on the column

CREATE TABLE your_table (
    TenantId INT NOT NULL CHECK(TenantId > 0)
    ...
)

And if column is NOT NULL then it should be in every insert else exception will throw

Cannot insert the value NULL into column 'TenantId', table 'dbo.your_table'; column does not allow nulls. INSERT fails.

Then, in your C# code use Data Annotations attribute

[Range(1, Int32.MaxValue)]

or create own like: [MinValue(1)]

To "ensure that every insert on every table has a TenantId value that is greater than zero" you can also use constructor with private setter

public class Model
{
   public Model(int name, int tenantId)
   {
      Name = name;
      if(tenantId <= 0)
      {
          throw new Exception();
      }
      TenantId = tenantId;
   }

   public string Name { get; private set; }
   public int TenantId { get; private set; }
}

or something like this property in your model

public int TenantId {
    get {
        return _tenantId;
    }
    set {
        if (value <= 0) throw new Exception();
        _tenantId = value;
    }
}

edited Jan 09 '19 at 16:14

answered Jan 09 '19 at 15:38

Roman Marusyk

23,328
24
73
116

In a code-first EF world? – Clay Jan 09 '19 at 15:50
1

And what is the problem with code-first? You can use data annotations or add check on property or use constructors with private setter, it depends on your implementation – Roman Marusyk Jan 09 '19 at 15:52
1

This does answer my question as I stated it. Thanks! Although, the next thing I lookup or ask will be how to add a constraint in entity framework. – Ben Jan 09 '19 at 15:55
The data annotation [Range(1, int.MaxValue)] is working for me. Thanks again! – Ben Jan 09 '19 at 16:08
1

@Ben should not be the TenantId added as ForeignKey to the table Tenant? If yes then it definitely cannot be zero or less – Roman Marusyk Jan 09 '19 at 16:10
1

There is a separate Tenant database that houses all the tenant info. So, making it a FK can't happen. In the startup of my application, I get the tenant Id from a tenant api based upon the api key used to make the request. – Ben Jan 09 '19 at 16:51

SQL Server - Validate Column In Every Insert

1 Answers1