Single row tables in data modeling

Single row tables are a fact of our life as as a data designer, data modeler, data programmer, data whatever. Whether you are setting up a data warehouse or an operational database, it happens often that you need to save a list of single values/parameters that are valid, temporarily or stably, for the whole application. These could be items such as date data (i.e. LastUpdate, CreationDate etc.), boolean data (i.e statusIsActive, isInTest, forceDataLoad etc.), numeric data (i.e. CurrentExchangeRate, refreshDays etc.) or even string data (currentLanguage, mainTitle, homeUrl etc.).

Usually we pour all these variables into a single row table such as dbo._params, dbo.parameters or something like that. Then these values get retrieved and updated via query or stored procedure that usually takes for granted these values are part of a single row table.

In fact you will include these values as part of cte, subquery or cross join referencing the single row table. Whatever is the method, the logic used must count on the fact that the table involved consists of one and only one row, otherwise the result would return unexpected values and behavior.

Cross joining a single row table, for instance, never affecta the cardinality, but if you only add one additional row to that table, you will trigger a cartesian product with unexpected results.

Even if you bypass this side effect by selecting the values as part of an aggregation clause (MAX,MIN,FIRST,LAST and so on), the risk is to select an incorrect value due to the fact that a new value got inserted as part of a new row instead of updating the column within the only existing one.

How to prevent single row tables from new rows

Practically speaking, I have to say that is pretty rare that single row tables could encounter a row singleness violation. Usually these kind of tables get updated by an admin, often directly via SSMS or Visual Studio. Nonetheless it could happen that these tables can be updated by a power user inside a standard web form, which includes the add new row option. A new insertion could happen by mistake if not on purpose.

Moreover, as a data modeler, we are often obsessed with constraints that prevent errors inherited from the application layer. Ok, guilty: I am one of them.

So how can we add a constraint which grants singleness beside uniqueness?

A Primary Key is needed for sure in order to get uniqueness. Nonetheless uniqueness doesn't mean singleness. A Primary Key applied to a column or a set of column guarantees uniqueness of the respective value(s) within the entire table but it doesn't apply any limitation to the number of rows. You can get a sort of limitation by choosing a specific datatype. A primary key applied to a tinyint column, for instance, will guarantee a maximum of 255 rows for the whole table. Choosing a 'bit' column as an ID will almost get the goal. In fact, it will admit not more than 2 rows.

In order to get a limit of 1 row for the table you should choose a calculated column with a fixed value as id. You need to declare this column as PERSISTED in order to use it as PRIMARY KEY. And that's it.

By setting this calculated column as your ID you will get your goal. A table like this can get added only one row:

CREATE TABLE dbo._params(
   paramsID as 1 PERSISTED CONSTRAINT PK_Params PRIMARY KEY
  ,statusIsActive bit NOT NULL
  ,isInTest bit NOT NULL
  ,lastUpdate datetime NOT NULL
  ,creationDate datetime NOT NULL
  ,currentUSDEUR_ExchangeRate money NOT NULL
  ,currentLanguage nchar(2) NOT NULL
)