Enforcing a Single-Row Table in MS Access and SQL Server

There are many uses for a table with one--and only one--row in it. Using such a table is simpler if you can rely on the sanctity of its one-row-ness.

Enforcing a Single-Row Table in MS Access and SQL Server

Why Do It At All?

In many of my applications, I have a table with a single row in it.  I use these tables for a variety of reasons:

  • Track the database's current schema version
  • Maintain client-specific global values (e.g., county name)
  • Maintain environment-specific global parameters (i.e., dev vs. test vs. prod)
  • As a dummy table to support table-less UNION queries in Access

Whatever the reason for the table, they all have one important requirement in common: there should be one–and only one–record in the table.

Being confident that there is always exactly one row in the table means you can simplify the process of retrieving data from the table.  You don't have to worry about some user (or developer) accidentally adding a second row (or deleting the existing row).

That sounds great, but how can you accomplish that guarantee?

How to do it in Access

There is no way to enforce the single-row constraint in the table design user interface or in code using DAO.  The only option is to use ADO.  

For local tables, Microsoft Access provides an ADO Connection object for the current project.  Here is SO user HansUp's excellent solution to the problem:

Public Sub CreateDualTable()
    Dim strSql As String
    strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
    strSql = "INSERT INTO Dual (id) VALUES (1);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql

    strSql = "ALTER TABLE Dual" & vbNewLine & _
        vbTab & "ADD CONSTRAINT there_can_be_only_one" & vbNewLine & _
        vbTab & "CHECK (" & vbNewLine & _
        vbTab & vbTab & "(SELECT Count(*) FROM Dual) = 1" & vbNewLine & _
        vbTab & vbTab & ");"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
End Sub

He uses the table name "Dual" in his code above, which is a reference to the built-in table with the same name in Oracle databases.

For linked Access tables, I believe you'll need to replace the CurrentProject.Connection code in the above snippet with an appropriate ADO connection to the source database.

How to do it in SQL Server

Here's the equivalent SQL Server solution from user Damien_The_Unbeliever:

CREATE TABLE T1(
    Lock char(1) not null,
    /* Other columns */,
    constraint PK_T1 PRIMARY KEY (Lock),
    constraint CK_T1_Locked CHECK (Lock='X')
)

External references

Table-less UNION query in MS Access (Jet/ACE)
This works as expected: SELECT “Mike” AS FName This fails with the error “Query input must contain at least one table or query”: SELECT “Mike” AS FNameUNION ALLSELECT “John” AS FName Is this ...
CurrentProject.Connection property (Access)
User HansUp
Stack Overflow | The World’s Largest Online Community for Developers
Oracle DUAL Table: What is DUAL Table in Oracle
This tutorial introduces you to Oracle DUAL table which is a special table used for evaluating expressions or calling functions
User Damien_The_Unbeliever
Stack Overflow | The World’s Largest Online Community for Developers
SQL Server: how to constrain a table to contain a single row?
I want to store a single row in a configuration table for my application. I would like to enforce that this table can contain only one row. What is the simplest way to enforce the single row

Image by RENE RAUSCHENBERGER from Pixabay

All original code samples by Mike Wolfe are licensed under CC BY 4.0