Ensuring a Single Row Table

Question

Ensuring a Single Row Table

Massimo Preitano

SSC Enthusiast

Points: 183
More actions
November 27, 2023 at 12:00 am

#4324631

Comments posted to this topic are about the item Ensuring a Single Row Table

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Neat solution, but I disagree with this comment:

it happens often that you need to save a list of single values/parameters that are valid, temporarily or stably, for the whole application.

In my (lengthy!) time as a database developer, I can remember setting up such a table only once! That's because updating a single-row table with new 'current' values is equivalent to the deletion of historical data.

Instead, I tend to use effective dates or IsActive flags to achieve the same thing, without removing previous information. You never know when someone might query a number in a report which was generated months ago, where that number depends on the values in your params table.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

and on long run having a key-value table end up being better for this type of things as you always end up requiring more entries added, so better to just insert a row than to change table structure.

Glen Cooper SSCommitted Points: 1917 More actions · Answer 3

Glen Cooper

SSCommitted

Points: 1917

November 27, 2023 at 2:08 pm

#4335260

Interesting

JChrisCompton SSCommitted Points: 1609 More actions · Answer 4

That's because updating a single-row table with new 'current' values is equivalent to the deletion of historical data.

Yes, it is. There are cases where you don't need the historical data. *

Example:

We store the current application version in the db. That value has a single purpose - if a user didn't get the update, the program notifies them and terminates. (Success rate is above three nines, but we still miss a few)

That's likely the only design decision that hasn't been revisited in its decade+ lifetime.

* Yes, I acknowledge that can sound like blasphemy.

In case it helps you agree with me: I'll point out that the 'historical version data' is already recorded in lots of other places (release schedule, build server, etc.) If that doesn't work you'll just have to take my word that we are an edge case : -)

Massimo Preitano SSC Enthusiast Points: 183 More actions · Answer 5

Thanks for your comment, Phil. You know, sometimes we assume that what happens to us, happen to the whole world. Perhaps I should have said, 'It often happens to me'

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

Also, if you want to enforce a constraint of 1 .. X rows, leverage an identity column combined with a check constraint.

For example, the following allows a max of 3 rows to be inserted.

CREATE TABLE dbo.params (
paramsID INT IDENTITY(1,1) 
CONSTRAINT PK_Params PRIMARY KEY 
CHECK (paramsID BETWEEN 1 AND 3)
, param_name varchar(200) NOT NULL
, param_value VARCHAR(200) NOT NULL
);

INSERT dbo.params ( param_name, param_value ) VALUES ( 'A', '123' );
INSERT dbo.params ( param_name, param_value ) VALUES ( 'B', '123' );
INSERT dbo.params ( param_name, param_value ) VALUES ( 'C', '123' );

Now attempt to insert the 4th row.

INSERT dbo.params ( param_name, param_value ) VALUES ( 'D', '123' );

Msg 547, Level 16, State 0, Line 14

The INSERT statement conflicted with the CHECK constraint "CK__params__paramsID__3A81B327". The conflict occurred in database "tempdb", table "dbo.params", column 'paramsID'.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Zidar SSC Enthusiast Points: 126 More actions · Answer 7

There are other ways for enforcing cardinality of 1. here is one of them:

CREATE TABLE SingleRowTable

(

DataColumn varchar or whatever

,  CheckColumn int  UNIQUE CHECK (CheckColumn=1)

)

CheckColum can store 1 and nothing else. Since it is UNIQUE, we cannot add more columns with value 1.

Of course, choice of CheckColum name and value is up to you.

🙂

Zidar's Theorem: The best code is no code at all...

Zidar SSC Enthusiast Points: 126 More actions · Answer 8

My solution is similar to what Eric M. as Russell proposed, except for me not using identity. The version of CheckColumn would be

CheckColumn int UNIQUE CHECK (CheckColumn IN (1,2,3)) NOT NULL

By avoiding identity we have no restrictions for deleting and updating records. Identity could become out of order - delete any row and you have lost that value forever. Not likely to happen, but still, one headache less in the future.

Zidar's Theorem: The best code is no code at all...