Sometimes you need only one record in a table

To be specific, I want a table that contains a single record, typically this reports version information or status or something like that.  Do not allow a second record to be added.

I’ve done this in both Access and SQL Server, here’s the code for SQL:

CREATE TABLE [dbo].[z­DBInfo] (
[ID] [tinyint] NOT NULL IDENTITY(1, 1),
[App­Is­Locked] [bit] NOT NULL,
[Info­Date] [datetime2] (0) NOT NULL,
[Version­Date] [datetime2] (0) NOT NULL,
[DBVersion] [varchar] (10) COLLATE SQL_­Latin1_­General_­CP1_­CI_­AS NOT NULL,
[Info­Msg] [varchar] (512) COLLATE SQL_­Latin1_­General_­CP1_­CI_­AS NOT NULL,
[Row­Ver] [timestamp] NOT NULL) ON [PRIMARY]
GO

ALTER TABLE [dbo].[z­DBInfo] ADD CONSTRAINT [CK_z­DBInfo_­There­Can­Be­Only­One] CHECK (([ID]=(1)))
GO

ALTER TABLE [dbo].[z­DBInfo] ADD CONSTRAINT [PK_z­DBInfo] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO

The key to this is the constraint.  The ID field doesn’t need to be an identity, it can just as not have that parameter as long as you plug in the 1.  Users should only have Select permissions on the table so that your application program can see it, when you need to modify any information you do it direct at a higher permission level.

Personally, I like the AppIsLocked field.  In my application, the startup menu is actually invisible: it checks if the current user has access to the system and then launches the appropriate menu.  But first it checks the AppIsLocked field, and if it is true, doesn’t allow them in to the system at all.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s